Import CSV to MDB (ASP.NET 1.1 VB)

Question

User673645988 posted

I have a SQL error in the select statement when I try importing data from a a CSV (delimited with comma) to MDB.

Code under buttons:

'This one works

strSQL = "INSERT INTO [target] (targetname, targetvalue) SELECT targetname, targetvalue FROM[Text;HDR=YES;DATABASE=C:\accessdb;].[test.csv]"

'This one doesn't work

strSQL = "INSERT INTO [Sheet1] (Campaign,Date,SPS,Account,PubCost,Clicks,Impr,QA,ACV,LoginConfirmationConv,Status,AvgPos)
SELECT Campaign,Date,SPS,Account,PubCost,Clicks,Impr,QA,ACV,LoginConfirmationConv,Status,AvgPos FROM[Text;HDR=YES;DATABASE=C:\accessdb;].[test2.csv]"

The headers match. The database location is fine. HDR is on. The 2nd one gives me an error. I don't know if it's the data itself or the format of the database.

The error is:

Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error in
INSERT INTO statement.

Source Error:


Line 49:         Dim objCommand As New
System.Data.OleDb.OleDbCommand(strSQL, cnn)
Line 50:         objCommand.CommandText = strSQL
Line 51:         objCommand.ExecuteNonQuery()
Line 52:         cnn.Close()
Line 53:     End Sub

Yes, it's 1.1. I cannot change version.

Thanks.

Answer 1

User-821857111 posted

Syntax error in INSERT INTO statement.

See here: http://www.mikesdotnetting.com/Article/76/80040E14-MS-Access-Syntax-Error-messages and then check the word "Date" in the list of reserved words for JET.

Answer 2

User3866881 posted

strSQL = "INSERT INTO [Sheet1] (Campaign,Date,SPS,A……

Suspecting it that you've used a key word——Date！

So in order to avoid this problem：

1）Please set around your field names with a pair of "[]"。

2）Do please to put the Access query in the form of SQL into Access and run it first and then paste it into your codes。

Reguards！

Answer 3

User673645988 posted

Got this one sorted out - it was the date. Thank you.