Importing a CSV file into Access from Command Line

[Karen Middleton]

I have a CSV file with the first row in the CSV file to be the column
headers.

The file is as follows:



Material, Customer, month,qty, sales
10000,19ABC, 122004, 90.5, 10000
20000,20ABC, 122004, 80.5, 12000
30000,21ABC, 122004, 70.5, 14000
40000,22ABC, 122004, 60.5, 16000
50000,23ABC, 122004, 50.5, 18000

I want to import this file from DOS command prompt command line into
Access.

There are tools like Bulk Insert in SQL Server or sqlloader in Oracle
to import these files into the database.

How can I do this import into Access from a command line please advise
if there are some free tools that can do this from command line
importing a csv file into a Access database.

Thanks
Karen

 

[John Nurick]

Hi Karen,

The simplest way to do this is to ignore Access and use the DAO library
to execute a make-table or append query. Here's a simple VBScript, but
you can use any OLE-compatible scripting language.

'Sample VBScript to import data from a textfile into
'a table in an MDB database without opening Access
'Modify strSQL and filenames as required

Option Explicit

Dim oJet 'DAO.DBEngine
Dim oDB 'DAO.Database
Dim strSQL 'String


strSQL = "SELECT * INTO MyTable FROM
[Text;HDR=Yes;Database=D:\Folder\;].FileName#txt"

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase("D:\Folder\Database.mdb")

oDB.Execute strSQL

oDB.Close

 

[Karen Middleton]

Hi Karen,

The simplest way to do this is to ignore Access and use the DAO library
to execute a make-table or append query. Here's a simple VBScript, but
you can use any OLE-compatible scripting language.

'Sample VBScript to import data from a textfile into
'a table in an MDB database without opening Access
'Modify strSQL and filenames as required

Option Explicit

Dim oJet 'DAO.DBEngine
Dim oDB 'DAO.Database
Dim strSQL 'String


strSQL = "SELECT * INTO MyTable FROM
[Text;HDR=Yes;Database=D:\Folder\;].FileName#txt"

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase("D:\Folder\Database.mdb")

oDB.Execute strSQL

oDB.Close

I have a CSV file with the first row in the CSV file to be the column
headers.

The file is as follows:



Material, Customer, month,qty, sales
10000,19ABC, 122004, 90.5, 10000
20000,20ABC, 122004, 80.5, 12000
30000,21ABC, 122004, 70.5, 14000
40000,22ABC, 122004, 60.5, 16000
50000,23ABC, 122004, 50.5, 18000

I want to import this file from DOS command prompt command line into
Access.

There are tools like Bulk Insert in SQL Server or sqlloader in Oracle
to import these files into the database.

How can I do this import into Access from a command line please advised i
if there are some free tools that can do this from command line
importing a csv file into a Access database.

Thanks
Karen

Hi John

Many thanks the code works beautifully it can take a delimited file
and import it into Access preciselt what I wanted.

Also, can it do importing any delimited file like tab, csv, semicolon
delimited file into access.

Apart from it the only problem I found was what happens if the file is
existing can it include a smart to check if table is existing to take
a command line argument to import into an existing table or a command
line flag to drop the existing table and import the new values.

I am not good at VB scripting kindly can you provide the enhancement
to the code that will enable to do that.

Thanks
Karen

 

[Karen Middleton]

Hi Karen,

The simplest way to do this is to ignore Access and use the DAO library
to execute a make-table or append query. Here's a simple VBScript, but
you can use any OLE-compatible scripting language.

'Sample VBScript to import data from a textfile into
'a table in an MDB database without opening Access
'Modify strSQL and filenames as required

Option Explicit

Dim oJet 'DAO.DBEngine
Dim oDB 'DAO.Database
Dim strSQL 'String


strSQL = "SELECT * INTO MyTable FROM
[Text;HDR=Yes;Database=D:\Folder\;].FileName#txt"

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase("D:\Folder\Database.mdb")

oDB.Execute strSQL

oDB.Close

I have a CSV file with the first row in the CSV file to be the column
headers.

The file is as follows:



Material, Customer, month,qty, sales
10000,19ABC, 122004, 90.5, 10000
20000,20ABC, 122004, 80.5, 12000
30000,21ABC, 122004, 70.5, 14000
40000,22ABC, 122004, 60.5, 16000
50000,23ABC, 122004, 50.5, 18000

I want to import this file from DOS command prompt command line into
Access.

There are tools like Bulk Insert in SQL Server or sqlloader in Oracle
to import these files into the database.

How can I do this import into Access from a command line please advise
if there are some free tools that can do this from command line
importing a csv file into a Access database.

Thanks
Karen

John

Sorry, I missed something is it possible to modify this code so that
it can import the csv file into a Oracle or a SQL Database as well.

Thanks
Karen

 

[John Nurick]

Many thanks the code works beautifully it can take a delimited file
and import it into Access preciselt what I wanted.

Also, can it do importing any delimited file like tab, csv, semicolon
delimited file into access.

