Hi Clark, I started out with doing everything from VBA, setting up the ADO recordset with data from IB, then creating a SQLiteDb.Recordset (I use the VB wrapper from Terra Informatica) then pushing data from the ADO recordset to the SQLiteDB recordset and then doing updates every so many cycles of the loop. This was a bit too slow and ADO recorset to text and importing that text into the SQLite DB is much faster. Haven't looked yet into doing direct UPDATES or INSERTS, without the SQLiteDB recordset and I suspect that is faster then the first method. I am not sure if I do UPDATES or INSERTS if this faster with the VB wrapper or with direct commands to SQLite. I take it the last is faster. Doing away with the text file should make it faster although writing that file from the ADO recordset is pretty fast. We are not talking about enormous amounts of data here, about 140000 rows and 4 fields. Eventually though if this works out well it might be up to a few million rows.
OK, will just have to do some further experimenting. At least I got something going now that is fast already and works well. 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] -----------------------------------------------------------------------------
