Solved

Read csv file into datatable when it contains commas.

Posted on 2016-09-13
10
65 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 

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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

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…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

624 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