Aah, I see "Shell" means something different to you than to me. I'm sorry, I was thinking SQLite shell. I can't help with any specific VB examples.
-Clark ----- Original Message ---- From: RB Smissaert <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, November 15, 2006 5:11:38 PM Subject: RE: [sqlite] Importing text file via .bat file Not sure if Shell can do something like that. What would the VB code be? RBS -----Original Message----- From: Clark Christensen [mailto:[EMAIL PROTECTED] Sent: 15 November 2006 23:53 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Importing text file via .bat file Shell ----- Original Message ---- From: RB Smissaert <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Wednesday, November 15, 2006 2:16:32 PM Subject: RE: [sqlite] Importing text file via .bat file > sqlite3 c:\sqlite\ReadCode.db ".read c:\sqlite\ReadCode.sql" Not sure how that would work from VBA. Did you mean to run this with Shell or the Windows API? 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] ---------------------------------------------------------------------------- - ---------------------------------------------------------------------------- - To unsubscribe, send email to [EMAIL PROTECTED] ---------------------------------------------------------------------------- - ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] ----------------------------------------------------------------------------- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
