Solved

Read csv file into datatable when it contains commas.

Posted on 2016-09-13
10
37 Views
Last Modified: 2016-09-13
Please can I have some C# code to read a csv file into a datatable when the csv file contains commas in the columns.  The csv file contains company names which have commas in them.
I'm using a web form in .Net Framwork 3.5
0
Comment
Question by:AlHal2
  • 6
  • 4
10 Comments
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41795790
Looking into the CSV`: Are the names, which include commas, delimited in quotes/double quotes? If not there is no way to distinguish which commas belong to data and which are the separators of columns and the CSV then is not within the specs of CSV.

Bye, Olaf.
0
 

Author Comment

by:AlHal2
ID: 41795809
In VB6, I could do something like this which worked.  It ingested the file cell by cell rather than line by line.  Can this be done in C#?

dim a as string, b as string
open "C:\temp\test.csv" for input as #1
do until eof(1)
             input #1, a,b
loop
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41795820
I'm sure also VB can only distinguish what makes up a single field, when there are quotation delimiters around names including a comma. There is no future telling going on in any language. I can't make it any clearer. If you have 10 columns you'd expect 9 commas between them. If there are 10 only looking at the data with the knowledge of context (this comma is part of a company name) no programming language can know to keep one comma and take another as separator.

The moment you have more commas, a VB command only populating two variables still does so and skip the rest, that works error free but with data loss of the end of line and the value with the included comma then is split.

So please, why don't you simply take the 1 minute needed to give me the view into the CSV, I'll not advice further, what to do in case one and two, and write a lengthy essay for each case, when half of it isn't needed. You also don't want to read all this. So simply be our eyes, if you want your problem solved by us.

Bye, Olaf.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

Author Comment

by:AlHal2
ID: 41795864
I'm generating the file myself.  At the moment I'm using this code to do so.  Attached is a small sample file.

using (StreamWriter sw = new StreamWriter(DownloadDir + "GovCorp\\Downloads\\" + UserFile + ".txt"))
                                        {
                                            foreach (DataColumn dc in ds.Tables["InputParameters"].Columns)
                                            {
                                                sw.Write(dc.ColumnName + "|");
                                            }
                                            sw.WriteLine("");

                                            foreach (DataRow dr in ds.Tables["InputParameters"].Rows)
                                            {
                                                ColCt = 0;
                                                foreach (DataColumn dc in ds.Tables["InputParameters"].Columns)
                                                {
                                                    sw.Write(dr[ColCt].ToString() + "|");

                                                    ColCt += 1;
                                                }
                                                sw.WriteLine("");
                                            }
                                        }

Open in new window

C--temp-alhal2.txt
0
 

Author Comment

by:AlHal2
ID: 41795872
This file will be sent to users to modify.  Once they've modified the file they'll push a button on the GUI to send it back to the server.  The GUI will call a stored procedure to update the database accordingly.

Basically they get an extract from the database, make the changes they want then send the file back.

I don't want to keep it as a text file as the names are quite wide and it would be difficult to keep track of what is going on.
I tried xls, but the server doesn't have Excel installed.  Now I want to try csv unless you have something better in mind.
0
 
LVL 29

Expert Comment

by:Olaf Doschke
ID: 41795875
That's pipe separated data. You'll only habe Probleme with a oipe within a value, in this case.

Bye, Olaf.
0
 

Author Comment

by:AlHal2
ID: 41795882
The database name do not contain double quotes which may help.
0
 
LVL 29

Accepted Solution

by:
Olaf Doschke earned 500 total points
ID: 41795935
For reading this in, use for example https://www.stellman-greene.com/CSVReader/

There is something native in the VisualBasic namespace, which you may use in C#, too: https://www.dotnetperls.com/textfieldparser

Bye, Olaf.
0
 

Author Closing Comment

by:AlHal2
ID: 41796059
Thanks.
0
 

Author Comment

by:AlHal2
ID: 41796062
I'm going to give the user a | delimited txt file then tell them to put it into an xlsx file.  They will make their changes and run a local executable to convert the xlsx file into another | delimited file which can be sent to the server.
The server doesn't have Excel installed.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question