Import text file with DATE column into MS Access Database with VB.NET or C#

[efiguero2000]

Hello,

I followed the thread "Import large text file to MS Access Database"
trying to solve a similar problem, but with no help.

I have a CSV file called "orders.csv" which I want to export to a MS
Access table.

The CSV file has the following structure:
ORDER,DATE,TIME,F_NAME,L_NAME
10001,3/10/2007,5:40:20,Rafael,Lindberg
10002,4/11/2007,6:50:10,Luis,Mcgary

The MS Acces table ("orders_tbl") has the following structure:
ORDER TEXT, 10
DATE DATE
TIME TEXT, 15
F_NAME TEXT, 30
L_NAME TEXT, 30

I tried the following code, but with no success:
'<------ begins code
Dim AccessConn As New
System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.
4.0;Data Source=c:\myPath\orders.mdb;User Id=Admin;Password=;")
AccessConn.Open()

Dim strSQL As String
strSQL = ""
strSQL += "INSERT INTO [orders_tbl] ("
strSQL += "ORDER,"
strSQL += "DATE, "
strSQL += "TIME, "
strSQL += "F_NAME, "
strSQL += "L_NAME) "
strSQL += "SELECT "
strSQL += "ORDER, "
strSQL += "DATE, "
strSQL += "TIME, "
strSQL += "F_NAME, "
strSQL += "L_NAME "
strSQL += "FROM "
strSQL += "[Text;DATABASE=C:\myPath;]"
strSQL += "."
strSQL += "[orders.csv]"
Dim AccessCommand As New System.Data.OleDb.OleDbCommand(strSQL,
AccessConn)
AccessCommand.ExecuteNonQuery()
AccessConn.Close()
'<--------- ends code

The error message I got is: "Syntax error in INSERT INTO statement."

The problem comes from the DATE and TIME fields, because if I don't
include these fields the code works. I have tried to change
in the access table the field type to Date or Text unsuccessfully.

It seems I have to do some previous transformations on the date and
time columns in the csv file BUT I don't have any idea.

Please any help appreciated!!

efiguero

 

[RobinS]

My guess is you need # delimiters around the dates and times in the file.
If I were you, I would import those fields as TEXT and convert them
afterwards. If they go together, you could import them, then do an update
query to concatenate them and convert them into a date and stick them in a
separate field.

Robin S.

 

[Cor Ligthert [MVP]]

Hi,

As thousand times written in this newsgroup. Use parameters.

http://www.vb-tips.com/dbpages.aspx?ID=87057b89-a61c-44b9-bbfa-f80b8e80394e

To create a DateTime which works in all cultures/languages

dim theDate as DateTime = New DateTime(yy,MM,dd,hh,mm,ss)

I hope this helps,

Cor

 

[Paul Clement]

On 12 Mar 2007 16:39:43 -0700, (e-mail address removed) wrote:

¤ Hello,
¤
¤ I followed the thread "Import large text file to MS Access Database"
¤ trying to solve a similar problem, but with no help.
¤
¤ I have a CSV file called "orders.csv" which I want to export to a MS
¤ Access table.
¤
¤ The CSV file has the following structure:
¤ ORDER,DATE,TIME,F_NAME,L_NAME
¤ 10001,3/10/2007,5:40:20,Rafael,Lindberg
¤ 10002,4/11/2007,6:50:10,Luis,Mcgary
¤
¤ The MS Acces table ("orders_tbl") has the following structure:
¤ ORDER TEXT, 10
¤ DATE DATE
¤ TIME TEXT, 15
¤ F_NAME TEXT, 30
¤ L_NAME TEXT, 30
¤
¤ I tried the following code, but with no success:
¤ '<------ begins code
¤ Dim AccessConn As New
¤ System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.
¤ 4.0;Data Source=c:\myPath\orders.mdb;User Id=Admin;Password=;")
¤ AccessConn.Open()
¤
¤ Dim strSQL As String
¤ strSQL = ""
¤ strSQL += "INSERT INTO [orders_tbl] ("
¤ strSQL += "ORDER,"
¤ strSQL += "DATE, "
¤ strSQL += "TIME, "
¤ strSQL += "F_NAME, "
¤ strSQL += "L_NAME) "
¤ strSQL += "SELECT "
¤ strSQL += "ORDER, "
¤ strSQL += "DATE, "
¤ strSQL += "TIME, "
¤ strSQL += "F_NAME, "
¤ strSQL += "L_NAME "
¤ strSQL += "FROM "
¤ strSQL += "[Text;DATABASE=C:\myPath;]"
¤ strSQL += "."
¤ strSQL += "[orders.csv]"
¤ Dim AccessCommand As New System.Data.OleDb.OleDbCommand(strSQL,
¤ AccessConn)
¤ AccessCommand.ExecuteNonQuery()
¤ AccessConn.Close()
¤ '<--------- ends code
¤
¤ The error message I got is: "Syntax error in INSERT INTO statement."
¤
¤ The problem comes from the DATE and TIME fields, because if I don't
¤ include these fields the code works. I have tried to change
¤ in the access table the field type to Date or Text unsuccessfully.
¤
¤ It seems I have to do some previous transformations on the date and
¤ time columns in the csv file BUT I don't have any idea.
¤

Both Date and Time are reserved words in Jet. Either rename the columns or enclose them within
brackets.


Paul
~~~~
Microsoft MVP (Visual Basic)