Import Script

[GM]

I have this external VBS script that gets called to import a file into an
access dbase. How do I define the script so that it knows that the first line
in the file requests.csv is a header row?

Thanks
Greg



Option Explicit
Dim ojet
Dim odb
Dim strsql
strsql = "insert into customers select * from
text;hdr=yes;database=\\path\to\import file\;].requests.csv"
Set ojet = CreateObject("dao.dbengine.36")
Set odb = ojet.Opendatabase("\\path\to\dbase")
odb.execute strsql
odb.Close

 

[John Nurick]

Hi Greg,

The "hdr=yes;" clause should be doing that already. The SQL statement
appends records to an existing table: do the field names in the header
match the field names in the table? If not, alias them in the SQL, e.g.

INSERT INTO Customers SELECT ID AS CustomerNum, First AS FirstName...

Or do you mean something other than a standard CSV header (list of field
names), e.g.

"ID","FirstName","LastName"

If you want to dump a header line, you'll need to use the
FileSystemObject to open the file and read it line by line, writing each
line except the first to a new file. Then import the new file.

If the answer is "none of the above", post back with more information.

I have this external VBS script that gets called to import a file into an
access dbase. How do I define the script so that it knows that the first line
in the file requests.csv is a header row?

Thanks
Greg



Option Explicit
Dim ojet
Dim odb
Dim strsql
strsql = "insert into customers select * from
text;hdr=yes;database=\\path\to\import file\;].requests.csv"
Set ojet = CreateObject("dao.dbengine.36")
Set odb = ojet.Opendatabase("\\path\to\dbase")
odb.execute strsql
odb.Close

 

[GM]

John, After looking at this further it is not the header row that is the
problem. I think that you touched on what it is. I am not defining the
columns correctly. By the way I think that script I am using came from one of
your posts. Thanks I will let you know if I have any further issues.

Thanks Greg

Hi Greg,

The "hdr=yes;" clause should be doing that already. The SQL statement
appends records to an existing table: do the field names in the header
match the field names in the table? If not, alias them in the SQL, e.g.

INSERT INTO Customers SELECT ID AS CustomerNum, First AS FirstName...

Or do you mean something other than a standard CSV header (list of field
names), e.g.

"ID","FirstName","LastName"

If you want to dump a header line, you'll need to use the
FileSystemObject to open the file and read it line by line, writing each
line except the first to a new file. Then import the new file.

If the answer is "none of the above", post back with more information.

I have this external VBS script that gets called to import a file into an
access dbase. How do I define the script so that it knows that the first line
in the file requests.csv is a header row?

Thanks
Greg



Option Explicit
Dim ojet
Dim odb
Dim strsql
strsql = "insert into customers select * from
text;hdr=yes;database=\\path\to\import file\;].requests.csv"
Set ojet = CreateObject("dao.dbengine.36")
Set odb = ojet.Opendatabase("\\path\to\dbase")
odb.execute strsql
odb.Close

 

[GM]

John,

I am posting the full script. I get an error at line 8 cahr 1
error=the insert into statement contains the following unknown field name
"i can;t type the characters that appear in the error" make sure that you
have typed the name correctly.

I am using the * so I am not specifing the fields. I have doible checked the
names in the the header to make sure that they match what is in the table.
They are correct. I am don't understand what is going on.

Option Explicit
Dim ojet
Dim odb
Dim strsql
strsql = "insert into customers select *
from[text;hdr=yes;database=\\py1fsshdp01\Shared
Data\Iridesse\Milkshake\;].requests.csv"
Set ojet = CreateObject("dao.dbengine.36")
Set odb = ojet.Opendatabase("\\py1fsshdp01\Shared
Data\Iridesse\Milkshake\customer dbase catalogs.mdb")
odb.execute strsql
odb.Close

Thanks Greg

 

[John Nurick]

The script looks OK. But "doible checking" the field names is not
enough. Quadruple check them, making sure that there's nothing funny
like spaces or foreign characters in the field names or inconsistent
quoting in the header record.

Once you - and preferably a colleague with fresh eyes - are certain of
that, suspect an inconsistency in the data in the CSV file. A superflous
comma or an unmatched or unescaped quote in a record somewhere could
cause this sort of problem.

IME problems importing text files are far more likely to stem from
problems in the file than from problems with the Access/Jet import
routines.

John,

I am posting the full script. I get an error at line 8 cahr 1
error=the insert into statement contains the following unknown field name
"i can;t type the characters that appear in the error" make sure that you
have typed the name correctly.

I am using the * so I am not specifing the fields. I have doible checked the
names in the the header to make sure that they match what is in the table.
They are correct. I am don't understand what is going on.

Option Explicit
Dim ojet
Dim odb
Dim strsql
strsql = "insert into customers select *
from[text;hdr=yes;database=\\py1fsshdp01\Shared
Data\Iridesse\Milkshake\;].requests.csv"
Set ojet = CreateObject("dao.dbengine.36")
Set odb = ojet.Opendatabase("\\py1fsshdp01\Shared
Data\Iridesse\Milkshake\customer dbase catalogs.mdb")
odb.execute strsql
odb.Close

Thanks Greg