Problem While Importing Dta From The CSV File To Sqlserver 2005
Feb 7, 2008Hi,
In my project i have to import csv file into DB, i have written a C# windows application as below...
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;namespace FedExDataAnalysis
{
public partial class Form1 : Form
{public Form1()
{
InitializeComponent();
}protected string[] arrayValues;
//string row = "";
// int cnt = 0;private void Form1_Load(object sender, EventArgs e)
{string path = "c:\2008-01-09_01-00_FedExInv_232316837.csv";
importCSV(path);string path1 = "c:\2008-01-10_05-18_FedExInv_232316837.csv";
importCSV(path1);
}public void importCSV(string path)
{string row = "";int cnt = 0;
using (StreamReader sr = new StreamReader(path))
{while (sr.Peek() >= 0)
{
cnt = cnt + 1;
row = sr.ReadLine();arrayValues = row.Split(',');if (cnt > 2)
{if (arrayValues.Length > 0)
{
insertrecord();
}
}
}
}
}public void insertrecord()
{string str;
int Recordaffected;string inserted = "no";
string constr;SqlConnection conn;
SqlCommand cmd;
constr = "Data Source=I07;Initial Catalog=JuiceApp;User ID=sa;Password=dj$ql1736ju1c30ca1a";conn = new SqlConnection(constr);
conn.Open();
try
{str = "insert into xt_importcsv (Bill_to_accountno,Invoice_date,invoice_number,Original_ammountDue,Current_Balance,express_or_ground_trackingid,";
str = str + "transportation_charge_ammount,net_charge_ammount,shipment_date,actual_weight_amount,rated_weight_ammount,recipient_name,recipient_company,";str = str + "recipientaddress_line1,recipient_city,recipient_state,recipient_zipcode,recipient_country,shipper_name,trackingid_charge_description,trackingid_charge_ammount,";
str = str + "trackingid_charge_description1,trackingid_charge_amount1,trackingid_charge_description2,trackingid_charge_ammount2,trackingid_charge_description3,trackingid_charge_ammount3,";str = str + "trackingid_charge_description4,trackingid_charge_ammount4) values ('" + arrayValues[0].ToString().Replace(""", "") + "','" + arrayValues[1].ToString().Replace(""", "") + "','" + arrayValues[2].ToString().Replace(""", "") + "','" + arrayValues[4].ToString().Replace(""", "") + "','" + arrayValues[5].ToString().Replace(""", "") + "','" + arrayValues[8].ToString().Replace(""", "") + "','" + arrayValues[9].ToString().Replace(""", "") + "','" + arrayValues[10].ToString().Replace(""", "") + "','" + arrayValues[13].ToString().Replace(""", "") + "','" + arrayValues[18].ToString().Replace(""", "") + "','" + arrayValues[20].ToString().Replace(""", "") + "','" + arrayValues[25].ToString().Replace(""", "") + "','" + arrayValues[26].ToString().Replace(""", "") + "','" + arrayValues[27].ToString().Replace(""", "") + "','" + arrayValues[29].ToString().Replace(""", "") + "','" + arrayValues[30].ToString().Replace(""", "") + "','" + arrayValues[31].ToString().Replace(""", "") + "','" + arrayValues[32].ToString().Replace(""", "") + "','" + arrayValues[34].ToString().Replace(""", "") + "','" + arrayValues[80].ToString().Replace(""", "") + "','" + arrayValues[81].ToString().Replace(""", "") + "','" + arrayValues[82].ToString().Replace(""", "") + "','" + arrayValues[83].ToString().Replace(""", "") + "','" + arrayValues[84].ToString().Replace(""", "") + "','" + arrayValues[85].ToString().Replace(""", "") + "','" + arrayValues[86].ToString().Replace(""", "") + "','" + arrayValues[87].ToString().Replace(""", "") + "','" + arrayValues[88].ToString().Replace(""", "") + "','" + arrayValues[89].ToString().Replace(""", "") + "')";cmd = new SqlCommand(str, conn);
Recordaffected = cmd.ExecuteNonQuery();if (Recordaffected == 1)
{inserted = "yes";
}
}catch (System.Exception ex)
{Console.WriteLine(ex);
}
finally
{
conn.Close();
}
}
}
}
It is working fine.. It is importing data into DB. But the problem what m facing is , in the code above m using the split function and assigning the row of the CSV file into arrayValue. the prob is if the arrayValue has two entries lk: Name: "john,Mikel " it will split it separately and insert into the next field in the DB... M not getting how resolve this... Please help...
Thanks,
Chai.NV.