Question 186181 in ASP.NET Question posted in
The official documentation can be found here.

Syntax error in FROM clause in ASP.net vb while querying csv file

In my ASP .NET application (using vb.net), I am getting error "syntax error in FROM clause" while querying a csv file using OLEDB connection.

   connString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & 
   Server.MapPath(strFolderPath) & ";Extended 
   Properties=""Text;HDR=Yes;FMT=Delimited(,)"";"

   conn = New OleDbConnection(connString)
   query = "SELECT * FROM " & FilePath
   'File path is C:DEVUploadsfile.csv
   cmd = New OleDbCommand(query, conn)
   da = New OleDbDataAdapter(cmd)
   ds = New DataSet()
   da.Fill(ds, "Putaway") 'here throwing error

Please how to solve it.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I solved the issue when directly used the csv file name without fullpath.

     query = "SELECT * FROM file.csv"
 'File path is C:DEVUploadsfile.csv

  2. 0

    For starters, don’t use OLEDB to read CSV files. CSV files are just text files with Values Separated by Commas. Nothing more. You can read the data as text, or use a library like CsvHelper that parses CSV files and can even wrap them in a DataReader.

    For example, using CsvHelper’s CsvDataReader class you can replace your code with :

    Using reader As New StreamReader("path\to\file.csv")
    Using csv As new CsvReader(reader, CultureInfo.InvariantCulture)
        Using dr As new CsvDataReader(csv)
           Dim dt As new DataTable()
            dt.Load(dr)
            ...
        End Using
    End Using
End Using

    This code will work both on Windows and Linux, both for 32-bit and 64-bit applications

    The Access driver used in this code has several problems and is avoided in web applications. It was created to allow Office applications to read Access and Excel and CSV files, not for general use. CSV files are supported in a quirky way

    1. It requires installing the ACE engine
    2. OLEDB only works on Windows, so can’t work at all in ASP.NET Core
    3. The ACE engine must match the web app’s bitness (32/64-bit). Worse, you can’t install both versions at the same time.
    4. The ACE’s bitness must match any installed Office applications.

    That driver treats a folder as the "database" and file names in that folder as table names. That means that FilePath must be file.csv, not the full path C:DEVUploadsfile.csv

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search