[ACCEPTED]-c# reading csv file gives not a valid path-oledb

Accepted answer
Score: 62

Ok, I dug a little further and it seems 6 that my connection string is wrong. With 5 CSV files, you don't specify the actual 4 file name but the directory where it belongs, eg.

var fileName = string.Format("{0}{1}", AppDomain.CurrentDomain.BaseDirectory, "Uploads\\");
string connectionString = string.Format(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source={0}; Extended Properties=""text;HDR=YES;FMT=Delimited""", fileName);
OleDbConnection oledbConn = new OleDbConnection(connectionString);
var cmd = new OleDbCommand("SELECT * FROM [countrylist.csv]", oledbConn);

And 3 you specify the filename in the SelectCommand. What 2 a strange way of doing it. It's working 1 for me now.

Score: 4

I recommend you use a CSV parser rather 13 than using the OLEDB data provider.

Search 12 and you'll find many (free) candidates. Here 11 are a few that worked for me:

A portable and efficient generic parser for flat files (easiest to 10 use, IMO)
A Fast CSV Reader (easy to use, great for large 9 data sets)
FileHelpers library (flexible, includes 8 code generators, bit of a learning curve)

Typically 7 these will allow you to specify properties 6 of your CSV (delimiter, header, text qualifier, etc.) and 5 with a method call your CSV is dumped to 4 a data structure of some sort, such as a 3 DataTable or List<>.

If you'll be working 2 at all with CSV, it's worth checking out 1 a CSV parser.

Score: 2

The way to combine paths and filenames is 1 to use:

fullFilename = System.IO.Path.Combine(folderfilepath, Filename);

in your example:

var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Uploads\countrylist.csv");
Score: 2

If you're just trying to read a CSV file 8 with C#, the easiest thing is to use the 7 Microsoft.VisualBasic.FileIO.TextFieldParser class. It's actually built into the .NET 6 Framework, instead of being a third-party 5 extension.

Yes, it is in Microsoft.VisualBasic.dll, but that doesn't 4 mean you can't use it from C# (or any other 3 CLR language).

Here's an example of usage, taken 2 from the MSDN documentation:

Using MyReader As New _
   MyReader.TextFieldType = FileIO.FieldType.Delimited
   Dim currentRow As String()
   While Not MyReader.EndOfData
         currentRow = MyReader.ReadFields()
         Dim currentField As String
         For Each currentField In currentRow
      Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
      MsgBox("Line " & ex.Message & _
      "is not valid and will be skipped.")
      End Try
   End While
End Using

Again, this example is in VB.NET, but 1 it would be trivial to translate it to C#.

Score: 1

I had the same problem a few weeks ago trying 2 to do some Office 2007 automation and spent 1 too much time trying to fix it.

string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1;\"";
Score: 0

If the D drive is a mapped network drive 1 then you may need to use the UNC path:


More Related questions