Import CSV file into SQL using C#

Question

I'm trying to insert a CSV file into SQL Database using C#. I can read the csv file and insert it into the table, however any fields with an embedded comma are not being read correctly. How can I get around this. Below is the code that I'm using:

protected void UploadFile_Click(object sender, EventArgs e)
        {
            conStr = "workstation id=" + ServerName + ";packet size=4096;user id=xx;password=" yyyyy ";data source=" blahblah ";persist security info=False;initial catalog=";
            conStr = conStr + DB;

            string filepath = "D:\\Work\\Sample01.csv";
            StreamReader sr = new StreamReader(filepath);
            int NrLines = 0;
            string[,] mline;
            mline = new string[NrLines, 50];
            int cntra = 0;
            int counter = 0;

            using (StreamReader cr = new StreamReader(filepath))
            {
                while ((cr.ReadLine()) != null)
                {
                    NrLines++;
                }
                cr.Close();
            }

            mline = new string[NrLines, 25];

            for (int lcounter = 1; (lcounter <= NrLines); lcounter++)
            {

                string[] sline = sr.ReadLine().Split(',');
                //strElem = strElem.Append("");
                if (sline != null)
                {
                    for (int c = 0; c < sline.Length; c++)
                        mline[cntra, c] = sline[c];
                    cntra++;
                }
            }
            sr.Close();            

            for (counter = 1; counter < NrLines; counter++)
            {
                string Date = mline[counter, 0].ToString();
                string SiteUD = mline[counter, 1].ToString();
                string SiteName = mline[counter, 2].ToString();
                string ModelNo = mline[counter, 3].ToString();
                string MachID = mline[counter, 4].ToString();
                string Manufacture = mline[counter, 5].ToString();
                string TotalCashIn = mline[counter, 6].ToString();
                string TotalCashOut = mline[counter, 7].ToString();
                string NotesIN = mline[counter, 8].ToString();
                string CoinsIn = mline[counter, 9].ToString();
                string CoinsOut = mline[counter, 10].ToString();
                string CoinstoDrop = mline[counter, 11].ToString();
                string RemoteCashIn = mline[counter, 12].ToString();
                string RemoteCashOut = mline[counter, 13].ToString();
                string TotalWin = mline[counter, 14].ToString();
                string TotalBet = mline[counter, 15].ToString();
                string GGR = mline[counter, 16].ToString();
                string GamesPlayed = mline[counter, 17].ToString();
                string HandPays = mline[counter, 18].ToString();
                string HopperRefill = mline[counter, 19].ToString();

                     SQL = "INSERT INTO ztrewVNLCemsImport " +
                              "([Date],            [SiteUD],        [SiteName],        [ModelNo.],            [MachID], " +
                              "[Manufacture],   [TotalCashIn],  [TotalCashOut],    [NotesIN],            [CoinsIn], " +
                              "[CoinsOut],        [CoinstoDrop],    [RemoteCashIn],    [RemoteCashOut],    [TotalWin], " +
                              "[TotalBet],        [GGR],            [GamesPlayed],    [HandPays],            [HopperRefill] ) " +
                          "VALUES " +
                              "('" + Date + "',         '" + SiteUD + "',        '" + SiteName + "',     '" + ModelNo + "',            '" + MachID + "', " +
                              "'" + Manufacture + "',    '" + TotalCashIn + "',  '" + TotalCashOut + "',    '" + NotesIN + "',          '" + CoinsIn + "', " +
                              "'" + CoinsOut + "',        '" + CoinstoDrop + "',    '" + RemoteCashIn + "',    '" + RemoteCashOut + "',    '" + TotalWin + "', " +
                              "'" + TotalBet + "',        '" + GGR + "',            '" + GamesPlayed + "',    '" + HandPays + "',            '" + HopperRefill + "') ";
                SQL = SQL.Replace('\t', ' ');

Have you considered using `Microsoft.VisualBasic.FileIO.TextFieldParser` for parsing CSV files? It removes all the complexities associated with CSV files. — SF Lee, May 05 '14 at 06:59
So the , symbol is the separator for each column. Are the imbedded commas somehow marked as being text? like being in between 2 ' or 2 " ? If not then even a 3rd person csv parser will run into problems there. But if they are you can either use a 3rd person csv-parser OR adapt your code with the help of regex so that those , don't count. — Thomas, May 05 '14 at 07:03

score 2 · Answer 1 · edited May 23 '17 at 11:57

Try using a CSV-parser library to read the CSV file instead of doing it yourself. There is a bunch of libraries available: CSV parser/reader for C#?

After you have read in the CSV data you can write it back to the database using either an ORM or plain ADO.Net as you use in your example. If you write the SQL yourself you should make sure to use SQL parameters in your query. Not only will that guarantee that every data type is stored properly, but it will also protect you against any malicious content (not that that is very likely in your case).

The easiest solution would probably be to use an SqlDataAdapter based on your database table.

score 0 · Answer 2 · answered Nov 28 '18 at 07:26

Visual Studio 2017
File -> New -> Projcet -> Visual C# -> Console App (.NET Framework) -> File Name :-ImportCSVToSQL

3. Open "Program.cs" Write Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using System.IO;

namespace ImportCSVToSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(@"Data Source=**ServerName**; User ID = **; Password = **; Integrated Security=false"))
                {
                    conn.Open();

                    #region create table

                    using (SqlCommand createTable = new SqlCommand(@"
          IF EXISTS(SELECT TOP 1 *
          FROM  [DatabaseName].[dbo].[TableName])
          DROP TABLE [DatabaseName].[dbo].[TableName]            
          IF NOT EXISTS
        (
            SELECT *
            FROM
                sys.schemas s
                    INNER JOIN sys.tables t ON
                        t.[schema_id] = s.[schema_id]
            WHERE
                s.name = 'dbo' AND
                t.name = 'TableName'
        )
            CREATE TABLE [DatabaseName].[dbo].[TableName]
            (
                createddate varchar(500), 
                fid0 varchar(500),
                fid1 varchar(500),
                fid2 varchar(500),
                fid3 varchar(500),
                fpip varchar(500),
                pid varchar(500),
                isloggedin varchar(500),
                sessionid varchar(500),
                source varchar(500),
                useragent varchar(500),
                jpnumber varchar(500)
            )

            ;
        ", conn))
                    {
                        createTable.ExecuteNonQuery();
                    }

                    #endregion

                    using (var reader = new StreamReader(@"CSV FILE PATH"))
                    {
                        Console.WriteLine("******* Uploading Data ...... ***********************");
                        while (!reader.EndOfStream)
                        {
                            var line = reader.ReadLine();
                            var data = line.Split(',');
                            // data[0] data[1]

                            string query = @"Insert into [DatabaseName].[dbo].[TableName]" + " values ('" + data[0] + "','" + data[1] + "','" + data[2] + "','" + data[3] + "','" + data[4] + "','" + data[5] + "','" + data[6] + "','" + data[7] + "','" + data[8] + "','" + data[9] + "','" + data[10] + "','" + data[11] + "')";



                            SqlCommand cmd = new SqlCommand();
                            cmd.Connection = conn;
                            cmd.CommandText = query;
                            cmd.CommandType = CommandType.Text;
                            cmd.ExecuteNonQuery();
                        }
                        Console.WriteLine("******* Uploading Data Completed ***********************");
                    }

                    conn.Close();
                }
            }
            catch(Exception ex)
            {
                Console.WriteLine(" \n**************************** Error - " + ex.Message);
            }

            Console.ReadLine();
        }
    }
}

Build and Execute

Import CSV file into SQL using C#

2 Answers2

3. Open "Program.cs" Write Code