Import data file & update with SQL

I would like to be able to use a vbs script to
1) import a text file of a known, specific format into a table
2) Delete records that match a criteria (a delete SQL?)
3) Save to a new file.

Any pointers? or suggestions?

This does not have to be a DB/table solution, but I am an ACCESS
programmer with very basic vbs specific knowledge. Would like this to
be a simple drag and drop operation for the end user if possible.

What specific info do I need to supply to have an intelligent answer?

TIA

- Z

ekkehard
Mon Apr 10 15:02:03 CDT 2006

Zarqy wrote:
> I would like to be able to use a vbs script to
> 1) import a text file of a known, specific format into a table

You can access a text file using the ADO Text driver + a schema.ini
file to specify the format of the text file; to import the text file into
a SQL (= ADO/Jet) table you can use an "INSERT INTO .. IN/SELECT INTO .. IN"
statement

> 2) Delete records that match a criteria (a delete SQL?)

This could be a simple "DELETE FROM .. WHERE" statement with a condition
derived from user input (drag & drop?)

> 3) Save to a new file.

"INSERT INTO .. IN/SELECT INTO .. IN" again, but source and target
switched (SQL table => text file)

>
> Any pointers? or suggestions?
>
> This does not have to be a DB/table solution, but I am an ACCESS
> programmer with very basic vbs specific knowledge. Would like this to
> be a simple drag and drop operation for the end user if possible.

GUI suggests HTA application - or are you thinking "INTERNET/WEBSERVER/ASP"

>
> What specific info do I need to supply to have an intelligent answer?
>
> TIA
>
> - Z
>

Zarqy
Tue Apr 11 07:05:31 CDT 2006

THanks for the suggestions. The ""INSERT INTO .. IN/SELECT INTO .. IN"
is something that I wouldn't have thought of. I just recently came
across the "IN" clause to use and external file.

After I posted and was able to clear my head a little, I came up with
another possibility. I might try loading the file into an array (using
the split() function), and filtering out the records I don't want
before saving back to a file. This approach would remove the need for
additional files (the .mdb and schema.ini), which would make this
process much easier for the end user to deal with.

Any thoughts are still welcome! I'll let you know how this works.

- Z

ekkehard
Tue Apr 11 14:02:16 CDT 2006

Zarqy wrote:
> THanks for the suggestions. The ""INSERT INTO .. IN/SELECT INTO .. IN"
> is something that I wouldn't have thought of. I just recently came
> across the "IN" clause to use and external file.
>
> After I posted and was able to clear my head a little, I came up with
> another possibility. [...]

I'd still use ADO/TextDriver/schema.ini, because

> I might try loading the file into an array (using
> the split() function), [...]

if you want an array, look at the ADORecordset.GetRows() method

>and filtering out the records I don't want [...]

SQL filtering is 'cheaper' than rolling your own comparisons

> before saving back to a file. This approach would remove the need for
> additional files (the .mdb and schema.ini), [...]

no .mdb or IN needed, if you just
INSERT/SELECT INTO new.txt FROM old.txt WHERE magicondition

> which would make this
> process much easier for the end user to deal with.
>
> Any thoughts are still welcome! I'll let you know how this works.
>
> - Z
>

Zarqy
Wed Apr 12 10:50:43 CDT 2006

O.K., I found the info on creating a schema.ini file
(http://support.microsoft.com/kb/149090/EN-US/). I will need to create
the schema.ini from the script as the file name and/or location could
change. Then delete the schema.ini when I am done. No problem with FSO
or whatever.

Three questions:

1) Can the delimiter be this : "," not ,
some of the fields do contain commas

2) The input files does not have a header row. Do all the columns need
to be defined? Or can I just define the columns that I need to filter
on in the WHERE clause? There are 40 columns, Not to much of a problem
to define all, just easier to not have to.

3) Can the outpu be saved with "," as seperators, not just ,? We need
the double quote enclosed fields to catch the fields with commas

Thanks for all the help. I do appreciate your time and efforts!

- Z

ekkehard
Wed Apr 12 11:17:20 CDT 2006

Zarqy wrote:
> O.K., I found the info on creating a schema.ini file
> (http://support.microsoft.com/kb/149090/EN-US/). I will need to create

Another link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

> the schema.ini from the script as the file name and/or location could
> change. Then delete the schema.ini when I am done. No problem with FSO
> or whatever.
>
> Three questions:
>
> 1) Can the delimiter be this : "," not ,
> some of the fields do contain commas

I'd use "Format=TabDelimited", because I saw customer names like
Hotel "Zur Post"
and why not
The "Good", the 'bad', and the |ugly| Ltd.

> 2) The input files does not have a header row. Do all the columns need
> to be defined? Or can I just define the columns that I need to filter
> on in the WHERE clause? There are 40 columns, Not to much of a problem
> to define all, just easier to not have to.

You don't need a head row (ColNameHeader=False); but please define all
columns carefully - somebody (you in 4 week) will be grateful.

> 3) Can the outpu be saved with "," as seperators, not just ,? We need
> the double quote enclosed fields to catch the fields with commas
>
> Thanks for all the help. I do appreciate your time and efforts!

you are welcome

>
> - Z
>

Zarqy
Wed Apr 12 11:28:58 CDT 2006

I have no control over how the incoming text file is formatted. It is
coming from a commercial web application, so this is out of my hands.
All fields are enclosed in double quotes.

Will do with the column headers.

- Z

Zarqy
Wed Apr 12 11:36:11 CDT 2006

found this thread:

http://groups.google.com/group/microsoft.public.scripting.vbscript/browse_frm/thread/84175f2af663dc2f/0d3e11c8d059ffdc

The code from Mike Harris seems to indicate that the
Format=CSVDELIMITED
LIne in the Schema.ini will be fine with the ",".

I'll give this a shot, and see what happens.