Apart from it the only problem I found was what happens if the file is
existing can it include a smart to check if table is existing to take
a command line argument to import into an existing table or a command
line flag to drop the existing table and import the new values.

I am not good at VB scripting kindly can you provide the enhancement
to the code that will enable to do that.

Hi Karen,

This one appends or imports depending on whether the table exists, and
can easily be modified to do just what you want. To get command line
arguments in VBScript, use the WScript.Arguments collection.

For example, you could use something like this to collect a single
optional Overwrite argument:

Dim blOverwrite

If WScript.Arguments.Count = 0 Then 'No argument
blOverwrite = False
Else
If WScript.Arguments(0) = "Overwrite" Then
blOverwrite = True
Else
blOverwrite = False
End If
End If

When appending, you must ensure that the structure of the textfile
matches the structure of the existing table.

'----------------------------------------------------
'Sample VBScript to import data from a textfile into
'a table in an MDB database without opening Access

'Modify DB_NAME, TBL_NAME, DATA_SOURCE as required
'and the code that builds strSQL as necessary.

'If TBL_NAME exists, appends to it; otherwise creates it.


Dim oJet 'As DAO.DBEngine
Dim oDB 'As DAO.Database
Dim oTDef 'As DAO.TableDef
Dim blTExists 'As Boolean
Dim strSQL 'As String

Const DB_NAME = "C:\Temp1\BoxWithinBox_Backup.mdb"
Const TBL_NAME = "My_Table"
Const DATA_SOURCE = "[Text;HDR=Yes;Database=C:\Temp\;].B1#txt"

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(DB_NAME)

For Each oTDef In oDB.TableDefs
If oTDef.Name = TBL_NAME Then
blTExists = True
Exit For
End If
Next

If blTExists Then
strSQL = "INSERT INTO " & TBL_NAME _
& " SELECT * FROM " & DATA_SOURCE & ";"
Else
strSQL = "SELECT * INTO " & TBL_NAME _
& " FROM " & DATA_SOURCE & ";"
End If

oDB.Execute strSQL

oDB.Close
'----------------------------------------------

 

[John Nurick]

Yes, though I don't know how extensive the modifications may need to be.
You may have to use ADO (the ADODB library) instead of DAO and create
DSN-less connections before executing the queries. Also there may be
security issues; wouldn't it be simpler on the whole to use Oracle's or
SQL Server's own tools?

 

[Karen Middleton]

Yes, though I don't know how extensive the modifications may need to be.
You may have to use ADO (the ADODB library) instead of DAO and create
DSN-less connections before executing the queries. Also there may be
security issues; wouldn't it be simpler on the whole to use Oracle's or
SQL Server's own tools?

Hi John

Many thanks for your inputs. Your help is greatly appreciated the code
you provided is cool I am able to import data into Access.

I am new to VB Scripting appreciate if somebody could provide a
similar ADO based code to import into SQL or Oracle a arbitrary CSV
delimited file.

Thanks
Karen

 

[John Nurick]

Hi Karen,

AFAIK if you are accessing the SQL Server or Oracle database via linked
tables in the MDB file, you can update these using DAO calls in a
VBScript as if they were native Access tables (assuming of course that
you have the permissions to do so).

Otherwise I really feel it would be better to use the server database's
own tools. In SQL Server I think the drill is to create a DTS package
that does the import, and then use the "dtsrun" command-line utility to
execute it. But many DBAs would go ape at the thought of a user's script
casually running make-table queries.

If you still want to go ahead with using VBScript to calling ADO to poke
a text file into SQL Server or Oracle, you've left the world of Access.
You'll probably find some pointers if you go to
http://groups.google.com/advanced_group_search and search in the
microsoft.public.scripting.vbscript or microsoft.public.data.ado
newsgroups for something like
adodb vbscript

If you still need help after that, post a question in one of those
groups.

Good luck!

 

[multicoms]

Hi Can any one help me please, i have the following code:

Option Explicit

Dim oJet 'As DAO.DBEngine
Dim oDB 'As DAO.Database
Dim oTDef 'As DAO.TableDef
Dim blTExists 'As Boolean
Dim strSQL 'As String

Format=Delimited(

Const DB_NAME = "e:\ftpdat\ftpdat.mdb"
Const TBL_NAME = "customer"
Const DATA_SOURCE = "[Text;HDR=Yes;Database=e:\ftpdat\].customer#csv"

Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase("ftpdat.mdb")

For Each oTDef In oDB.TableDefs
If oTDef.Name = TBL_NAME Then
blTExists = True
Exit For
End If
Next

If blTExists Then
strSQL = "INSERT INTO " & "customer" _
& " SELECT * FROM " & DATA_SOURCE & ";"
Else
strSQL = "SELECT * INTO " & "customer" _
& " FROM " & DATA_SOURCE & ";"
End If

oDB.Execute strSQL

oDB.Close

The script help me import my csv file well but i need to insert format delimited ; semicolon to separate the fields, and I need the first field to be as text type, please help edit my script.
Thanks in advance