c# reading csv file gives not a valid path

Question

I can't seem to read a .csv file using the following connection string:

var fileName = string.Format("{0}{1}", AppDomain.CurrentDomain.BaseDirectory, "Uploads\\countrylist.csv");
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);
oledbConn.Open();

It gives the following error:

'D:\arrgh\arrgh\Uploads\countrylist.csv' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.

I verified that the file is there. What is happening here?

Diphtheria

Answer 1

Ok, I dug a little further and it seems that my connection string is wrong. With CSV files, you don't specify the actual 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);
oledbConn.Open();
var cmd = new OleDbCommand("SELECT * FROM [countrylist.csv]", oledbConn);

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

Diphtheria

Also note that if you're using the Microsoft Jet OLEDB driver to read CSV files, you won't be able to read any CSV file that has more than one dot in its filename. That is, "filename.csv" will work, but "file.name.csv" won't. – Doublure

And note that you want to use sql = SELECT * FROM myfile.csv (i.e. filename without the path). You can extract the filename from the full path using csvFile = Right(csvPath, Len(csvPath) - InStrRev(csvPath, "\")) – Encephalography

@TommyO'Dell or simply Path.GetFileName(csvPath) – Whelm

Works perfectly! Thank you! – Sheenasheeny

Exactly what i was missing. Thanks! – Surtax

Answer 2

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

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

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

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

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

Alisiaalison

I've used the "Fast CSV Reader", it's great. – Graticule

While I agree that using a CSV parser is probably the best solution (see my answer for an alternative that is actually built into the .NET Framework already), there may be cases that using the Microsoft Jet OLEDB driver is useful. One useful property is that it can detect the data types of CSV columns, which I've used in the past when writing code to translate CSV files into a different format (DBF in my case). – Doublure

@Daniel - Interesting about OLEDB provider, I did not know that. I'm not sure I'd want to use this feature though, prefering to explicitly set (and check) these things myself. Thanks for the info. – Alisiaalison

Answer 3

The way to combine paths and filenames is to use:

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

in your example:

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

Choriamb

@seanlinmt: It's not your only problem, but this is very sound advice. Never do path manipulations as strings -- always use the System.IO.Path helper functions. Otherwise, there will always be that one edge case that bites you. – Doublure

Answer 4

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

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

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

Using MyReader As New _
Microsoft.VisualBasic.FileIO.TextFieldParser("C:\testfile.txt")
   MyReader.TextFieldType = FileIO.FieldType.Delimited
   MyReader.SetDelimiters(",")
   Dim currentRow As String()
   While Not MyReader.EndOfData
      Try
         currentRow = MyReader.ReadFields()
         Dim currentField As String
         For Each currentField In currentRow
            MsgBox(currentField)
         Next
      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 it would be trivial to translate it to C#.

Doublure

Answer 5

I had the same problem a few weeks ago trying to do some Office 2007 automation and spent 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;\"";

Behrens

Answer 6

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

\\computerName\shareName\path\

Clementineclementis

Answer 7

try this, A Fast CSV Reader, efficient CSV parser

CsvReader

Hallow