provider strings for CSV files?

[Maury Markowitz]

I'm trying to write some generic code to use ODBC to load data from
various Excel files. I have this working fine for XLS, but I'd like to
expand it so it could also use tab- or comma-delimited files as well.
This isn't working, and the debugger can't find the source so...

Here is the code that fails. All inputs are valid and point to real
files in the correct format.

string cnn = GetExcelConnectionString(filename,
hasHeaderRow);
OleDbConnection conn = new OleDbConnection(cnn);
conn.Open();

If this code is called with an XLS is works fine. When I call it with
a CSV it fails inside the Open, claiming that the path is incorrect.
It is not. The code to build the string follows below.

Can anyone spot the problem?

Maury

=======

private static string GetExcelConnectionString(string filename, bool
hasHeaderRow)
{
string useHeaders = (hasHeaderRow) ? "Yes" : "No";
string type = Path.GetExtension(filename);

switch (type)
{
case ".xlsx":
return @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
filename + @";Extended Properties=""Excel 12.0 Xml;HDR" + useHeaders +
@";""";

case ".xls":
return @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
filename + @";Extended Properties=""Excel 8.0;IMEX=1;HDR" + useHeaders
+ @";""";

case ".csv":
return @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
filename + @";Extended Properties=""TEXT;IMEX=1;HDR" + useHeaders +
@";""";

case ".tab":
case ".txt":
return @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
filename + @";Extended Properties=""TEXT;FMT=TabDelimited;IMEX=1;HDR"
+ useHeaders + @";""";

default:
return null;
}
}

 

[Peter Duniho]

[...]
If this code is called with an XLS is works fine. When I call it with
a CSV it fails inside the Open, claiming that the path is incorrect.
It is not. The code to build the string follows below.

Can anyone spot the problem?

If you want help with ODBC connection strings, you really should post
your question to a forum where questions about ODBC are on-topic.

In the meantime: are you sure that the file path doesn't need quoting?
And why is "HDRYes" and "HDRNo" a valid way to specify the headers?
Shouldn't that be "HDR=Yes" or "HDR=No"?

I don't really know that much about ODBC connection strings, so maybe
those are non-issues. But then, that's the kind of response you get
when you post ODBC questions in a C# newsgroup.

Pete

 

[Maury Markowitz]

If you want help with ODBC connection strings, you really should post
your question to a forum where questions about ODBC are on-topic.

Perhaps you can suggest one? There's nothing with that name in it.

In the meantime: are you sure that the file path doesn't need quoting?
And why is "HDRYes" and "HDRNo" a valid way to specify the headers?
Shouldn't that be "HDR=Yes" or "HDR=No"?

As I stated, these strings work perfectly when the input file is a
XLS. As you can see in the code, the HDR is the same in both cases.

Maury

 

[Peter Duniho]

Perhaps you can suggest one? There's nothing with that name in it.

Sorry. You'd have to ask someone who's in the habit of dealing with
ODBC for the name of a forum specific to ODBC. Not many of those people
around here, but you could get lucky. Or, you could ask Google.

As I stated, these strings work perfectly when the input file is a
XLS. As you can see in the code, the HDR is the same in both cases.

If you say so.

Pete

 

[Jeff Johnson]

As I stated, these strings work perfectly when the input file is a
XLS. As you can see in the code, the HDR is the same in both cases.

connectionstrings.com disagrees. Perhaps your data just happens to match the
default HDR value (which I'm sure is what is being used since what you're
passing is an invalid keyword; connection string always use name/value
pairs) and everything coincidentally works.