provider strings for CSV files?

  • Thread starter Thread starter Maury Markowitz
  • Start date Start date
M

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;
}
}
 
Maury said:
[...]
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?
Click to expand...

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
 
If you want help with ODBC connection strings, you really should post
your question to a forum where questions about ODBC are on-topic.
Click to expand...

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"?
Click to expand...

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
 
Maury said:
Perhaps you can suggest one? There's nothing with that name in it.
Click to expand...

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.
Click to expand...

If you say so.

Pete
 
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.
Click to expand...

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.
 
Back
Top