Problem running .bat file

[RB Smissaert]

Trying to import a text file into SQLite with the .import command that is
part of sqlite3.exe.
No problem at all doing this manually from the command prompt:

cd C:\SQLite
(press Return)
SQLite3 Test.db3
(press Return)
.mode csv
(press Return)
.import Test.txt Table1
(press Return)

This imports the data nicely in the table that was already created before
running this.

If I put in in a .bat file though:

@ECHO OFF
Cd C:\SQLite
SQLite3 Test.db3
.mode csv
.import Test.txt Table1

and run that then no records are imported.

Tried numerous variations of the above .bat file, but all to no avail and I
have the feeling
that I am overlooking something simple here. Must admit, I am not familiar
with .bat files.
Ideally I would like to run this all from VB, with no .bat file with
CreateProcessA and WaitForSingleObject
etc., but I should get this simple .bat file fixed first.

Any suggestions?


RBS

[Carlos Rocha]

On Sunday, May 19, 2013 1:46:00 PM UTC+1, bart.sm...@gmail.com wrote:
> If I put in in a .bat file though:
>
> @ECHO OFF
> Cd C:\SQLite
> SQLite3 Test.db3
> .mode csv
> .import Test.txt Table1
>
> and run that then no records are imported.
>

I guess that when you enter SQLite3 shell (after the 1st command) the batch can't work. Maybe you can write some kind of script and run it from SQLite3 shell.

Carlos

[RB Smissaert]

> Maybe you can write some kind of script and run it from SQLite3 shell

How would I do that?
I have a feeling though that this must be possible from a simple .bat file.

RBS



"Carlos Rocha" <jazzd...@gmail.com> wrote in message
news:e3051e96-0166-454b...@googlegroups.com...

[ralph]

Try putting the commands in a 'response' file. Then running it like
this ...

