数据表到管道分隔的CSV [英] DataTable to Pipe Delimited CSV

问题描述

大家好.我有一个使用管道分隔查询的程序，将结果填充到数据表中，然后将数据表传递给生成CSV文件的类.我的问题是产生一个适当的竖线分隔"的CSV文件，Excel会识别并给我相应的选择来分隔我的文件.

因此，我的管道分隔查询的摘要看起来如下:

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)

如您所见，它不是用逗号分隔，而是用竖线(+''|''+)分隔.

我的dataTable传递给生成CSV文件的代码是这样的:

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();

            }

顺便说一句，此代码不允许我打开CSV文件并将其指定为管道分隔文件.我上面的代码可以更改以允许用管道定界的输出是什么?

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?

推荐答案

您确实知道csv扩展名意味着文件用逗号定界. ， 对?

Excel不会自动用逗号分隔csv文件.如果要将由管道分隔的文件导入Excel，则必须使用导入外部数据向导".这样可以将任何字符指定为分隔符.

或更改您的代码以生成实际的csv文件.

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.

我只是快速浏览了一下您的代码，但不应该

I have only had a quick look at your code but shouldn''t

sw.Write(",");

be

be

sw.Write("|");

这篇关于数据表到管道分隔的CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！