Solved

Read csv file into datatable when it contains commas.

Posted on 2016-09-13
10
51 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
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 …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

710 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