how to import a csv file to sql server 2005 database table using C#
 

Hi friends i want to read a csv file and then store it in sql server 2005 table , know how to read a file but i don't know how do i write it in the table. plz help me

Re: how to import a csv file to sql server 2005 database table using C#
 

There are a number of ways to do this.
Using directing insert statement is one, another is using a stored procedure, calling as ssis package is another.

The easiest is probably the insert statement.
Simple get you SqlConnection, then use an SqlCommand.

use the string.Format method to parse the data into the command.

string Sql = string.Format("insert into foo(field1,field2) values('{0}',{1})",csv_value1,csv_value2);
SqlConnection conn = new SqlConnection(conectionstring)
conn.Open()
... Loop through all lines parsing the file into vars or an array
SqlCommand cmd = new SqlCommand(sql,conn)

cmd.ExecuteNonQuery();
... loop back
... finally close the connection, done

// Jerry

Re: how to import a csv file to sql server 2005 database table using C#
 

hi jerry i have read your code but i did not understand it how to implement it can you send my couple of code please i have tried it in this manner:

 string filepath = @"C:\Documents and Settings\jitendra\Desktop\RIC_SELL_080209.csv";

            int i = 0;         

            StreamReader sr = new StreamReader(filepath);

            string line = sr.ReadLine();

            string[] value = line.Split(',');

            DataTable dt = new DataTable();

            DataRow row ;

            foreach (string dc in value)

            {

                dt.Columns.Add(new DataColumn());

            }

            do

            {

                row = dt.NewRow();

                row.ItemArray = line.Split();

                dt.Rows.Add(row);

                i = i + 1;

                line = sr.ReadLine();

            }

            while (line != string.Empty);

            SqlBulkCopy bc = new SqlBulkCopy(con.ToString(), SqlBulkCopyOptions.TableLock);

            bc.DestinationTableName = "mytable";

            bc.BatchSize = dt.Rows.Count;

            con.Open();

            bc.WriteToServer(dt);

            bc.Close();

            con.Close();

on doing this i am getting an error of "Input array is longer than the number of columns in this table." so could you please help me.....

Re: how to import a csv file to sql server 2005 database table using C#
 

Here is a version of your code that was slightly modified, and works on a table with a few thousand rows.

The problem with the ""Input array is longer than the number of columns in this table." error is that the file has a column with an embedded comma. This causes the split to return additional columns.
This is a very typical problem with using CSV files. There is no way of knowing where the embedded comma is part of data, or used as a delimiter.
The only thing you can do is to inspect the line for commas, and replace them with a special character, then split, then find the special character and change it back into a comma.

private void button3_Click(object sender, EventArgs e)

        {

            SqlConnection con = new SqlConnection(@"Data Source=SHAWHP\SQLEXPRESS;Initial Catalog=FOO;Persist Security Info=True;User ID=sa");

            string filepath = "C:\\params.csv";

            StreamReader sr = new StreamReader(filepath);

            string line = sr.ReadLine();

            string[] value = line.Split(',');

            DataTable dt = new DataTable();

            DataRow row;

            foreach (string dc in value)

            {

                dt.Columns.Add(new DataColumn(dc));

            }



            while ( !sr.EndOfStream )

            {

                value = sr.ReadLine().Split(',');

                if(value.Length == dt.Columns.Count)

                {

                    row = dt.NewRow();

                    row.ItemArray = value;

                    dt.Rows.Add(row);

                }

            }

            SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);

            bc.DestinationTableName = "tblparam_test";

            bc.BatchSize = dt.Rows.Count;

            con.Open();

            bc.WriteToServer(dt);

            bc.Close();

            con.Close(); 

        }

// Jerry
PS If solved, please mark this topic as solved

Re: how to import a csv file to sql server 2005 database table using C#
 

"The only thing you can do is to inspect the line for commas, and replace them with a special character, then split, then find the special character and change it back into a comma."

