locked
Import CSV to MDB (ASP.NET 1.1 VB) RRS feed

  • 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.

    Saturday, April 21, 2012 12:15 PM

Answers

All replies

  • 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.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, April 21, 2012 5:21 PM
  • 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!

    Sunday, April 22, 2012 10:17 PM
  • User673645988 posted

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

    Sunday, June 10, 2012 7:56 AM