Importing file.csv into Access using a VBS

[Carlos Garcia via AccessMonster.com]

I'd like to know if somebody has a VBS to import a CSV file into Access
using VBS. The first lines of my CSV file are below. Thank you in advance
for your help.

Example: File.CSV
Page Device Group ItemID Item Value
Report Device1 257 Version Value1
Report Device2 258 Author Value2

 

[John Nurick]

Hi Carlos,

This is a VBS procedure that creates a table in an MDB file containing
the data from a CSV file.

Sub TextImport(MDBFile, TableName, FolderPath, TextFileName, Extension)
'VBS to create a table in MDBFile containing the
'data in a textfile

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

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

strSQL = "SELECT * INTO " & TableName & " IN '" & MDBFile _
& "' FROM [Text;HDR=Yes;Database=" & FolderPath _
& ";].[" & TextFileName & "#" & Extension & "];"
oDB.Execute strSQL

oDB.Close
End Sub

Note that it will only work with a true CSV file. Your sample data looks
as if it is tab-separated text rather than comma-separated; in that case
you will need to provide a schema.ini file in the same folder as the
text file. See these links for more information on schema.ini
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Create a Schema.ini file based on an existing table in your database:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155512

http://support.microsoft.com/default.aspx?scid=kb;EN-US;149090
http://www.devx.com/tips/Tip/12566

I assume you're sufficiently familiar with VBS to do the rest.

 

[Carlos Garcia via AccessMonster.com]

Hi John,
Thank you very much! It worked well. There is another thing that I forgot
to mention on my previous question. By using your code, We are creating a
new table into the database, but I'd like to test first if the table
already exist and if yes, I'd like to just insert the files not create the
new table into the Database. I'd appreciate, If you could give this other
part of the thing. Thank you! Carlos.

 

[John Nurick]

Hi Carlos,

Iterate through the database's TableDefs collection to see if the table
exists, e.g.

For j = 0 To oDB.TableDefs.Count - 1
If oDB.TableDefs(j).Name = TableName Then
MsgBox "Table exists"
End If
Next

Then use an append query instead of a make-table one, i.e.

INSERT INTO NameOfTable SELECT * FROM [Text...

By the way, the IN clause in the SQL I gave you this morning is
unnecessary because the table is in the database whose .execute method
we are using. This should be enough:

strSQL = "SELECT * INTO " & TableName _
& " FROM [Text;HDR=Yes;Database=" & FolderPath _
& ";].[" & TextFileName & "#" & Extension & "];"

 

[Carlos Garcia via AccessMonster.com]

Hi John,

Thank you again! I have included below a peace of my script and I'm getting
this error --> "Table 'Table1' already exists, Code: 800A0BC2, source:
DAO.Database" Please, let me know how can I solve this. Thank you!

For j = 0 To oDB.TableDefs.Count - 1
If oDB.TableDefs(j).Name = TableName Then

strSQL = "INSERT INTO " & TableName _
& " SELECT * FROM [Text;HDR=Yes;Database=" & FolderPath _
& ";].[" & TextFileName & "#" & Extension & "];"
oDB.Execute strSQL

Else

strSQL = "SELECT * INTO " & TableName _
& " FROM [Text;HDR=Yes;Database=" & FolderPath _
& ";].[" & TextFileName & "#" & Extension & "];"
oDB.Execute strSQL
End If
Next

 

[Carlos Garcia via AccessMonster.com]

Hi John,

Thank you for your help. I was able to find a solution for the problem as I
wrote below.

For j = 0 To oDB.TableDefs.Count - 1

If oDB.TableDefs(j).Name = TableName Then

strSQL = "INSERT INTO " & TableName _
& " SELECT * FROM [Text;HDR=Yes;Database=" & FolderPath _
& ";].[" & TextFileName & "#" & Extension & "];"
oDB.Execute strSQL

End If

' strSQL = "SELECT * INTO " & TableName _
' & " FROM [Text;HDR=Yes;Database=" & FolderPath _
' & ";].[" & TextFileName & "#" & Extension & "];"
' oDB.Execute strSQL
' End If

Next

 

[John Nurick]

Hi Carlos,

I'd probably do something like this:

Dim TableExists 'boolean
Dim j 'counter

TableExists = False
For j = 0 To oDB.TableDefs.Count - 1
If oDB.TableDefs(j).Name = TableName Then
TableExists = True
Exit For
End If
Next

If TableExists Then
strSQL = "INSERT INTO " & TableName _
& " SELECT * FROM [Text;HDR=Yes;Database=" & FolderPath _
& ";].[" & TextFileName & "#" & Extension & "];"
Else
strSQL = "SELECT * INTO " & TableName _
& " FROM [Text;HDR=Yes;Database=" & FolderPath _
& ";].[" & TextFileName & "#" & Extension & "];"
End If

oDB.Execute strSQL

Hi John,

Thank you for your help. I was able to find a solution for the problem as I
wrote below.

For j = 0 To oDB.TableDefs.Count - 1

If oDB.TableDefs(j).Name = TableName Then

strSQL = "INSERT INTO " & TableName _
& " SELECT * FROM [Text;HDR=Yes;Database=" & FolderPath _
& ";].[" & TextFileName & "#" & Extension & "];"
oDB.Execute strSQL

End If

' strSQL = "SELECT * INTO " & TableName _
' & " FROM [Text;HDR=Yes;Database=" & FolderPath _
' & ";].[" & TextFileName & "#" & Extension & "];"
' oDB.Execute strSQL
' End If

Next

 

[freddylocks]

Problem

Hi there,

I am trying to get the above code to work, however i am struggling with it...
I have created a database using the following:

Sub CreateNewMDB(FileName, Format)
  Dim Engine 
  Set Engine = CreateObject("DAO.DBEngine.36")
  Engine.CreateDatabase FileName, ";LANGID=0x0409;CP=1252;COUNTRY=0", Format
End Sub
'Create Access2000 database
CreateNewMDB "c:\localdata\test\a2000.mdb", dbVersion40

and it creates the database with no problems and I am able to open the database in Access and use it like any other database.

However...
When using the code that was supplied above to create a table and enter the data in it (from a csv file, in correct format)
Then i get a error stating "Cannot update. Database or object is read-only" on Line 30.

Here is the code i am using to test this...

Dim oJet ' DAO.DBEngine
Dim oDB ' DAO.Database
Dim strSQL ' String
MDBFile = "c:\localdata\test\a2000.mdb"
TableName = "Table1"
Folderpath = "c:\localdata\test\"
TextFileName = Table1
Extension = csv
Set oJet = CreateObject("DAO.DBEngine.36")
Set oDB = oJet.OpenDatabase(MDBFile)
'strSQL1 = "CREATE TABLE Table1 (Field1 char(100),Field2 char(100))"
'oDB.Execute strSQL1

strSQL = "SELECT * INTO " & TableName _
& " FROM [Text;HDR=Yes;Database=" & FolderPath _
& ";].[" & TextFileName & "#" & Extension & "];"
oDB.Execute strSQL
oDB.Close

The strSQL1 statement is able to create a table in the database without any problems. Therefore i beleive it is something to do with the way that it is importing the data from the csv file?

Any help would be very much appreciated on this.
Many Thanks
Freddy