I have been browsing the Web for a good and simple class to handle delimited file imports. My current assignment has an import option that needs to deal with that. However, the current implementation (using StreamReader) is not good enough. It doesn't handle all the exceptions you encounter with delimited files. I found a number of examples on the Internet, but none of them really suited my needs. What I really missed was a simple example that I could extend so that it would suit my needs. So, being the developer that I am, I created my own class to import delimited files. After this was completed, I though I'd share it with others as an example.
The easiest way to process delimited files is to use a StreamReader object. You then simply open the file, read each line and then use the split method to get the various column values. For example:
public void ImportDelimitedFile(string filename, string delimiter)
{
using (StreamReader file = new StreamReader(filename))
{
string line;
while ((line = file.ReadLine()) != null)
{
if (line.Trim().Length > 0)
{
string[] columns = line.Split(delimiter, StringSplitOptions.None);
// Add code to process the columns
}
}
}
}
In a lot of cases this works just fine, but there are limitations to this scenario:
string.Split is therefore not an option. The above mentioned problems are eliminated when you use the Jet engine. The following code shows how a CSV file can be processed:
public void ImportCsvFile(string filename)
{
FileInfo file = new FileInfo(filename);
using (OleDbConnection con =
new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" +
file.DirectoryName + "\";
Extended Properties='text;HDR=Yes;FMT=Delimited(,)';"))
{
using (OleDbCommand cmd = new OleDbCommand(string.Format
("SELECT * FROM [{0}]", file.Name), con))
{
con.Open();
// Using a DataReader to process the data
using (OleDbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// Process the current reader entry...
}
}
// Using a DataTable to process the data
using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
{
DataTable tbl = new DataTable("MyTable");
adp.Fill(tbl);
foreach (DataRow row in tbl.Rows)
{
// Process the current row...
}
}
}
}
}
As you can see in the example, once you have the Command object, you have the option of using anything a command object will allow you to do. You could process the file using a DataReader object, create a DataTable object containing the data or even add a where clause to the CommandText of your Command object to specify better which data is to be imported.
Using this, and the information provided in this Microsoft article, I created a small class that allows you to import delimited files. The class is very basic, but can easily be extended to suit your specific needs. This class will solve the most important issues when you're going to use Jet as your import engine.
Listed below are some things you need to consider when you are importing delimited files, be it with this class or using custom code:
string. In these cases, you should create a Schema.Ini file that describes the type of value for each column. The class creates a Schema.Ini file before it opens the delimited file, but only to specify what the delimiter is. You may want to change this to use pre-defined INI files that describe your input file. Details on the Schema.Ini file can be found here. DataSet or DataTable object. It's also possible to use SqlBulkCopy to instantly insert all the data into a SQL server database. The information I used to build this class was found on the Internet. I used the following resources:
The code presented in the helper class is not an all-purpose import solution. It's just a basic class to help you build your own import class. If you need other import types, or a way to influence the content of the default Schema.ini file, you will need to do that yourself. If you find any problems, please feel free to point them out to me.
| You must Sign In to use this message board. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
