Import CSV file into SQL using C#

[PaullyB]

Hi There
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 embebbed comma are not being read correctly. How can I get around this.
Below is the code that I'm using:

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

            string filepath = "D:\\Work\\Vukani\\CEMSSample10.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', ' ');

[DrBunchman]

Hi PaullyB,

Welcome to Bytes.com! I hope you find the site useful.

You've posted your question in the ASP Forum which is for Classic ASP only - I've moved it for you but in future please post all ASP.NET questions in the .NET Forum.

If there are commas already embedded in the fields in your CSV file then there is nothing that you can do. They must be removed/replaced prior to the CSV file being created. For example, if i was creating a CSV file from a database query I might replace all instances of a comma with a space when extracting the data.

Is this possible for you?

Dr B

[r035198x]

Your sins lie on this line in your code

string[] sline = sr.ReadLine().Split(',');

[PaullyB]

Hi PaullyB,

Welcome to Bytes.com! I hope you find the site useful.

You've posted your question in the ASP Forum which is for Classic ASP only - I've moved it for you but in future please post all ASP.NET questions in the .NET Forum.

If there are commas already embedded in the fields in your CSV file then there is nothing that you can do. They must be removed/replaced prior to the CSV file being created. For example, if i was creating a CSV file from a database query I might replace all instances of a comma with a space when extracting the data.

Is this possible for you?

Dr B

Thanks for your reply

The CSV file comes from a 3rd party source and at this stage it is not possible to remove the embedded commas. the fields with embedded commas are qualified by " " (double quotes) however the fields are being separated at the commas.
is there any code i can add to get the streamreader to recognise the qualified fields?

[PaullyB]

Your sins lie on this line in your code

string[] sline = sr.ReadLine().Split(',');

Thanks, But I'm not sure what you mean by this.

[r035198x]

There are several approaches to getting around that. Most of them are dirty hacks which require replacing the comma inside the quotes with something else and then putting the comma back after using the Split method of them. But before we get into those, why not load the data directly from the csv file by executing a LOAD DATA INFILE command (or similar depending on your RDBM)?

P.S There are also some free csv reader classes around that may do the job for you.

[balabaster]

Try this idea - it uses regular expressions and will give you each item on the line (it will recognize embedded commas) in the matches collection and you can iterate over them just as you would have using your split array

while (!sr.EndOfStream){
  MatchCollection matches = Regex.Matches(sr.ReadLine(), "(?:^|,)(\"(?:[^\"]+|\"\")*\"|[^,]*)");
  /*  Do whatever you need to do with the currentline referencing the
      matches MatchCollection.  */
  foreach(Match match in matches){
    string sItem = match.Group[0].Value;
  }
}

Don't forget

using System.Text.RegularExpressions;

It's far simpler and more elegant than most of the dirty hacks I've come across for this...

[PaullyB]

Try this idea - it uses regular expressions and will give you each item on the line (it will recognize embedded commas) in the matches collection and you can iterate over them just as you would have using your split array

while (!sr.EndOfStream){
  MatchCollection matches = Regex.Matches(sr.ReadLine(), "(?:^|,)(\"(?:[^\"]+|\"\")*\"|[^,]*)");
  /*  Do whatever you need to do with the currentline referencing the
      matches MatchCollection.  */
  foreach(Match match in matches){
    string sItem = match.Group[0].Value;
  }
}

Don't forget

using System.Text.RegularExpressions;

It's far simpler and more elegant than most of the dirty hacks I've come across for this...

Thanks.
This has solved my problems

[NeoPa]

Suryakant,

Please check out your PMs for an infraction given already for posting inappropriate links. I notice from this last post that you have not stopped this yet. Further such behaviour will result in serious consequences.

The site rules are available from every page of the site, and ignorance (when you've already been warned and directed to read them) is not considered an excuse. Please be sure to remain within them in future.

-Administrator.

[kombsh]

From http://www.morgantechspace.com/2013/...file-in-c.html

using System;
using System.Data;
using Microsoft.VisualBasic.FileIO;

 namespace ReadDataFromCSVFile
  {

    static class Program
      {
        static void Main()
        {
            string csv_file_path=@"C:\Users\Administrator\Desktop\test.csv";

            DataTable csvData = GetDataTabletFromCSVFile(csv_file_path);

            Console.WriteLine("Rows count:" + csvData.Rows.Count);
            
            Console.ReadLine();
        }


       private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
        {
            DataTable csvData = new DataTable();

            try
            {
        
            using(TextFieldParser csvReader = new TextFieldParser(csv_file_path))
                {
                    csvReader.SetDelimiters(new string[] { "," });
                    csvReader.HasFieldsEnclosedInQuotes = true;
                    string[] colFields = csvReader.ReadFields();
                    foreach (string column in colFields)
                    {
                        DataColumn datecolumn = new DataColumn(column);
                        datecolumn.AllowDBNull = true;
                        csvData.Columns.Add(datecolumn);
                    }

                    while (!csvReader.EndOfData)
                    {
                        string[] fieldData = csvReader.ReadFields();
                        //Making empty value as null
                        for (int i = 0; i < fieldData.Length; i++)
                        {
                            if (fieldData[i] == "")
                            {
                                fieldData[i] = null;
                            }
                        }
                        csvData.Rows.Add(fieldData);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return csvData;
        }
      }
    }

[Willjoe]

How do I import a CSV file into SQL?
Using SQL Server Management Studio Import CSV Tools
From the Object Explorer, expand the Databases folder.
Select the Target Database.
Select a Flat File Source.
Specify the CSV File.
Configure the Columns.
Choose the Destination (SQL Server)
Specify the Database Table and Check Column Mappings.

Regards,
Willjoe

[NeoPa]

Hi WillJoe.

Please read the question before replying. Your answer doesn't match the question posted as even the title specifies clearly that it needs to be done using the c# language.

Furthermore, this thread already has a perfectly valid answer that actually works.

-Ade (Admin).