SQLite3 @mycommands.txt (Extension doesn't matter.)

Not sure if this will work or not. (feature may not have been ported
from unix versions.) The commands in the file will be identical to
what you would type using the SQLite Shell.

[Carlos Rocha]

On Sunday, May 19, 2013 3:07:38 PM UTC+1, bart.sm...@gmail.com wrote:
> > Maybe you can write some kind of script and run it from SQLite3 shell
>
> How would I do that?

Create a text file with the commands you want to send and call

sqlite3 < commands.txt

I just tried with the following simple commands and it worked:

.echo on
.timer on
.timer off

I don't know how to create a file tho, but it should be possible.

Carlos

[Carlos Rocha]

On Sunday, May 19, 2013 4:33:12 PM UTC+1, Carlos Rocha wrote:

>
> I don't know how to create a file tho, but it should be possible.
>

Yes, it's possible:

sqlite3 test.db < commands.txt

this command creates test.db (if it doesn't exist) and executes the commands inside commands.txt

Carlos

[RB Smissaert]

I had tried that already and didn't work, but had another go with it
and now I got it finally to work with this:

In .bat file:

@ECHO OFF
Cd C:\SQLite

SQLite3 Test.db3 <@Commands.sql

In the file @Commands.sql:

.mode csv
.import Test.txt Table1

I can run the .bat file from VB simply like this:

Dim oShell As Object

Set oShell = CreateObject("WSCript.shell")
oShell.Run "cmd /C C:\SQLite\Test.bat", 0
Set oShell = Nothing

Looks all sorted then.
So the missing bit was the < before the command file.


RBS



"ralph" <nt_con...@yahoo.com> wrote in message
news:vmqhp81ln1ifkirbq...@4ax.com...

[RB Smissaert]

Thanks, got it working now, see other reply.

RBS


"Carlos Rocha" <jazzd...@gmail.com> wrote in message

news:6b3ccfc7-38ad-424a...@googlegroups.com...

[ralph]

On Sun, 19 May 2013 16:58:54 +0100, "RB Smissaert"
<bart.sm...@gmail.com> wrote:

>I had tried that already and didn't work, but had another go with it
>and now I got it finally to work with this:
>
>In .bat file:
>
>@ECHO OFF
>Cd C:\SQLite
>SQLite3 Test.db3 <@Commands.sql
>
>In the file @Commands.sql:
>
>.mode csv
>.import Test.txt Table1
>

...

>Looks all sorted then.
>So the missing bit was the < before the command file.
>

Should have caught that, or at least suspected it.
Unix shells use the redirectional operator "<" to do the same.
Dos utilities use the CPM/DEC "@" prescript originally, but later
utilities (expecially those imported from UNIX) use the "<".

It looks like the authors of Windows SQLite decided to cover both
bases.

[Larry Serflaten]

On Sunday, May 19, 2013 7:46:00 AM UTC-5, bart.sm...@gmail.com wrote:
> Trying to import a text file into SQLite with the .import command that is
> part of sqlite3.exe.
>
> No problem at all doing this manually from the command prompt:

But .import is a SQLite command. I'd suggest the direction to look would be
to see if SQLite supports scripting, and then how to do that on the fly from
VB...

LFS

[Tony Toews]

On Sun, 19 May 2013 13:46:00 +0100, "RB Smissaert"
<bart.sm...@gmail.com> wrote:

>@ECHO OFF

The above line means to not show any command input or output from any
of the subsequent lines including errors.

Remove the above line and execute the rest of the lines. Then maybe
you'll see an error message.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

[ObiWan]

> Any suggestions?

http://stackoverflow.com/questions/660320/how-to-automate-a-process-with-the-sqlite3-exe-command-line-tool

[RB Smissaert]

One thing to add that might be useful to somebody:
Had another computer (Win XP) where I couldn't get this to work.
Turned out this was caused by the line:
Cd C:\SQLite
not working, so not changing the current directory.
The solution was to change it to:
Pushd C:\SQLite
Never had seen the PushD before, but found it after some Googling.
Maybe the problem with cd was to do with the fact that this machine has
Sophos disc encryption.

RBS


"ralph" <nt_con...@yahoo.com> wrote in message

news:3u9ip8hgmobbv0qi1...@4ax.com...

[Deanna Earley]

On 21/05/2013 18:50, RB Smissaert wrote:
> One thing to add that might be useful to somebody:
> Had another computer (Win XP) where I couldn't get this to work.
> Turned out this was caused by the line:
> Cd C:\SQLite
> not working, so not changing the current directory.
> The solution was to change it to:
> Pushd C:\SQLite
> Never had seen the PushD before, but found it after some Googling.
> Maybe the problem with cd was to do with the fact that this machine has
> Sophos disc encryption.

Note that an undecorated CD does not change the current drive.
If it was D:\wibble then a CD C:\sqlite would change the current
directory on the C: drive but leave the d: drive (and current drive)
unchanged.

Try CD /D
> Use the /D switch to change current drive in addition to changing current
> directory for a drive.

--
Deanna Earley (dee.e...@icode.co.uk)
iCatcher Development Team
http://www.icode.co.uk/icatcher/

iCode Systems

(Replies direct to my email address will be ignored. Please reply to the
group.)

[ObiWan]

> >>In .bat file:
> >>
> >>@ECHO OFF
> >>Cd C:\SQLite
> >>SQLite3 Test.db3 <@Commands.sql

it's quite OT, I think; anyhow try

@echo off
:
rem --- set disk/folder
pushd C:\SQLite
:
rem --- create a temp filename
set SQLCMD=%TEMP%\sql%RANDOM%.txt
:
rem --- populate the command file, see
rem --- http://www.sqlite.org/sqlite.html
echo .mode csv >%SQLCMD%
echo .import Test.txt Table1 >>%SQLCMD%
echo .exit >>%SQLCMD%
:
rem --- execute the command file
SQLite3 Test3.db <%SQLCMD%
:
rem --- cleanup and terminate
del %SQLCMD%
set SQLCMD=
popd

the above will change the current drive/folder, set the environment
variable "SQLCMD" to a temporary, random file name, fill the file with
the SQLite commands, invoke "sqlite3.exe" feeding it the command file
and then clean after itself and restore the initial drive/folder;
notice that when populating the command file the first "echo" row uses
a single redirect sign ">" to create the file while the others use a
double one ">>" to append the other lines to it

[RB Smissaert]

OK, thanks, didn't realise that.

RBS

"Deanna Earley" <dee.e...@icode.co.uk> wrote in message
news:knhuqf$uq8$1...@speranza.aioe.org...