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:

Expand|Select|Wrap|Line Numbers

 
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

Expand|Select|Wrap|Line Numbers

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

PaullyB

Quote:

Originally Posted by 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

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

Quote:

Originally Posted by r035198x

Your sins lie on this line in your code

Expand|Select|Wrap|Line Numbers

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

Expand|Select|Wrap|Line Numbers

 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

Expand|Select|Wrap|Line Numbers

using System.Text.RegularExpressions;

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

PaullyB

Quote:

Originally Posted by 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

Expand|Select|Wrap|Line Numbers

 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

Expand|Select|Wrap|Line Numbers