CODEWITHSUNDEEP
vb.netcsvoledbjet
Utkarsh
Utkarsh

Reputation: 622

"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?

Upvotes: 1

Views: 6704

Answers (4)

SteveCinq
SteveCinq

Reputation: 1963

Having just struggled with this, I found that no part of the file path or name could contain spaces or single quotes or the file path would be determined to be invalid. This applied no matter what qualification or escaping of the name I tried.

After I removed all spaces and single quotes, everything was fine. Eg.

Invalid:

"C:\Users\Administrator\Documents\Data\User''s Folder\Import Data.csv"

Valid:

C:\Users\Administrator\Documents\Data\Users-Folder\ImportData.csv

Upvotes: 2

Rory MacLeod
Rory MacLeod

Reputation: 11160

Some experimentation has shown that the file name without the extension must not contain a ".". So, MyData.csv is a valid file name, but My.Data.csv is not.

Upvotes: 1

Utkarsh
Utkarsh

Reputation: 622

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

Upvotes: 0

MarkJ
MarkJ

Reputation: 30398

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

Upvotes: 1

Related Questions