473,574 Members | 2,588 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Import CSV file into SQL using C#

PaullyB
4 New Member
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
11 26947
DrBunchman
979 Recognized Expert Contributor
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 MVP
Your sins lie on this line in your code
Expand|Select|Wrap|Line Numbers
  1. string[] sline = sr.ReadLine().Split(',');
Nov 18 '08 #3
PaullyB
4 New Member
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
PaullyB
4 New Member
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 MVP
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 Recognized Expert Contributor
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
PaullyB
4 New Member
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,564 Recognized Expert Moderator MVP
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 New Member
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

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

Similar topics

0
6903
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
2591
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 empty module object. That works okay, but as soon as the generated code tries do perform certain imports, it fails! Certain other imports succeed....
5
2466
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 kk in k:
0
2703
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 to obtain SHIP_wrap.cpp and SHIP.py; the latter contains the line "import _SHIP". I compile SHIP_wrap.cpp and a bunch of files into a DLL which I...
1
3053
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
2150
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 RDT). Each recordtype has a unique structure. ------------------------------------------------------------------------------- For example line one...
6
2352
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 problem also with other imports (e.g. urllib2 etc.). And again very often in all these cases where I get weired Python exceptions, the problem is around...
7
20547
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 _Ron
49
3903
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 isomorphism between filesystem and namespace, doesn't seem very well suited for big projects. However, I might not really understand the Pythonic way....
9
5768
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
8075
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8253
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7827
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8109
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6471
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5632
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5316
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
2253
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
1072
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.