Re: [sqlite] Importing text file via .bat file
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] - --
Re: [sqlite] Importing text file via .bat file
On 11/15/06, RB Smissaert <[EMAIL PROTECTED]> wrote: 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? You can certainly do this from vb. It's a bit of work though. You will need to read the data file and parse it yourself, then insert the data directly. You should wrap all the inserts in a transaction since it will speed it up greatly (this is true for any method of doing inserts into sqlite). You can also run the script to import it but if I recall correctly vb lets you start a process but it takes more work to wait for the external process to complete and then to get it's return code from the operating system. The system call I was familiar with just started the script running, it didn't wait for it to finish. I doubt you will see any significant difference in speed between the two methods. -- SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com - To unsubscribe, send email to [EMAIL PROTECTED] -
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] -
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] -
RE: [sqlite] Importing text file via .bat file
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] -
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] -
RE: [sqlite] Importing text file via .bat file
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 14 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] - -
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] -
RE: [sqlite] Importing text file via .bat file
The application is run from a VBA .xla add-in with a number of VB6 ActiveX helper files. Good point, I could delete the imported text file and use that As the trigger to notify VBA that all is done. Not sure VBA can monitor a rename event other that by running a loop and checking for the existence of a file. RBS -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 15 November 2006 20:11 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Importing text file via .bat file I've noticed that you mention both VB and VBS. Is this something that you are eventually going to run from an actual VB application? If so, you might be able to use a directory notification on the directory where the import is, then just trigger an action when the import file is deleted, which the .bat file could do after the import is done. Or, if you don't want to be that drastic, monitor the specific file for a Rename event. Of course, I don't know that VB 6 supports this, but I know that VB.Net does. -- Eric Pankoke Founder / Lead Developer Point Of Light Software http://www.polsoftware.com/ -- Original message -- From: "RB Smissaert" <[EMAIL PROTECTED]> > Of course the .bat file could make a little file to notify VB. > Still, running a loop to check for this is not ideal. > > RBS > > -Original Message- > From: Griggs, Donald [mailto:[EMAIL PROTECTED] > Sent: 15 November 2006 19:37 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Importing text file via .bat file > > > Echo .mode csv >MyCommands.tmp > Echo .import ReadCode.txt ReadCode >>MyCommands.tmp > > Sqlite3 ReadCode.db ".read myCommands.tmp" > > = > -Original Message- > From: RB Smissaert [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 15, 2006 1:13 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Importing text file via .bat file > > Hi Donald, > > Thanks, but I don't quite get it yet. > What is in this file MyCommand.tmp? > Is there no way to put the whole thing in on .bat file or even better > run the whole sequence from VB? > > RBS > === > === > > Regarding: "What is in this file MyCommand.tmp?" > > The file, "MyCommand.tmp" would be a temporary file created by your > batch file itself as it runs. > > > > Regarding: "...one batch file...?" > > In a sense, the entire logic *is* in one batch file, but it's true that > a small, temporary file is created as part of the process, and you can > delete it when done. Unless you're doing this thousands of times an > hour, the extra time introduced by having a temporary file will be > minimal. > > Kees Nyut showed a nicer syntax, one that I *thought* I had problems > with for early versions of sqlite.exe under windows, but seems to work > well now: > Instead of: > Sqlite3 ReadCode.db ".read myCommands.tmp" > use: > Sqlite3 ReadCode.db > > Re: "..or VB?" > I don't use Visual Basic, so I'm no expert, but the advantage of > using the sqlite command-line utility (called sqlite3.exe) is that the > code for parsing comma-delimited strings is built in. If you have your > routines for this, or want to incorporate the code from the sqlite > command utility source, then you can certainly do everything within VB. > > > Returning to the "single batch file" question: > > I've actually used a sneaky means of doing this, but it assumes > you're pretty familiar with batch files. It's more straightforward for > unix scripts, but for windows, we start it with a line of > /* > Which gives a (harmless) error when the batch file starts, but it allows > us to treat the top portion of the file as a set of inputs to sqlite > command utility. > > -beginning of file Mybatchfile.bat > /* > GOTO :startit > This is a combination sql and batch file to ... > */ > > /* Put all your sql commands and DOT commands here */ > drop table if exists ReadCode; > create table ReadCode ( > SUBJECT_TYPE varchar(5), > READ_CODEvarchar(5), > TERM30 varchar(30), > TERM60 varchar(60) > ); > .mode csv > .import ReadCode.txt ReadCode > > -- end sqlite with .quit, but batch file continues > .quit > > == nothing runs in this space till we get to "startit" > > > :Startit > Rem Beginning of batch file commands > @echo off >
RE: [sqlite] Importing text file via .bat file
There is no actual VBS file. I run it like this from VBA: Sub UpdateReadSQLite2(bShowSQL As Boolean) Dim oShell UpdateReadTextFile bShowSQL, True Application.StatusBar = _ "transferring the data from ReadCodeNoQuotes.txt to the SQLite DB" Set oShell = CreateObject("WSCript.shell") oShell.Run "cmd /C C:\SQLite\ReadCode.bat", 0 Set oShell = Nothing Application.StatusBar = False End Sub Still, these are minor details and I will figure that out. RBS -Original Message- From: Kees Nuyt [mailto:[EMAIL PROTECTED] Sent: 15 November 2006 20:30 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Importing text file via .bat file On Wed, 15 Nov 2006 19:46:32 -, you wrote: >Kees, > >Just one other thing needed. >In the .sql file is there a way to notify VB that the text import is >finished? I run the .bat file now from VBS, so it won't be visible. >I couldn't see anything suitable in the dot commands to tell VB. > >RBS It is done when the .bat returns to VBS, I guess. You could try to execute like this (just guessing, i never use VBS): start /w ReadCode.bat Or add this to the sql script: at the top: if exist signal.txt del signal.txt at the bottom: .output signal.txt select SUBJECT_TYPE from ReadCode limit 1; and detect the presence of the file signal.txt in your VBS script. Or do everything in your .bat (or better, .cmd). -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Importing text file via .bat file
Didn't know about .cmd scripts (not that I know much about .bat) and will have a look at that. There is a slim chance that some of my users are still on Win98, so that could be a problem. Will have a look at Sqlite3Explorer as well. Only started with SQLite a few days ago, but I can see it is great software, especially as I only need as a local, temporary, one user database. Shame I don't know C. RBS -Original Message- From: Kees Nuyt [mailto:[EMAIL PROTECTED] Sent: 15 November 2006 20:22 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Importing text file via .bat file On Wed, 15 Nov 2006 19:15:38 -, you wrote: >Hi Kees, > >Thanks a lot for that. All working perfect now. >I think all this should be on the SQLite site as >it took me a long time to get this fixed. http://www.sqlite.org/sqlite.html and the .help command taught me the commands, http://www.sqlite.org/lang.html the SQL as understood by sqlite; the rest is straight shell scripting, redirecting and piping. Which is nasty at first, but quite powerful once you get used to it. Worth to learn, really. But not really on topic on the sqlite site. But here are some hints: On windows NT4, 2000, XP and 2003 you're better off with writing .cmd scripts instead of .bat. .cmd is interpreted by CMD.EXE, which is much more powerful than command.exe, which is (usually) interpreted by the legacy command.exe command shell. Add some tools like make (for process management) and gawk (for data cleaning, input filtering and transformations), and there's your database factory ;) CMD.EXE even allows you to use forward slashes, you just have to put paths and filenames between double quotes as in "disk:/path/filename.extension". For easy table browsing and testing I use the excellent Sqlite3Explorer by Mike Cariotoglou (see http://www.sqlite.org/cvstrac/wiki?p=ManagementTools for a pointer), which even includes a query editor and a report generator. >Nogmaals bedankt. Veel plezier! >RBS -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Importing text file via .bat file
On Wed, 15 Nov 2006 19:46:32 -, you wrote: >Kees, > >Just one other thing needed. >In the .sql file is there a way to notify VB that the text import is >finished? I run the .bat file now from VBS, so it won't be visible. >I couldn't see anything suitable in the dot commands to tell VB. > >RBS It is done when the .bat returns to VBS, I guess. You could try to execute like this (just guessing, i never use VBS): start /w ReadCode.bat Or add this to the sql script: at the top: if exist signal.txt del signal.txt at the bottom: .output signal.txt select SUBJECT_TYPE from ReadCode limit 1; and detect the presence of the file signal.txt in your VBS script. Or do everything in your .bat (or better, .cmd). -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Importing text file via .bat file
On Wed, 15 Nov 2006 19:15:38 -, you wrote: >Hi Kees, > >Thanks a lot for that. All working perfect now. >I think all this should be on the SQLite site as >it took me a long time to get this fixed. http://www.sqlite.org/sqlite.html and the .help command taught me the commands, http://www.sqlite.org/lang.html the SQL as understood by sqlite; the rest is straight shell scripting, redirecting and piping. Which is nasty at first, but quite powerful once you get used to it. Worth to learn, really. But not really on topic on the sqlite site. But here are some hints: On windows NT4, 2000, XP and 2003 you're better off with writing .cmd scripts instead of .bat. .cmd is interpreted by CMD.EXE, which is much more powerful than command.exe, which is (usually) interpreted by the legacy command.exe command shell. Add some tools like make (for process management) and gawk (for data cleaning, input filtering and transformations), and there's your database factory ;) CMD.EXE even allows you to use forward slashes, you just have to put paths and filenames between double quotes as in "disk:/path/filename.extension". For easy table browsing and testing I use the excellent Sqlite3Explorer by Mike Cariotoglou (see http://www.sqlite.org/cvstrac/wiki?p=ManagementTools for a pointer), which even includes a query editor and a report generator. >Nogmaals bedankt. Veel plezier! >RBS -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Importing text file via .bat file
I've noticed that you mention both VB and VBS. Is this something that you are eventually going to run from an actual VB application? If so, you might be able to use a directory notification on the directory where the import is, then just trigger an action when the import file is deleted, which the .bat file could do after the import is done. Or, if you don't want to be that drastic, monitor the specific file for a Rename event. Of course, I don't know that VB 6 supports this, but I know that VB.Net does. -- Eric Pankoke Founder / Lead Developer Point Of Light Software http://www.polsoftware.com/ -- Original message -- From: "RB Smissaert" <[EMAIL PROTECTED]> > Of course the .bat file could make a little file to notify VB. > Still, running a loop to check for this is not ideal. > > RBS > > -Original Message- > From: Griggs, Donald [mailto:[EMAIL PROTECTED] > Sent: 15 November 2006 19:37 > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Importing text file via .bat file > > > Echo .mode csv >MyCommands.tmp > Echo .import ReadCode.txt ReadCode >>MyCommands.tmp > > Sqlite3 ReadCode.db ".read myCommands.tmp" > > = > -Original Message- > From: RB Smissaert [mailto:[EMAIL PROTECTED] > Sent: Wednesday, November 15, 2006 1:13 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Importing text file via .bat file > > Hi Donald, > > Thanks, but I don't quite get it yet. > What is in this file MyCommand.tmp? > Is there no way to put the whole thing in on .bat file or even better > run the whole sequence from VB? > > RBS > === > === > > Regarding: "What is in this file MyCommand.tmp?" > > The file, "MyCommand.tmp" would be a temporary file created by your > batch file itself as it runs. > > > > Regarding: "...one batch file...?" > > In a sense, the entire logic *is* in one batch file, but it's true that > a small, temporary file is created as part of the process, and you can > delete it when done. Unless you're doing this thousands of times an > hour, the extra time introduced by having a temporary file will be > minimal. > > Kees Nyut showed a nicer syntax, one that I *thought* I had problems > with for early versions of sqlite.exe under windows, but seems to work > well now: > Instead of: > Sqlite3 ReadCode.db ".read myCommands.tmp" > use: > Sqlite3 ReadCode.db > > Re: "..or VB?" > I don't use Visual Basic, so I'm no expert, but the advantage of > using the sqlite command-line utility (called sqlite3.exe) is that the > code for parsing comma-delimited strings is built in. If you have your > routines for this, or want to incorporate the code from the sqlite > command utility source, then you can certainly do everything within VB. > > > Returning to the "single batch file" question: > > I've actually used a sneaky means of doing this, but it assumes > you're pretty familiar with batch files. It's more straightforward for > unix scripts, but for windows, we start it with a line of > /* > Which gives a (harmless) error when the batch file starts, but it allows > us to treat the top portion of the file as a set of inputs to sqlite > command utility. > > -beginning of file Mybatchfile.bat > /* > GOTO :startit > This is a combination sql and batch file to ... > */ > > /* Put all your sql commands and DOT commands here */ > drop table if exists ReadCode; > create table ReadCode ( > SUBJECT_TYPE varchar(5), > READ_CODEvarchar(5), > TERM30 varchar(30), > TERM60 varchar(60) > ); > .mode csv > .import ReadCode.txt ReadCode > > -- end sqlite with .quit, but batch file continues > .quit > > == nothing runs in this space till we get to "startit" > > > :Startit > Rem Beginning of batch file commands > @echo off > Rem clear the harmless error off the screen > Cls > > Rem [Do any other preparatory batch file commands here] > > Rem Invoke sqlite3 command utility and submit the top of this batch file > as a set of commands > Rem we assume this entire batch file is named "Mybatchfile" and it's in > our current directory. > > Sqlite3 myData.db > Rem [ put here any windows batch commands we want after Sqlite3 leaves > with the .quit command ] > > end of mybatchfile.bat > > > > --
RE: [sqlite] Importing text file via .bat file
Of course the .bat file could make a little file to notify VB. Still, running a loop to check for this is not ideal. RBS -Original Message- From: Griggs, Donald [mailto:[EMAIL PROTECTED] Sent: 15 November 2006 19:37 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Importing text file via .bat file Echo .mode csv >MyCommands.tmp Echo .import ReadCode.txt ReadCode >>MyCommands.tmp Sqlite3 ReadCode.db ".read myCommands.tmp" = -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 15, 2006 1:13 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Importing text file via .bat file Hi Donald, Thanks, but I don't quite get it yet. What is in this file MyCommand.tmp? Is there no way to put the whole thing in on .bat file or even better run the whole sequence from VB? RBS === === Regarding: "What is in this file MyCommand.tmp?" The file, "MyCommand.tmp" would be a temporary file created by your batch file itself as it runs. Regarding: "...one batch file...?" In a sense, the entire logic *is* in one batch file, but it's true that a small, temporary file is created as part of the process, and you can delete it when done. Unless you're doing this thousands of times an hour, the extra time introduced by having a temporary file will be minimal. Kees Nyut showed a nicer syntax, one that I *thought* I had problems with for early versions of sqlite.exe under windows, but seems to work well now: Instead of: Sqlite3 ReadCode.db ".read myCommands.tmp" use: Sqlite3 ReadCode.db
RE: [sqlite] Importing text file via .bat file
Will see if I can get that .bat file working. Thanks again. One thing I will need to figure out is how to notify VB that all is finished. I suppose I could run a loop at the end of my VB procedure checking for something, but I couldn't see anything suitable (amongst the dot commands) that VB could check for. Maybe the .bat file should show at the end to tell the user things are finished. I now run the .bat file from VBS, so I can make it run invisible. RBS -Original Message- From: Griggs, Donald [mailto:[EMAIL PROTECTED] Sent: 15 November 2006 19:37 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Importing text file via .bat file Echo .mode csv >MyCommands.tmp Echo .import ReadCode.txt ReadCode >>MyCommands.tmp Sqlite3 ReadCode.db ".read myCommands.tmp" = -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 15, 2006 1:13 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Importing text file via .bat file Hi Donald, Thanks, but I don't quite get it yet. What is in this file MyCommand.tmp? Is there no way to put the whole thing in on .bat file or even better run the whole sequence from VB? RBS === === Regarding: "What is in this file MyCommand.tmp?" The file, "MyCommand.tmp" would be a temporary file created by your batch file itself as it runs. Regarding: "...one batch file...?" In a sense, the entire logic *is* in one batch file, but it's true that a small, temporary file is created as part of the process, and you can delete it when done. Unless you're doing this thousands of times an hour, the extra time introduced by having a temporary file will be minimal. Kees Nyut showed a nicer syntax, one that I *thought* I had problems with for early versions of sqlite.exe under windows, but seems to work well now: Instead of: Sqlite3 ReadCode.db ".read myCommands.tmp" use: Sqlite3 ReadCode.db
RE: [sqlite] Importing text file via .bat file
Kees, Just one other thing needed. In the .sql file is there a way to notify VB that the text import is finished? I run the .bat file now from VBS, so it won't be visible. I couldn't see anything suitable in the dot commands to tell VB. RBS -Original Message- From: Kees Nuyt [mailto:[EMAIL PROTECTED] Sent: 15 November 2006 18:54 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Importing text file via .bat file On Tue, 14 Nov 2006 23:44:12 -, you wrote: >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 Input scripts for the sqlite command line utility aren't restricted to SQL, you can also put 'dot commands' in it. Try this (some day you may be working on an existing database instead of deleting it first, so I would drop the table first): --- file ReadCode.sql begin --- drop table if exists ReadCode; create table ReadCode ( SUBJECT_TYPE varchar(5), READ_CODEvarchar(5), TERM30 varchar(30), TERM60 varchar(60) ); .mode csv .import ReadCode.txt ReadCode --- file ReadCode.sql end --- And this is your shell script: --- file ReadCode.bat begin --- cd C:\SQLite del ReadCode.db SQLite3 ReadCode.db
RE: [sqlite] Importing text file via .bat file
Echo .mode csv >MyCommands.tmp Echo .import ReadCode.txt ReadCode >>MyCommands.tmp Sqlite3 ReadCode.db ".read myCommands.tmp" = -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 15, 2006 1:13 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Importing text file via .bat file Hi Donald, Thanks, but I don't quite get it yet. What is in this file MyCommand.tmp? Is there no way to put the whole thing in on .bat file or even better run the whole sequence from VB? RBS === === Regarding: "What is in this file MyCommand.tmp?" The file, "MyCommand.tmp" would be a temporary file created by your batch file itself as it runs. Regarding: "...one batch file...?" In a sense, the entire logic *is* in one batch file, but it's true that a small, temporary file is created as part of the process, and you can delete it when done. Unless you're doing this thousands of times an hour, the extra time introduced by having a temporary file will be minimal. Kees Nyut showed a nicer syntax, one that I *thought* I had problems with for early versions of sqlite.exe under windows, but seems to work well now: Instead of: Sqlite3 ReadCode.db ".read myCommands.tmp" use: Sqlite3 ReadCode.db
RE: [sqlite] Importing text file via .bat file
Hi Kees, Thanks a lot for that. All working perfect now. I think all this should be on the SQLite site as it took me a long time to get this fixed. Nogmaals bedankt. RBS -Original Message- From: Kees Nuyt [mailto:[EMAIL PROTECTED] Sent: 15 November 2006 18:54 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Importing text file via .bat file On Tue, 14 Nov 2006 23:44:12 -, you wrote: >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 Input scripts for the sqlite command line utility aren't restricted to SQL, you can also put 'dot commands' in it. Try this (some day you may be working on an existing database instead of deleting it first, so I would drop the table first): --- file ReadCode.sql begin --- drop table if exists ReadCode; create table ReadCode ( SUBJECT_TYPE varchar(5), READ_CODEvarchar(5), TERM30 varchar(30), TERM60 varchar(60) ); .mode csv .import ReadCode.txt ReadCode --- file ReadCode.sql end --- And this is your shell script: --- file ReadCode.bat begin --- cd C:\SQLite del ReadCode.db SQLite3 ReadCode.db
Re: [sqlite] Importing text file via .bat file
On Tue, 14 Nov 2006 23:44:12 -, you wrote: >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 Input scripts for the sqlite command line utility aren't restricted to SQL, you can also put 'dot commands' in it. Try this (some day you may be working on an existing database instead of deleting it first, so I would drop the table first): --- file ReadCode.sql begin --- drop table if exists ReadCode; create table ReadCode ( SUBJECT_TYPE varchar(5), READ_CODEvarchar(5), TERM30 varchar(30), TERM60 varchar(60) ); .mode csv .import ReadCode.txt ReadCode --- file ReadCode.sql end --- And this is your shell script: --- file ReadCode.bat begin --- cd C:\SQLite del ReadCode.db SQLite3 ReadCode.db
RE: [sqlite] Importing text file via .bat file
Hi Donald, Thanks, but I don't quite get it yet. What is in this file MyCommand.tmp? Is there no way to put the whole thing in on .bat file or even better run the whole sequence from VB? RBS -Original Message- From: Griggs, Donald [mailto:[EMAIL PROTECTED] Sent: 15 November 2006 15:02 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Importing text file via .bat file Regarding: "...but how would I combine all this in one .bat file ...?" Hello, RBS. The following is in windows commandline syntax: Echo .mode csv >MyCommands.tmp Echo .import ReadCode.txt ReadCode >>MyCommands.tmp Sqlite3 ReadCode.db ".read myCommands.tmp" or, if your file of sqlite commands is not in the same directory: Sqlite3 ReadCode.db ".read c:\\myDirectory\\myCommands.tmp"\ You can use your file of commands, of course, to do many other things, such as creating indices after the import, creating views, tallying statistics, etc. [opinions expressed are my own, and not those of my company] - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Importing text file via .bat file
Regarding: "...but how would I combine all this in one .bat file ...?" Hello, RBS. The following is in windows commandline syntax: Echo .mode csv >MyCommands.tmp Echo .import ReadCode.txt ReadCode >>MyCommands.tmp Sqlite3 ReadCode.db ".read myCommands.tmp" or, if your file of sqlite commands is not in the same directory: Sqlite3 ReadCode.db ".read c:\\myDirectory\\myCommands.tmp"\ You can use your file of commands, of course, to do many other things, such as creating indices after the import, creating views, tallying statistics, etc. [opinions expressed are my own, and not those of my company] - To unsubscribe, send email to [EMAIL PROTECTED] -
[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), TERM60varchar(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] -