Solved

Read csv file into datatable when it contains commas.

Posted on 2016-09-13
10
35 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
What is "public object" in C#? 3 45
EF5: Question about Metadata Artifact Processing 4 15
VB.NET - Error - Object not set in Module 4 33
List<PaisEntity> - show some 1 24
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

776 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