After some thought, this statement probably won't work. If your CSV has the text columns wrapped in double quotes, then you can probably scrub the data to account for extra embedded commas but, if not... well you are kind-a screwed.

// Jerry

Re: how to import a csv file to sql server 2005 database table using C#
 

hi jerry shaw i have tried your code but it does not give any output and my machine also gets hanged..

Re: how to import a csv file to sql server 2005 database table using C#
 

Hopefully you adjusted the connection string to use your own server, and not mine ?

Did you walk it through the debugger and see what is happening ?

If you still can not get it to work, send me your project and csv file, and I will see what is wrong with it.

My email is shawhj@meadowcrk.com


// Jerry

Re: how to import a csv file to sql server 2005 database table using C#
 

he jerry your email address is not working i tried to send my project but its not working by the way i have created new code which is working fine and its up and running it can import about 20000 records from a file to a table the only problem occurs is that when i try to import a file having records having 500000 records i get this kind of error message

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.WriteSni()
at System.Data.SqlClient.TdsParserStateObject.WritePacket(Byte flushMode)
at System.Data.SqlClient.TdsParser.WriteByteArray(Byte[] b, Int32 len, Int32 offsetBuffer, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.WriteEncodingChar(String s, Int32 numChars, Int32 offset, Encoding encoding, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.WriteValue(Object value, MetaType type, Int32 actualLength, Int32 encodingByteSize, Int32 offset, TdsParserStateObject



see the code of mine goes here

 static void Main(string[] args)

        {

            string connectionstring = @"Data Source=ACC-222\SQLEXPRESS;Initial Catalog=mydata;Integrated Security=True";



            SqlConnection con = new SqlConnection(connectionstring);

            con.Open();



            SqlCommand mycount = new SqlCommand("select count(*) from dbo.mytable",con);



            long countstart = Convert.ToInt32(mycount.ExecuteScalar());



            Console.WriteLine("stating row count={0}", mycount);



           



            DbConnectionStringBuilder csb = new DbConnectionStringBuilder();



            csb["Provider"] = "Microsoft.Jet.OLEDB.4.0";



            csb["Data Source"] = @"C:\Documents and Settings\jitendra\Desktop";



            csb["Extended Properties"] = "Text;HDR=NO";



            string cs = csb.ConnectionString;



            OleDbConnection txtconnection = new OleDbConnection(cs);  



            txtconnection.Open();  



            OleDbCommand mycommand = new OleDbCommand("Select * from RIC_customer_20080318.csv", txtconnection);



            OleDbDataReader dr = mycommand.ExecuteReader(CommandBehavior.SequentialAccess);



            SqlConnection seccon = new SqlConnection(connectionstring);



            seccon.Open();



            SqlBulkCopy bc = new SqlBulkCopy(seccon);



            bc.DestinationTableName = "dbo.mytable";



            try

            {

                bc.WriteToServer(dr);



            }

            catch (SqlException se)

            {

                StreamWriter sr = new StreamWriter(@"C:\error.txt");

                

                Console.WriteLine(se.ToString());

                sr.WriteLine(se.ToString());

                Console.ReadLine();

            }

            dr.Close();



            long countend = Convert.ToInt32(mycount.ExecuteScalar());



            Console.WriteLine("Ending row count = {0}", countend);



            Console.WriteLine("{0} rows were added.", countend - countstart);



        }

now can you please help me... thanking you in advance..

Re: how to import a csv file to sql server 2005 database table using C#
 

You are getting timeout error means, Set your timeout = 0 in sqlconnection and try it. If it does not work, insert 5,000 records at a time and put it in loop. It works.

Re: how to import a csv file to sql server 2005 database table using C#
 

Curious as to why you are using the Jet driver ? (slow, unreliable)
Next, you should break the transactions up into chunks of < 10,000 lines per chunk.
You might also want to place an Application.DoEvents between chunks.

The Garbage collection subsystem any many other subsystems need a few cycles every now and then to do their job. IOW, make your loop play nice with the OS and Network.

// Jerry