How Read .CSV File and How to store that data int toto sql server 2005

Posted by Kummee under Regular Expressions on 6/8/2011 | Points: 10 | Views : 8074 | Status : [Member] | Replies : 4
Any body Help Me...

How Read .CSV File and How to store that data int to sql server 2005




Responses

Posted by: A4u6178 on: 6/8/2011 [Member] Starter | Points: 25

Up
0
Down
Hi Kummee,

Refer the following link.
http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/

Thanks & Regards;
aaa

Kummee, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Ndebata on: 6/8/2011 [Member] Starter | Points: 25

Up
0
Down
There are many ways to achieve this.
One ways can be
Step1:

Try to read all your data in to a datatable ( assuming 1st line of CSV represents the ColumnName)
var values = GetAllLines();

DataTable dt = new DataTable();
foreach (var item in values.FirstOrDefault())
{
if (!dt.Columns.Contains(item))
dt.Columns.Add(item);
}
var allvalues=values.Skip(1).ToList();
for (int i = 0; i < allvalues.Count; i++)
{
dt.Rows.Add(allvalues[i].Take(dt.Columns.Count).ToArray());
}

Description for Get All Lines function
private IEnumerable<string[]> GetAllLines()

{
string str;
using (StreamReader rd=new StreamReader(txtFileName.Text))
{
while ((str = rd.ReadLine()) != null)
{
yield return str.Split(',');
}
}
}

STEP2
Then you can use SQL Bulk Copy to Copy the Entire datatable to database.
Where you need to set the destination table and Column Mapping, in my case i assume the column name is same as that of datatable

SqlConnection dbconnection = new SqlConnection("your Connection string.");
SqlBulkCopy sqlblkcpy=new SqlBulkCopy(dbconnection);
dbconnection.Open();
sqlblkcpy.DestinationTableName = "<Table Name To Which you want to Copy your Data>";
foreach (DataColumn item in dt.Columns)
{
sqlblkcpy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(item.ColumnName, item.ColumnName));
}
if(dt.RecordCount>0)
sqlblkcpy.WriteToServer(dt);

Thanks,
Debata

Kummee, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Susanthampy on: 6/8/2011 [Member] [MVP] Bronze | Points: 25
Posted by: Susanthampy on: 6/8/2011 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi.

Also try this,

http://www.codeproject.com/KB/aspnet/ImportExportCSV.aspx

Regards,
Susan

Kummee, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response