Re: [sqlite] Importing text file via .bat file

2006-11-16 Thread Clark Christensen 
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

2006-11-15 Thread Jay Sprenkle 

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

2006-11-15 Thread RB Smissaert 
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

2006-11-15 Thread RB Smissaert 
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

2006-11-15 Thread Clark Christensen 
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

2006-11-15 Thread RB Smissaert 
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
> Rem clear the harmless error off the screen
> Cls
&

RE: [sqlite] Importing text file via .bat file

2006-11-15 Thread RB Smissaert 
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

2006-11-15 Thread RB Smissaert 
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

2006-11-15 Thread Kees Nuyt 
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

2006-11-15 Thread Kees Nuyt 
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

2006-11-15 Thread epankoke 
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

2006-11-15 Thread RB Smissaert 
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

2006-11-15 Thread RB Smissaert 
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

2006-11-15 Thread RB Smissaert 
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

2006-11-15 Thread Griggs, Donald 
 
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

2006-11-15 Thread Kees Nuyt 
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

2006-11-15 Thread RB Smissaert 
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

2006-11-15 Thread Griggs, Donald 


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

2006-11-14 Thread RB Smissaert 
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]
-