Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 120
  • Last Modified:

Read csv file into datatable when it contains commas.

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
AlHal2
Asked:
AlHal2
  • 6
  • 4
1 Solution
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
AlHal2Author Commented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
AlHal2Author Commented:
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
 
AlHal2Author Commented:
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
 
Olaf DoschkeSoftware DeveloperCommented:
That's pipe separated data. You'll only habe Probleme with a oipe within a value, in this case.

Bye, Olaf.
0
 
AlHal2Author Commented:
The database name do not contain double quotes which may help.
0
 
Olaf DoschkeSoftware DeveloperCommented:
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
 
AlHal2Author Commented:
Thanks.
0
 
AlHal2Author Commented:
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.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now