To understand the limitation of ADO.NET parting a CSV file using the Microsfot Jet OLEDB provider (Microsoft.Jet.OLEDB.4.0) consider the following CSV file:
AdventureWorks.Person.Person.csv
Do not mistake the file for a table in AdventureWorks using a name qualified by database, schema and database object name (Database.Schema.DBObject). AdventureWorks.Person.Person.csv is a file residing on disk.
The code that should be able to generate a DataTable from the AdventureWorks.Person.Person.csv CSV file is as follows:
public static DataTable GetDataTableFromCSV(
string folderPath, string csvFileName)
{
DataTable table = new DataTable();
string connectionText = String.Format(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}'" +
";Extended Properties='text;HDR=Yes';",
folderPath);
string commandText = String.Format(
"SELECT * FROM [{0}]", csvFileName);
using (OleDbConnection connection =
new OleDbConnection(connectionText))
{
connection.Open();
using (OleDbDataAdapter command = new
OleDbDataAdapter(commandText, connection))
{
command.Fill(table);
command.Dispose();
}
connection.Close();
}
return table;
}
private const string _csvFilename =
"AdventureWorks.Person.Person.csv";
static void Main(string[] args)
{
DataTable table;
string executableFilename = Environment.GetCommandLineArgs()[0];
string executablePath =
Path.GetDirectoryName(executableFilename);
try
{
string fullyQUalifiedFilename =
Path.Combine(executablePath, _csvFilename);
if (File.Exists(fullyQUalifiedFilename))
{
Console.WriteLine("File found on disk: " +
fullyQUalifiedFilename);
}
table = GetDataTableFromCSV(executablePath, _csvFilename);
}
catch (Exception ex)
{
Console.Error.WriteLine("Error: " + ex.Message);
}
}
The previous code tests using File.Exists that the file exists on disk:
if (File.Exists(fullyQUalifiedFilename))
{
Console.WriteLine("File found on disk: " +
fullyQUalifiedFilename);
}
{
Console.WriteLine("File found on disk: " +
fullyQUalifiedFilename);
}
The File.Exists method in the previous code returns try as the file exists on disk.
The command passed to the OleDbDataAdapter object is as follows:
string commandText = String.Format(
"SELECT * FROM [{0}]", csvFileName);
"SELECT * FROM [{0}]", csvFileName);
Notice the that the filename is surrounded by square brackets. The square brackets should allow the query to handling filenames that contain special characters such as periods or spaces. The previous command text generates an exception at the line of code indicated below (command.Fill(table);):
string commandText = String.Format(
"SELECT * FROM [{0}]", csvFileName);
using (OleDbConnection connection =
new OleDbConnection(connectionText))
{
connection.Open();
using (OleDbDataAdapter command =
new OleDbDataAdapter(commandText, connection))
{
command.Fill(table); // Exception generated here
command.Dispose();
}
The irony is that the follow T-SQL executes successfully when a table name contains period and a space:
SELECT * FROM [NameWithPeriod.AndA Space]
To further test ADO.NET's accessing CSV files with the jet provider the following files will be tested:
- FileWithA Space.csv: succeeds when surrounded by square brackets
- FileWithA-Dash.csv: succeeds when surrounded by square brackets
- FileWithLone.Period.csv: fails even when surrounded by square brackets
A second test will be run on the files but this time using the following command text where the CSV file name is not surrounded by square brackets:
string commandText = String.Format(
"SELECT * FROM {0}", csvFileName);
"SELECT * FROM {0}", csvFileName);
When the application is running without the square brackets the following behavior is exhibited for the same CSV files:
- FileWithA Space.csv: fails
- FileWithA-Dash.csv: fails
- FileWithLone.Period.csv: fails
The error generated by attempting to access the previous CSV files without using square brackets is as follows:
Syntax error in FROM clause.
Conclusion
Clearly using square brackets around the CSV file name in the command text should be part of any project using ADO.NET to parse CSV files. The square brackets allow spaces in the filename and dashes in the filename.
Recall in an early entry the 2004 MSDN article was referenced as introducing accessing CSV files using ADO.NET and the Jet OLEDB Provider: Much ADO About Text Files. Be aware that previously referenced article does not use square brackets. As was mentioned in a previous post, the article does not surround the folder path in the connection string with single quotes. The single quotes around the folder path in the connection string and the square brackets around the CSV filename in the command text should be standard operation procedure when coding using this technology.
Accessing Source Code
The source code for this project can be found at GitHub at https://github.com/softwarepronto/Blog. The solution containing the code is ADODotNetReadingCSVFiles. The projects of note are:
- CSVToDataTableUsingADODotNetBadFilename
- CSVToDataTableUsingADODotNetAFewBadFilenames
