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

[jitupatil_2007]

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

[JerryShaw]

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

[jitupatil_2007]

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.....

[JerryShaw]

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

[JerryShaw]

"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

[jitupatil_2007]

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

[JerryShaw]

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 [e-mail]

// Jerry

[jitupatil_2007]

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..

[harivb]

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.

[JerryShaw]

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

[mukharanja]

Gettting errro while importing csv file to sqlserver 2005.Data type of column is int.Csv file contains empty cells Error is:
The given value of type String from the data source cannot be converted to type int of the specified target column.

Please suggest solution

file_num int Checked
fips int Checked
state_agency_num int Checked
distchar int Checked
nces_lea_num int Checked
filler_1 int Checked
filler_2 int Checked
leaname text Checked

code:

SqlBulkCopy bulkCopy = new SqlBulkCopy(consstr,SqlBulkCopyOptions.TableLock);
SqlBulkCopy.DestinationTableName = tableToInsert.ToString();
//// You can mannualy set the column mapping by the following way.
// bulkCopy.ColumnMappings.Add("MSISDN", "MSISDN");
bulkCopy.BatchSize = 1000;

bulkCopy.WriteToServer(dt);

[AITM_1]

Hello,
I want to use this button click function in mvc application. Where should I write this function then ?
In controller?