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);
}
}
Hi!
ReplyDeleteThis 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
ReplyDeleteMan, this is genius!! In knew there was something behind the Excel Magic and now i can use it's raw power
ReplyDeleteThanks for posting it man :¬)
Do you know a way to do this if the CSV "file" is already in a string variable?
ReplyDeleteThis 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?
ReplyDeleteHi Matthias,
ReplyDeleteThis 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?
ReplyDeleteThanks.
VERY handy! Thanks!!
ReplyDeleteThanks so much! This is a sweeet way to handle csv files. God bless you.
ReplyDeleteThank you! quick and helpful :)
ReplyDeleteKeren
We can use to import a .csv file to DataTable by using this line of code
ReplyDeleteDataTable dt=new DataTable("C:\test.csv");
after this we can read lines of this data table by using DataReader.