c# reading csv file gives not a valid path

Question

Ok, I must be missing something. 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.

But the file is there and I've quadruple checked the spellings. Is there some special permission that I have to give it? What gives???

Answer 1

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");

Answer 2

Ok, I dug a little further and it seems that my connection string is wrong. With CVS 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.

Answer 3

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

\\computerName\shareName\path\

Answer 4

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;\"";

Answer 5

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.

Answer 6

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#.

Answer 7

Wow. I would have thought this was older than September of '09. The CSV, in accordance with MS documentation, uses the file name as the table, and the directory as the data source. In Excel, you get to use the full filename with path because the excel file has multiple sheets which act as tables. Since the CSV and other flat, text files have only one, the directory is the source and the table name is the file name.

To get around the "File.Name.Extension" issue, change it to "File#Name#Extension". I haven't verified this, but it's what MS documentation says. Also, it might be possible to use FMT=Delimited(;), with a single-character delimiter within the parenthesis. This and much more can be done with a schema.ini file in the directory, but this file requires the filename in the schema, which is rather troublesome if you're generating files on the fly. You can also specify the delimiter in the HKLM hive of the registry, which is equally bothersome, since it applies to the entire machine. I doubt I'll test either, since my file is CSV formatted.

If all you want to do is parse a small CSV file, there is a file parser object in the framework that performs well. However, it is just a stream reader that parses the file, and you have to add the rows into a DataSet before you can query it. I'm looking for something I can query on before I work with the dataset. I should probably use LINK, but I really didn't expect it to be this much research, testing, and experimenting for such a common file format as CSV, especially when I already have an XL File ADO connection, and Excel can work with CSVs. Right? No? No!? NOOOO!!!

I hate this project.