472,356 Members | 2,004 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,356 software developers and data experts.

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...

11 25885
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,511 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
Willjoe
12 Byte
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
Jul 26 '22 #11
NeoPa
32,511 Expert Mod 16PB
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).
Jul 26 '22 #12

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

Similar topics

0
by: Stian Søiland | last post by:
all examples performed with: Python 2.3+ (#2, Aug 10 2003, 11:09:33) on linux2 (2, 3, 0, 'final', 1) This is a recursive import:
4
by: Irmen de Jong | last post by:
Hello, I don't understand why the following doesn't work. What I want to do is dynamically import some generated Python code and I'm doing this using compile and exec'ing it in the dict of a new...
5
by: Steve Holden | last post by:
This is even stranger: it makes it if I import the module a second time: import dbimp as dbimp import sys if __name__ == "__main__": dbimp.install() #k = sys.modules.keys() #k.sort() #for...
0
by: Bill Davy | last post by:
I am working with MSVC6 on Windows XP. I have created an MSVC project called SHIP I have a file SHIP.i with "%module SHIP" as the first line (file is below). I run SHIP.i through SWIG 1.3.24...
1
by: mirandacascade | last post by:
O/S: Windows 2K Vsn of Python: 2.4 Currently: 1) Folder structure: \workarea\ <- ElementTree files reside here \xml\ \dom\
1
by: NathanB | last post by:
Hi there, I have a text file (flat file) which I would like to import on a regular basis into Access. The text file contains 2 record types, header (prefixed with RHD) and detail (prefixed with...
6
by: robert | last post by:
I get python crashes and (in better cases) strange Python exceptions when (in most cases) importing and using cookielib lazy on demand in a thread. It is mainly with cookielib, but remember the...
7
by: Ron Adam | last post by:
from __future__ import absolute_import Is there a way to check if this is working? I get the same results with or without it. Python 2.5 (r25:51908, Sep 19 2006, 09:52:17) on win 32 ...
49
by: Martin Unsal | last post by:
I'm using Python for what is becoming a sizeable project and I'm already running into problems organizing code and importing packages. I feel like the Python package system, in particular the...
9
by: a | last post by:
Dear friends I want import data from CSV file to mdb file How can I do that in vb.net?
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
2
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it so the python app could use a http request to get...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.