Click here to Skip to main content
14,668,357 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hey guys. I have a program which takes a pipe delimited query, fills a data table with the results and then the data table is passed to a class that produces a CSV file. My problem is in producing a CSV file that is appropriately "pipe-delimited", that Excel will recognize and give me the option to delimit my file accordingly.

A snippet of my pipe-delimited query looks thusly:
+ '|' + CAST(ISNULL(ls.LoanOfficerName, '') AS VARCHAR)
+ '|' + CAST(ISNULL(ls.BorrowerLastName, '') AS VARCHAR)

As you can see, it is delimited not by comma but by a pipe (+ '|' +).

The code that my dataTable is being passed into to geneate the CSV File is this:
public void WriteCSVFile(DataTable dataTable, string filePath)
            {
                StreamWriter sw = new StreamWriter(filePath, false);

                int iColCount = dataTable.Columns.Count;

                for (int i = 0; i < iColCount; i++)
                {
                    sw.Write(dataTable.Columns[i]);

                    if (i < iColCount - 1)
                    {
                        sw.Write(",");
                    }
                }
                sw.Write(sw.NewLine);

                

                foreach (DataRow row in dataTable.Rows)
                {
                    for (int i = 0; i < iColCount; i++)
                    {
                        if (!Convert.IsDBNull(row[i]))
                        {
                            sw.Write(row[i].ToString());
                        }
                        if (i < iColCount - 1)
                        {
                            sw.Write(",");
                        }
                    }
                    sw.Write(sw.NewLine);
                }
                sw.Close();

            }

Incidentally, this code does not allow me to open the CSV file and designate it as a pipe delimited file. What is it about my above code that I can change to permit a pipe delimited output?
Posted
Comments
Dr.Walt Fair, PE 10-Dec-10 19:25pm
   
Is there some reason you can't just change the ","s to "|"s in your code?

Rate this:
Please Sign up or sign in to vote.

Solution 1

You do understand that the csv extension means that the file is delimited by commas, right?

Excel won't automatically delimit a csv file by anything by a comma. If you want to import a file delimited by pipes into Excel, you'll have to use the Import External Data Wizard. You can specify any character as the delimiter when you do it that way.

Or change your code to produce an actual csv file.
   
Comments
Isaiah83 10-Dec-10 17:35pm
   
The file absolutely has to be in a pipe-delimited format, so changing it to a comma (if that's what you meant), isn't possible for the requirements of this file.
Rate this:
Please Sign up or sign in to vote.

Solution 2

I have only had a quick look at your code but shouldn't
sw.Write(",");


be
sw.Write("|");
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100