将管道分隔的文本文件转换为.CSV excel文件时，行被搞砸了 [英] Rows messed up when converting pipe delimited text files to .CSV excel file

问题描述

我编写这个函数，将所有管道转换成逗号，然后将其转换为excel .CSV文件。

然而，之后，我意识到某些行有一些问题。

例如。名字[蔡伟龙]（据说是一栏），最后一个蔡伟在一列，Loon在下一列。

我看过文本文件，找不到名字之间的管道，我找不到解决方案。

下面是我的函数代码：

  protected void SaveAsExcelBtn_Click（object sender，EventArgs e）
 {
 
 
 // string strExcelOutputFilename =C：/ Documents and Settings / rhlim / My Documents /+ DateTime.Now.ToString（yyyyMMddHHmmss）+ xlExtension; 
 
 //尝试导入文件之前，验证
 // FileUpload控件包含一个文件。 
 if（TextFile.HasFile）
 {
 //获取Excel电子表格的名称。 
 string strFileName = Server.HtmlEncode（TextFile.FileName）; 
 
 //获取文本的扩展名。 
 string strExtension = Path.GetExtension（strFileName）; 
 
 //验证文件扩展名。 
 if（strExtension！=.TXT&& strExtension！=。txt）
 {
 
 Response.Write（< script> alert无效的文本文件！'）;< / script>）; 
返回; 
} 
 
 //生成保存文本文件的文件名。 
 // string strUploadFileName =C：/ Documents and Settings / rhlim / My Documents / Visual Studio 2005 / WebSites / SoD / UploadFiles /+ DateTime.Now.ToString（yyyyMMddHHmmss）+ strExtension; 
 
 if（DEMUserRoleRB.Checked）
 {
 string strExcelOutputFilename =C：/+userrolelist+ xlExtension; 
 
 
使用（StreamWriter outputWriter = new StreamWriter（File.Create（strExcelOutputFilename）））
 {
 StreamReader inputReader = new StreamReader（TextFile.FileContent）; 
 string fileContent = inputReader.ReadToEnd（）; 
 fileContent = fileContent.Replace（'|'，'，'）; 
 outputWriter.Write（fileContent）; 
 //TextFile.SaveAs(strExcelOutputFilename）; 
 inputReader.Close（）; 
 UploadStatusLabel.Text =转换成功。文件存储在目录C：/; 
} 
 
 // string strExcelOutputFilename =C：/ Documents and Settings / rhlim / My Documents /+ DateTime.Now.ToString（yyyyMMddHHmmss）+ xlExtension; 
 //将Excel电子表格保存在服务器上。 
 //TextFile.SaveAs（strExcelOutputFilename）; 
 
} 
} 
 else Response.Write（< script> alert（'请选择一个文件'）;< / script>）; 
  

.csv文件的示例输出（突出显示错误的行）：

我已经意识到我的错误，这是因为在变量中有逗号引起名称分裂。

有没有任何建议，我仍然可以将它们转换为.csv文件，尽管在变量之间有逗号？

>>>>>>>>？？？？？？？？？？？？？？？？？？？？？？？你先删除数据中的逗号吗？

I code this function which converts all pipes into commas, and after that converting it into an excel .CSV file.

However, after that I realize that there are some problems with some rows.

E.g. Name [Chua Wei Loon] (supposedly to be in one column), ended up "Chua Wei" is in one column, "Loon" is in the next column.

I have look through the text file and find no pipes in between the name, and I couldn't find a solution to it.

Below is my code for the function:

protected void SaveAsExcelBtn_Click(object sender, EventArgs e)
    {


        //string strExcelOutputFilename = "C:/Documents and Settings/rhlim/My Documents/" + DateTime.Now.ToString("yyyyMMddHHmmss") + xlExtension;

        // Before attempting to import the file, verify 
        // that the FileUpload control contains a file. 
        if (TextFile.HasFile)
        {
            // Get the name of the Excel spreadsheet. 
            string strFileName = Server.HtmlEncode(TextFile.FileName);

            // Get the extension of the text. 
            string strExtension = Path.GetExtension(strFileName);

            // Validate the file extension. 
            if (strExtension != ".TXT" && strExtension!=".txt")
            {

                Response.Write("<script>alert('Invalid text file!');</script>");
                return;
            }

            // Generate the file name to save the text file.
            //string strUploadFileName = "C:/Documents and Settings/rhlim/My Documents/Visual Studio 2005/WebSites/SoD/UploadFiles/" + DateTime.Now.ToString("yyyyMMddHHmmss") + strExtension;

            if (DEMUserRoleRB.Checked)
            {
                string strExcelOutputFilename = "C:/" + "userrolelist" + xlExtension;


                using (StreamWriter outputWriter = new StreamWriter(File.Create(strExcelOutputFilename)))
                {
                    StreamReader inputReader = new StreamReader(TextFile.FileContent);
                    string fileContent = inputReader.ReadToEnd();
                    fileContent = fileContent.Replace('|', ',');
                    outputWriter.Write(fileContent);
                    //TextFile.SaveAs(strExcelOutputFilename);
                    inputReader.Close();
                    UploadStatusLabel.Text = "Conversion successful. File is stored at directory C:/";
                }

                //string strExcelOutputFilename = "C:/Documents and Settings/rhlim/My Documents/" + DateTime.Now.ToString("yyyyMMddHHmmss")+xlExtension;
                // Save the Excel spreadsheet on server. 
                //TextFile.SaveAs (strExcelOutputFilename);

            }
        }
        else Response.Write("<script>alert('Please select a file');</script>");

A sample output of the .csv file (rows with errors are highlighted):

I have realized my mistake, it is due to having commans inside variables which caused the name to split.

Are there any suggestions so that I could still convert them to .csv files despite having commans in between variables?

解决方案

did you check whether [Chua Wei Loon] is not actually [Chua Wei, Loon] ? Did you get rid of the commas in the data first?

这篇关于将管道分隔的文本文件转换为.CSV excel文件时，行被搞砸了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！