StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PORows messed up when converting pipe delimited text files to .CSV excel file
text
Body
copied!<p>I code this function which converts all pipes into commas, and after that converting it into an excel .CSV file. </p> <p>However, after that I realize that there are some problems with some rows.</p> <p>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.</p> <p>I have look through the text file and find no pipes in between the name, and I couldn't find a solution to it.</p> <p>Below is my code for the function:</p> <pre><code>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>"); </code></pre> <p>A sample output of the .csv file (rows with errors are highlighted):</p> <p><img src="https://i.stack.imgur.com/OsdvW.png" alt="enter image description here"></p> <p>I have realized my mistake, it is due to having commans inside variables which caused the name to split. </p> <p>Are there any suggestions so that I could still convert them to .csv files despite having commans in between variables?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload