Search code examples
vb.netcsvoledbjet

"is not a valid name" error while retrieving data from csv/txt file using oledb

I retrieving data from a csv/txt file using oledb driver 

ConnString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source = " & System.IO.Path.GetDirectoryName(strFileName) & "; Extended Properties = ""Text;HDR=YES;FMT=Delimited"""

strQuery = "SELECT * FROM [" & System.IO.Path.GetFileName(strFileName) & "]"

In the last line I am getting following error if the filename is large

is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.

My question - Is there any limit to filename or the select query or there is some pattern which I need to follow?

Solution

  • I didn't exactly found out what the problem was but it is clear that long file name was creating problem so here is what I did

    ConnString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source = " & System.IO.Path.GetDirectoryName(strFileName) & "; Extended Properties = ""Text;HDR=YES;FMT=Delimited"""

    '=================================== OleDB Supports fixed length file name. Handle long file names'
    Dim OldFileName As String = System.IO.Path.GetFileName(strFileName)
    Dim NewFileName As String = Guid.NewGuid().ToString().Substring(0, 10) + System.IO.Path.GetExtension(OldFileName)
    Dim rootPath As String = System.IO.Path.GetDirectoryName(strFileName) + "/"

    'Rename file name'
    My.Computer.FileSystem.RenameFile(rootPath + OldFileName, NewFileName)
    strFileName = rootPath + NewFileName
    '===================================='

    Dim strQuery As String
    strQuery = "SELECT * FROM [" & System.IO.Path.GetFileName(strFileName) & "]"

    'Revert rename file name'
    '===================================================================='
    My.Computer.FileSystem.RenameFile(rootPath + NewFileName, OldFileName)
    strFileName = rootPath + OldFileName
    '===================================================================='

    I just renamed the file with a new Guid while I am retrieving the data after renamed it back to original file name.

    This is not exactly what I wanted, I am still looking for a better solution. Please post if someone find one.

    Thanks