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:
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.
ping back from http://www.castorsoft.com/articles/2008.3.10.htm
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 :¬)
Do you know a way to do this if the CSV "file" is already in a string variable?
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?
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
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.
VERY handy! Thanks!!
Thanks so much! This is a sweeet way to handle csv files. God bless you.
Thank you! quick and helpful :)
Keren
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.
Post a Comment