469,306 Members | 1,712 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,306 developers. It's quick & easy.

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
  1. protected void cmdUploadFile_Click(object sender, EventArgs e)
  2.         {
  3.             conStr = "workstation id=" + ServerName + ";packet size=4096;user id=sa;password=" + Pass + ";data source=" + ServerName + ";persist security info=False;initial catalog=";
  4.             conStr = conStr + DB;
  5.  
  6.             string filepath = "D:\\Work\\Vukani\\CEMSSample10.csv";
  7.             StreamReader sr = new StreamReader(filepath);
  8.             int NrLines = 0;
  9.             string[,] mline;
  10.             mline = new string[NrLines, 50];
  11.             int cntra = 0;
  12.             int counter = 0;
  13.  
  14.             using (StreamReader cr = new StreamReader(filepath))
  15.             {
  16.                 while ((cr.ReadLine()) != null)
  17.                 {
  18.                     NrLines++;
  19.                 }
  20.                 cr.Close();
  21.             }
  22.  
  23.             mline = new string[NrLines, 25];
  24.  
  25.             for (int lcounter = 1; (lcounter <= NrLines); lcounter++)
  26.             {
  27.  
  28.                 string[] sline = sr.ReadLine().Split(',');
  29.                 //strElem = strElem.Append("");
  30.                 if (sline != null)
  31.                 {
  32.                     for (int c = 0; c < sline.Length; c++)
  33.                         mline[cntra, c] = sline[c];
  34.                     cntra++;
  35.                 }
  36.             }
  37.             sr.Close();            
  38.  
  39.             for (counter = 1; counter < NrLines; counter++)
  40.             {
  41.                 string Date = mline[counter, 0].ToString();
  42.                 string SiteUD = mline[counter, 1].ToString();
  43.                 string SiteName = mline[counter, 2].ToString();
  44.                 string ModelNo = mline[counter, 3].ToString();
  45.                 string MachID = mline[counter, 4].ToString();
  46.                 string Manufacture = mline[counter, 5].ToString();
  47.                 string TotalCashIn = mline[counter, 6].ToString();
  48.                 string TotalCashOut = mline[counter, 7].ToString();
  49.                 string NotesIN = mline[counter, 8].ToString();
  50.                 string CoinsIn = mline[counter, 9].ToString();
  51.                 string CoinsOut = mline[counter, 10].ToString();
  52.                 string CoinstoDrop = mline[counter, 11].ToString();
  53.                 string RemoteCashIn = mline[counter, 12].ToString();
  54.                 string RemoteCashOut = mline[counter, 13].ToString();
  55.                 string TotalWin = mline[counter, 14].ToString();
  56.                 string TotalBet = mline[counter, 15].ToString();
  57.                 string GGR = mline[counter, 16].ToString();
  58.                 string GamesPlayed = mline[counter, 17].ToString();
  59.                 string HandPays = mline[counter, 18].ToString();
  60.                 string HopperRefill = mline[counter, 19].ToString();
  61.  
  62.                      SQL = "INSERT INTO ztrewVNLCemsImport " +
  63.                               "([Date],            [SiteUD],        [SiteName],        [ModelNo.],            [MachID], " +
  64.                               "[Manufacture],   [TotalCashIn],  [TotalCashOut],    [NotesIN],            [CoinsIn], " +
  65.                               "[CoinsOut],        [CoinstoDrop],    [RemoteCashIn],    [RemoteCashOut],    [TotalWin], " +
  66.                               "[TotalBet],        [GGR],            [GamesPlayed],    [HandPays],            [HopperRefill] ) " +
  67.                           "VALUES " +
  68.                               "('" + Date + "',         '" + SiteUD + "',        '" + SiteName + "',     '" + ModelNo + "',            '" + MachID + "', " +
  69.                               "'" + Manufacture + "',    '" + TotalCashIn + "',  '" + TotalCashOut + "',    '" + NotesIN + "',          '" + CoinsIn + "', " +
  70.                               "'" + CoinsOut + "',        '" + CoinstoDrop + "',    '" + RemoteCashIn + "',    '" + RemoteCashOut + "',    '" + TotalWin + "', " +
  71.                               "'" + TotalBet + "',        '" + GGR + "',            '" + GamesPlayed + "',    '" + HandPays + "',            '" + HopperRefill + "') ";
  72.                 SQL = SQL.Replace('\t', ' ');
  73.  
Nov 18 '08 #1

✓ answered 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
  1. while (!sr.EndOfStream){
  2.   MatchCollection matches = Regex.Matches(sr.ReadLine(), "(?:^|,)(\"(?:[^\"]+|\"\")*\"|[^,]*)");
  3.   /*  Do whatever you need to do with the currentline referencing the
  4.       matches MatchCollection.  */
  5.   foreach(Match match in matches){
  6.     string sItem = match.Group[0].Value;
  7.   }
  8. }
Don't forget
Expand|Select|Wrap|Line Numbers
  1. using System.Text.RegularExpressions;
It's far simpler and more elegant than most of the dirty hacks I've come across for this...

9 17260
DrBunchman
979 Expert 512MB
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
Nov 18 '08 #2
r035198x
13,262 8TB
Your sins lie on this line in your code
Expand|Select|Wrap|Line Numbers
  1. string[] sline = sr.ReadLine().Split(',');
Nov 18 '08 #3
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?
Nov 18 '08 #4
Your sins lie on this line in your code
Expand|Select|Wrap|Line Numbers
  1. string[] sline = sr.ReadLine().Split(',');
Thanks, But I'm not sure what you mean by this.
Nov 18 '08 #5
r035198x
13,262 8TB
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.
Nov 18 '08 #6
balabaster
797 Expert 512MB
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
  1. while (!sr.EndOfStream){
  2.   MatchCollection matches = Regex.Matches(sr.ReadLine(), "(?:^|,)(\"(?:[^\"]+|\"\")*\"|[^,]*)");
  3.   /*  Do whatever you need to do with the currentline referencing the
  4.       matches MatchCollection.  */
  5.   foreach(Match match in matches){
  6.     string sItem = match.Group[0].Value;
  7.   }
  8. }
Don't forget
Expand|Select|Wrap|Line Numbers
  1. using System.Text.RegularExpressions;
It's far simpler and more elegant than most of the dirty hacks I've come across for this...
Nov 18 '08 #7
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
  1. while (!sr.EndOfStream){
  2.   MatchCollection matches = Regex.Matches(sr.ReadLine(), "(?:^|,)(\"(?:[^\"]+|\"\")*\"|[^,]*)");
  3.   /*  Do whatever you need to do with the currentline referencing the
  4.       matches MatchCollection.  */
  5.   foreach(Match match in matches){
  6.     string sItem = match.Group[0].Value;
  7.   }
  8. }
Don't forget
Expand|Select|Wrap|Line Numbers
  1. 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
Nov 19 '08 #8
NeoPa
32,173 Expert Mod 16PB
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.
Feb 3 '10 #9
kombsh
3
From http://www.morgantechspace.com/2013/...file-in-c.html

Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Data;
  3. using Microsoft.VisualBasic.FileIO;
  4.  
  5.  namespace ReadDataFromCSVFile
  6.   {
  7.  
  8.     static class Program
  9.       {
  10.         static void Main()
  11.         {
  12.             string csv_file_path=@"C:\Users\Administrator\Desktop\test.csv";
  13.  
  14.             DataTable csvData = GetDataTabletFromCSVFile(csv_file_path);
  15.  
  16.             Console.WriteLine("Rows count:" + csvData.Rows.Count);
  17.  
  18.             Console.ReadLine();
  19.         }
  20.  
  21.  
  22.        private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
  23.         {
  24.             DataTable csvData = new DataTable();
  25.  
  26.             try
  27.             {
  28.  
  29.             using(TextFieldParser csvReader = new TextFieldParser(csv_file_path))
  30.                 {
  31.                     csvReader.SetDelimiters(new string[] { "," });
  32.                     csvReader.HasFieldsEnclosedInQuotes = true;
  33.                     string[] colFields = csvReader.ReadFields();
  34.                     foreach (string column in colFields)
  35.                     {
  36.                         DataColumn datecolumn = new DataColumn(column);
  37.                         datecolumn.AllowDBNull = true;
  38.                         csvData.Columns.Add(datecolumn);
  39.                     }
  40.  
  41.                     while (!csvReader.EndOfData)
  42.                     {
  43.                         string[] fieldData = csvReader.ReadFields();
  44.                         //Making empty value as null
  45.                         for (int i = 0; i < fieldData.Length; i++)
  46.                         {
  47.                             if (fieldData[i] == "")
  48.                             {
  49.                                 fieldData[i] = null;
  50.                             }
  51.                         }
  52.                         csvData.Rows.Add(fieldData);
  53.                     }
  54.                 }
  55.             }
  56.             catch (Exception ex)
  57.             {
  58.             }
  59.             return csvData;
  60.         }
  61.       }
  62.     }
Sep 5 '13 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Stian Søiland | last post: by
5 posts views Thread by Steve Holden | last post: by
1 post views Thread by mirandacascade | last post: by
1 post views Thread by NathanB | last post: by
7 posts views Thread by Ron Adam | last post: by
49 posts views Thread by Martin Unsal | last post: by
9 posts views Thread by a | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.