Rows messed up when converting pipe delimited text files to .CSV excel file

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

            // 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('|', ',');
                    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):

enter image description here

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?

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

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

Solution 2

The input files are pipe delimited because the data includes commas. Changing the delimiters to commas will therefore cause the problem you’re seeing. Solution: keep the pipe delimiters. Why do you feel you need to replace them? I’m sure Excel can read pipe-delimited files.

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply