wxsqlite3 saveing and exporting comma delimited files? Topic is solved

Talk here about issues with one of the components hosted at wxCode, or suggest features for it.
Post Reply
ouch67
Earned some good credits
Earned some good credits
Posts: 135
Joined: Sun Mar 23, 2008 12:09 am

wxsqlite3 saveing and exporting comma delimited files?

Post by ouch67 »

I know you can do this from the command line tool given with sqlite. but I want my program to have the ability to do this.

So is there a special command or maybe some undocumented SQL command to do this?

thanks.

BTW, I really enjoy wxsqlite3, it really makes working with sqlite that much easier than it already is. Keep it up!
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Re: wxsqlite3 saveing and exporting comma delimited files?

Post by utelle »

ouch67 wrote:I know you can do this from the command line tool given with sqlite. but I want my program to have the ability to do this.

So is there a special command or maybe some undocumented SQL command to do this?
Neither does wxSQLite3 provide special commands nor is there an undocumented SQL command to export data in comma delimited format. But SQLite SQL allows to express what you want. Example:

Code: Select all

create table t1 (c1 text, c2 integer);
insert into t1 values ('Anton', 1);
insert into t1 values ('Berta', 2);
select quote(a) || ',' || b from t1;
The select command returns a string of all selected columns in comma separated format for all selected rows:

Code: Select all

'Anton',1
'Berta',2
The use of the (non-standard SQL) quote function takes care of surrounding the column value by single quotes and escaping single quotes within the value string.

In your program you may loop through the result set and write these strings to a file.
ouch67 wrote:BTW, I really enjoy wxsqlite3, it really makes working with sqlite that much easier than it already is. Keep it up!
I'm pleased to hear you like wxSQLite3. :D

Regards,

Ulrich
ouch67
Earned some good credits
Earned some good credits
Posts: 135
Joined: Sun Mar 23, 2008 12:09 am

Post by ouch67 »

That's neat, but how would you import those back in to a data base?

I wanted to export some data to merge into another database. Maybe I'm going about this the wrong way. Maybe I should just create a new DB and copy values over to it?

which way is best?

thanks for the reply though.
utelle
Moderator
Moderator
Posts: 1125
Joined: Tue Jul 05, 2005 10:00 pm
Location: Cologne, Germany
Contact:

Post by utelle »

ouch67 wrote:That's neat, but how would you import those back in to a data base?
You asked how to export data, but you didn't mention what you wanted to do with these export files.

The SQLite source distribution contains the source of the command line shell as well. You might take a look into it how exporting and importing in comma delimited format is solved and could reuse portions of the code in your own application.
ouch67 wrote:I wanted to export some data to merge into another database. Maybe I'm going about this the wrong way. Maybe I should just create a new DB and copy values over to it?

which way is best?
If you have another SQLite database file from which you want to copy data the best approach is to use the SQL command ATTACH DATABASE to attach this other database and then to use the variant of the INSERT command using a SELECT statement to select the data to be inserted from the table in question.

Regards,

Ulrich
Post Reply