OK, writing the textfile as inserts and running that is a bit slower than the .import, but it has one advantage and that is that comma's in the field are no problem as the values can be enclosed in double-quotes. With the .import I had to take the comma's out first. There weren't many comma's and it wasn't a big problem, but it is best to go for accuracy, so It is a bit better. Is there no way to do .import with the data in double quotes, so that the comma's are no trouble?
Now the next thing to try is doing inserts directly from VB, from the ADO recordset. I suppose this should be the fastest. I can think I can do it with the VB wrapper, but not sure how to it without it. Can I do SQLite3.exe directly with the insert data as an argument? RBS -----Original Message----- From: Clark Christensen [mailto:[EMAIL PROTECTED] Sent: 15 November 2006 21:37 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Importing text file via .bat file RBS, Sorry to jump in late here. Others have given good advice, but I'm wondering, since this is all running from VB, why not do all the work in VB and skip the batch (or cmd) file. I'm not a VB guy, but I do know it's pretty powerful. Are you having some trouble with a VB wrapper for SQLite? If no, then what you propose should be as simple as iterating through the IB recordset and inserting what you need into your SQLite table. If you are having trouble with a wrapper, then it seems to me like VB can (and should) do everything except the actual import. If you create your SQL script as: --ReadCode.sql to build and populate ReadCode.db drop table if exists ReadCode; create table ReadCode ( SUBJECT_TYPE varchar(5), READ_CODE varchar(5), TERM30 varchar(30), TERM60 varchar(60) ); .mode csv .import c:\sqlite\ReadCode.txt ReadCode --END SQL Then, from VB, you issue a single command like: sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql" and wait for SQLite to finish (or read the exit code, or read SQLite's stdout output). If it's a success, there'll be no output from SQLite. If what you really want is to have one single SQL file to do the job, you would have your "Recordset to text" step write out each row as an insert statement into ReadCode.sql, so ReadCode.sql would then look like: --ReadCode.sql to build and populate ReadCode.db drop table if exists ReadCode; create table ReadCode ( SUBJECT_TYPE varchar(5), READ_CODE varchar(5), TERM30 varchar(30), TERM60 varchar(60) ); begin transaction; insert into ReadCode values (...); insert into ReadCode values (...); insert into ReadCode values (...); ... commit; --END SQL Then issue the same command from VB to start the job: sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql" Either way, you would be able to eliminate the batch file, and handle everything from within VB. -Clark ----- Original Message ---- From: RB Smissaert <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Tuesday, November 14, 2006 3:44:12 PM Subject: [sqlite] Importing text file via .bat file Have figure out now what the quickest way is to move data from Interbase to a SQLite db file: IB to ADO recordset Recordset to text Import the text file with the .import command. Now I am trying to figure out how to automate the last step with a .bat file. What I got sofar is: Have a SQL file with: create table ReadCode ( SUBJECT_TYPE varchar(5), READ_CODE varchar(5), TERM30 varchar(30), TERM60 varchar(60) ); Run a .bat file with this: cd C:\SQLite del ReadCode.db type ReadCode.sql | sqlite3 ReadCode.db Then run from the command prompt: Cd C:\SQLite (press return) SQLite3 ReadCode.db (press return) .mode csv (press return) .import ReadCode.txt ReadCode (press return) This runs nice and quick, but how would I combine all this in one .bat file or how could I run this all from VB? I know very little about .bat files, but I would think that somehow it must be possible. Thanks for any assistance. RBS ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
