string.Split() not working for the CSV file read

Question

I am reading an excel file using my controller. I am storing all the lines of CSV file in an array. When I print it out, I can see the contents of array. But when I iterate through each of the lines and split by comma, I get nothing and as a result I can't store read values.

Here is a sample of my output along with code:

VAWC Neptune flat file is my file. 206 are number of lines in file. Then I am printing line along with its length. And when this line is splitted by commas, I see only first output else everything is empty.

However as we keep on reading other lines, this splitted array doesn't appear.

Here is the section of code which I am using:

//files is only having a single file named VAWC Neptune flat file - new meters for inventory.csv
public ActionResult ReadFile(IEnumerable<HttpPostedFileBase> files)
{
        var fileName = Path.GetFileName(files.First().FileName);
        var destinationPath = Path.Combine(Server.MapPath("~/App_Data"), fileName);
        files.First().SaveAs(destinationPath);
try
{
     string[] read = System.IO.File.ReadAllLines(destinationPath);
     System.Diagnostics.Debug.WriteLine(read.Length);
     for (int i = 0; i < read.Length; i++)
     {
        System.Diagnostics.Debug.WriteLine(read[i]);
        List<string> s = read[i].Replace(Environment.NewLine,"").Split(new char[] { ',' }, StringSplitOptions.None).ToList<string>();
        System.Diagnostics.Debug.WriteLine("Length of words in line:" + s.Capacity);
        for (int j = 0; j < s.Capacity; j++)
        {
            System.Diagnostics.Debug.WriteLine("Data:s[" + j + "]" + s[j]);
        }
     }
}

I have tried so many possible ways but nothing has worked.

Please [stop writing your own CSV parser](http://www.secretgeek.net/csv_trouble); the format is more complicated than you realize. There are [plenty of working CSV parsers](https://stackoverflow.com/questions/2081418/) ready for you to drop in to your application. — Dour High Arch, Jun 04 '19 at 21:20
What is the reason that you don't use any of the existing CSV parsers? Add the example of your file to the question — Samvel Petrosov, Jun 04 '19 at 21:23
Your "read" array shouldn't have any line breaks to replace since the ReadAllLines already split on those. — LarsTech, Jun 04 '19 at 21:28
If `read[i]` represents a line read by `File.ReadAllLines`, then it won't contain an `Environment.NewLine`... — Rufus L, Jun 04 '19 at 21:28
@Rufus, yes that was my first approach. Thereafter I made changes and tried to use Environment.NewLine. When I opened CSV file in Notepad++, I saw CLRFs at end of every line. — Sahil Tiwari, Jun 04 '19 at 22:32
Yes, that is what indicates a new line, and that's literally how the file lines are split into an array (see the [source code](https://referencesource.microsoft.com/mscorlib/R/a4ada5f765646068.html)), but they are not returned as part of the strings returned by `File.ReadAllLines` — Rufus L, Jun 04 '19 at 22:33
Please share some sample file data that reproduces the issue. — Rufus L, Jun 04 '19 at 23:04

score 0 · Answer 1 · answered Jun 11 '19 at 01:32

From Excel to CSV, right. This is how I would do it.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            Excel.Application xlApp = new Excel.Application();
            Excel.Workbook xlWorkBook = xlApp.Workbooks.Open(@"C:\Users\Ryan\Desktop\Coding\DOT.NET\Samples C#\Excel Workbook - Save Each Sheet as a CSV File\Book1.xlsx");
            xlApp.Visible = true;
            foreach (Excel.Worksheet sht in xlWorkBook.Worksheets)
            {
                sht.Select();
                xlWorkBook.SaveAs(string.Format("{0}{1}.csv", @"C:\Users\Ryan\Desktop\Coding\DOT.NET\Samples C#\Excel Workbook - Save Each Sheet as a CSV File to CSV\", sht.Name), Excel.XlFileFormat.xlCSV, Excel.XlSaveAsAccessMode.xlNoChange);

            }
            xlWorkBook.Close(false);

        }

    }
}

string.Split() not working for the CSV file read

1 Answers1