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

Question

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?

Answer 1

Here's an answer stolen shamelessly from Avi which is why it's community wiki:

I recommend looking at the TextFieldParserClass built into .Net rather than using Oledb. You need to include

Imports Microsoft.VisualBasic.FileIO.TextFieldParser

Here's a quick sample:

        Dim afile As FileIO.TextFieldParser = New _
          FileIO.TextFieldParser(FileName)
        Dim CurrentRecord As String() ' this array will hold each line of data '
        afile.TextFieldType = FileIO.FieldType.Delimited
        afile.Delimiters = New String() {","}
        afile.HasFieldsEnclosedInQuotes = True

        ' parse the actual file '
        Do While Not afile.EndOfData
            Try
                CurrentRecord = afile.ReadFields
            Catch ex As FileIO.MalformedLineException
                Stop
            End Try
        Loop

I have already upvoted the answer I have borrowed

Answer 2

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, for more info - http://bit.ly/7xjbSD

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

Thanks