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?

score 64 · Accepted Answer · edited Apr 23 '12 at 21:50

64

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.

edited Apr 23 '12 at 21:50

Chris Missal

5,987
3
28
46

answered Sep 22 '09 at 05:11

sean

11,164
8
48
56

6

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. – Daniel Pryden Sep 22 '09 at 05:36
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, "\"))` – Tommy O'Dell Oct 13 '11 at 02:31
4

@TommyO'Dell or simply `Path.GetFileName(csvPath)` – Josh M. Apr 05 '13 at 19:59
Works perfectly! Thank you! – Stefan S Jun 19 '17 at 15:39
Exactly what i was missing. Thanks! – Atta H. Feb 08 '19 at 16:06

score 4 · Answer 2 · answered Sep 22 '09 at 05:35

4

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.

answered Sep 22 '09 at 05:35

Jay Riggs

53,046
9
139
151

3

I've used the "Fast CSV Reader", it's great. – Ty. Sep 22 '09 at 05:45
1

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). – Daniel Pryden Sep 22 '09 at 05:47
@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. – Jay Riggs Sep 22 '09 at 06:13

score 2 · Answer 3 · answered Sep 22 '09 at 04:18

2

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

answered Sep 22 '09 at 04:18

Mitch Wheat

295,962
43
465
541

1

@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. – Daniel Pryden Sep 22 '09 at 05:44

score 2 · Answer 4 · answered Sep 22 '09 at 05:42

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

score 1 · Answer 5 · answered Sep 22 '09 at 05:30

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

score 0 · Answer 6 · answered Sep 22 '09 at 05:14

0

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

\\computerName\shareName\path\

answered Sep 22 '09 at 05:14

AaronLS

37,329
20
143
202

score -1 · Answer 7 · answered Nov 06 '12 at 08:46

-1

try this, A Fast CSV Reader, efficient CSV parser

CsvReader

answered Nov 06 '12 at 08:46

littlecodefarmer758

968
2
10
23

c# reading csv file gives not a valid path

7 Answers7

Linked