Import CSV file to DataTable

I'm preparing a demo right now and for this I'm importing a CSV file to CRM. Especially the piece around the import of data from a CSV file to a DataTable is very generic. I'm sharing it with you guys for if it does make sense for you to use this as well.


class CSVReader
{
public System.Data.DataTable GetDataTable(string strFileName)
{
System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + System.IO.Path.GetDirectoryName(strFileName) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");
conn.Open();
string strQuery = "SELECT * FROM [" + System.IO.Path.GetFileName(strFileName) + "]";
System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
System.Data.DataSet ds = new System.Data.DataSet("CSV File");
adapter.Fill(ds);
return ds.Tables[0];
}
}


You can then use this class together with a code like this:

private void YourFunction()
{
CrmService service = new CrmService();
service.Credentials = System.Net.CredentialCache.DefaultCredentials;

CSVReader reader = new CSVReader();
DataTable dt = reader.GetDataTable("C:\\CoffeeContacts.csv");
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];

account acc = new account();
acc.name = dr["Company"].ToString();
service.Create(acc);
}
}

11 comments:

Anonymous said...

Hi!
This is Nice Blog!
Data Conversion Services provides all Convert Raw Data into MS Office.Conversion from Page maker to PDF format.Conversion from Word to HTML format.Conversion from Text to Word Perfect.Conversion from Text to Word to HTML and Acrobat Fine.

Anonymous said...

ping back from http://www.castorsoft.com/articles/2008.3.10.htm

Tim Hustler said...

Man, this is genius!! In knew there was something behind the Excel Magic and now i can use it's raw power

Thanks for posting it man :¬)

mkamoski said...

Do you know a way to do this if the CSV "file" is already in a string variable?

maatthias said...

This indeed looks very powerful. Do you think this type of code could replace the Data Migration tool? Or does that tool do stuff behind the scenes like run stored procs, code that you don't see?

Ronald Lemmen said...

Hi Matthias,

This piece of code does only load the data into a datatable in memory. From memory you would need to copy the data to crm. That is another approach for data migration compared to the data migration framework. You should think carefully about which approach you will be using when migrating data. Depending on how many entities, related data, multiple sources etc you should figure out what approach you will be using for the data migration. Both approaches (as well as others like using a 3rd party tool) do have their advantages and disadvantages.

Kind regards,
Ronald

Unknown said...

This is a nice once. However, I have a problem with it. How do I set the type of data retrieved? For example, I have a field with data 0123456789, and when retrieved, the 0 (zero) is missing. I assume it automatically converts to number format? What should I do?

Thanks.

Anonymous said...

VERY handy! Thanks!!

igjuana said...

Thanks so much! This is a sweeet way to handle csv files. God bless you.

keren said...

Thank you! quick and helpful :)
Keren

Unknown said...

We can use to import a .csv file to DataTable by using this line of code

DataTable dt=new DataTable("C:\test.csv");
after this we can read lines of this data table by using DataReader.