Reading csv file in datatable
Recently I had to work on one problem where I had to read the csv file and filling up the data table with the csv file data. I used the OLEDBAdapter to accomplish that with just few lines of code. Following is the code :
string query = "SELECT Symbol, [Name of Company], FROM [just file name with extension]";
string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + [csv file path without file name] + ";" + "Extended Properties=’text;HDR=YES;’";//create dataadapter object
OleDbDataAdapter adapter = new OleDbDataAdapter(query, connStr);// create table
DataTable dtSymbolDetails = new DataTable("ScriptDetails");
dtSymbolDetails.Columns.Add("Symbol");
dtSymbolDetails.Columns.Add("Name of Company");// fill the table with data using adapter
adapter.Fill(dtDetails);
So above code fills up the details of the csv file in the data table for further use.
Also note the text in red above. If column name contains the space then in query the column name should be put between [ ]. I learned it in bit hard way. It shows I’m so poor in my sql knowledge.
Dear Concerned,
this code does’n work at my side. i’ll be very thankful if you can send me the proper code to read the data from .csv file & update into sql server databse.
thanks & regards,
Bipin Singh
Let me know what is the problem you are facing at your end then can suggest something.
Hello everyone,
I am facing a problem like this.
I am trying to develop a tool for import CSV files into existing tables in my DB.
but i’m still facing the problem of reading csv into datatable to copy or insert it into my DB table.
Any help?
worked just fine
glad to hear that it worked for you.
hello every one,
i m getting problem on defining the path of the file. my file is on c:\csvtestDesk.csv. then what is the path for connStr variable.
thanks
did u try putting just “c:\\”?
Change Microsoft.ACE.OLEDB.12.0 to Microsoft.Jet.OLEDB.4.0 for non 32 bit operating systems.
DataTable tbl = new DataTable(“MyTable”);
FileInfo file = new FileInfo(cFile);
if (file.Extension.ToUpper() == “.CSV”)
{
using (OleDbConnection con = new OleDbConnection(“Provider=Microsoft.ACE.OLEDB.12.0; Data Source=” + file.DirectoryName + “;Extended Properties=Text;”))
{
using (OleDbCommand cmd = new OleDbCommand(string.Format(“SELECT * FROM [{0}]“, file.Name), con))
{
con.Open();
using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
adp.Fill(tbl);
}
}
}
Thanks, Jarrett for this informstion.
Hi, I’ve written something similar that reads a CSV files (or files) and bulk uploads them to a DB: http://arranmaclean.wordpress.com/2010/07/20/net-mvc-upload-a-csv-file-to-database-with-bulk-upload/ hope this might help any of your readers.