how to delete first field and all values of this field in .CSV file using C#

Question

I want to load .csv file and then import to SQl database. But before I do that, I want to do something to delete first field (in header) and also all values of this field.

this below is the example of my .csv file :

> TableId|PERIODE|DATEBALANCEASOF|ACCCODE|CUSTNAME|CUSTGROUP|
> TB_001|201501|2015-01-01|11-0001|DYNAMIC EXPRESS|11|
> TB_001|201501|2015-01-01|11-0002|DYNAMIC EXPRESS|12|
> TB_001|201501|2015-01-01|11-0003|DYNAMIC EXPRESS|13|
> TB_001|201501|2015-01-01|11-0004|DYNAMIC EXPRESS|14|

before I import that .csv file, I need my .csv file willbe like this below :

PERIODE|DATEBALANCEASOF|ACCCODE|CUSTNAME|CUSTGROUP|
201501|2015-01-01|11-0001|DYNAMIC EXPRESS|11|
201501|2015-01-01|11-0002|DYNAMIC EXPRESS|12|
201501|2015-01-01|11-0003|DYNAMIC EXPRESS|13|
201501|2015-01-01|11-0004|DYNAMIC EXPRESS|14|

this below is my code :

    public void readCSVManual(string pathLocalSuccess, string pathHistory, string modul)
    {
        try
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = @"server=" + serverDB + "; database=" + DB + "; Trusted_Connection=" + trustedConnection + "; user=" + UserDB + "; password=" + PassDB + "";
            string[] files = Directory.GetFiles(pathLocalSuccess);

            if (files == null)
            {
                MessageBox.Show("Files not found");
            }

            foreach (string file in files)
            {
                FileInfo fileInf = new FileInfo(file);
                string filename = fileInf.Name;
                StreamReader reader = new StreamReader(file);


                string line = reader.ReadLine();
                string[] value = line.Split('|');

                var list = new List<string>(value);
                list.RemoveAt(0);
                value = list.ToArray();

                //string[] v = string(nValue.ToArray());



                DataTable dt = new DataTable();
                DataRow row;
                foreach (string dc in value)
                {
                    dt.Columns.Add(new DataColumn(dc));
                }

                while (!reader.EndOfStream)
                {
                    value = reader.ReadLine().Split('|');
                    if (value.Length == dt.Columns.Count)
                    {
                        row = dt.NewRow();
                        row.ItemArray = value;
                        dt.Rows.Add(row);
                    }
                }

                SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);

                bc.DestinationTableName = "ACC_004";


                bc.BatchSize = dt.Rows.Count;
                bc.WriteToServer(dt);
                bc.Close();
                con.Close();
                reader.Close();
            }
        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

Please help me to resolve this problem..

I already resolve this problem, my problem is after the process to get all data from .csv and I put all data in datatable (from code above it's in while processing). after I got all data in datatable, I want to delete first colum and all it's data. I just have to add code : dt.Columns.RemoveAt(0); after while process. — HariEko, Jun 19 '15 at 07:43
You know it's not a CSV file if it isn't separated by commas? — Enigmativity, Jun 19 '15 at 07:45
Please don't do `catch (Exception ex)` - it's a terrible coding practice. You should only ever catch specific exceptions that you can't code around. — Enigmativity, Jun 19 '15 at 07:47

score 1 · Accepted Answer · answered Jun 19 '15 at 07:38

Thanks for everyone who respons my question, Actually I already solve this problem using a little editing in my code this below is my code and works for me :

public void readCSVAutomatic(string pathLocalSuccess, string pathHistory, string pathLogFolderSuccess, string pathErrorLog, string modul)
    {
        try
        {
            string[] files = Directory.GetFiles(pathLocalSuccess);

            foreach (string file in files)
            {
                FileInfo fileInf = new FileInfo(file);
                string filename = fileInf.Name;
                StreamReader reader = new StreamReader(file);

                string line = reader.ReadLine();
                string[] value = line.Split('|');

                DataTable dt = new DataTable();
                DataRow row;
                foreach (string dc in value)
                {
                    dt.Columns.Add(new DataColumn(dc));
                }

                while (!reader.EndOfStream)
                {
                    //value = value.
                    value = reader.ReadLine().Split('|');
                    if (value.Length == dt.Columns.Count)
                    {
                        row = dt.NewRow();
                        row.ItemArray = value;
                        dt.Rows.Add(row);
                    }
                }

                string xTableName = dt.Rows[0].ItemArray[0].ToString();
                string xPeriode = dt.Rows[0].ItemArray[1].ToString();
                dt.Columns.RemoveAt(0);

                SqlConnection con = new SqlConnection();
                con.ConnectionString = @"Data Source=" + serverDB + "; Initial Catalog=" + DB + "; Trusted_Connection=" + trustedConnection + "; user=" + UserDB + "; password=" + PassDB + "";

                con.Open();

                SqlCommand com = con.CreateCommand();
                string strDelete = "DELETE FROM " + xTableName + " WHERE PERIODE='" + xPeriode + "'";
                com.CommandText = strDelete;
                com.Connection = con;
                com.ExecuteNonQuery();

                SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);

                bc.DestinationTableName = xTableName;

                bc.BatchSize = dt.Rows.Count;
                bc.WriteToServer(dt);
                bc.Close();
                con.Close();
                reader.Close();

                moveFileAfterImported(pathLocalSuccess, filename, pathHistory);

                createLogCSVSuccessImported(pathLogFolderSuccess, "File Imported","Message");
            }
        }

        catch(Exception ex)
        {
            createErrorLogImportCSV(pathErrorLog, "ErrorImportCSV", ex.ToString());
        }
    }

Please don't do this: `"DELETE FROM " + xTableName + " WHERE PERIODE='" + xPeriode + "'";` Get in the habit of always [using parameters](http://blog.codinghorror.com/give-me-parameterized-sql-or-give-me-death/). Also: encapsulate `SqlCommand` etc. in a [using statement](http://stackoverflow.com/questions/23185990/sqlcommand-with-using-statement). — BCdotWEB, Jun 19 '15 at 07:52

score -1 · Answer 2 · answered Jun 19 '15 at 08:06

This seems to do the job for me:

First start by reading in all the files:

    var datatables =
        Directory
            .GetFiles(pathLocalSuccess)
            .Select(file => File.ReadAllLines(file).Select(x => x.Split('|')).ToArray())
            .Select(lines => new
            {
                headers = lines[0].Skip(1).ToArray(),
                lines = lines.Skip(1).Select(l => l.Skip(1).ToArray()).ToArray(),
            })
            .Select(x =>
            {
                var dt = new DataTable();
                foreach (var dc in x.headers)
                {
                    dt.Columns.Add(new DataColumn(dc));
                }
                foreach (var line in x.lines.Skip(1).Where(y => y.Length == x.headers.Length))
                {
                    var row = dt.NewRow();
                    row.ItemArray = line;
                    dt.Rows.Add(row);
                }
                return dt;
            })
            .ToArray();

Then it's a simple matter of loading all the data. This code only opens the connection once. It should be quite fast.

   if (!datatables.Any())
   {
       MessageBox.Show("Files not found");
   }
   else
   {
        using (var con = new SqlConnection())
        {
            con.ConnectionString = @"server=" + serverDB + "; database=" + DB + "; Trusted_Connection=" + trustedConnection + "; user=" + UserDB + "; password=" + PassDB + "";
            foreach (var dt in datatables)
            {
                var bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
                bc.DestinationTableName = "ACC_004";
                bc.BatchSize = dt.Rows.Count;
                bc.WriteToServer(dt);
                bc.Close();
            }
            con.Close();
        }
    }

The important part for skipping the first field is found in this code:

            .Select(lines => new
            {
                headers = lines[0].Skip(1).ToArray(),
                lines = lines.Skip(1).Select(l => l.Skip(1).ToArray()).ToArray(),
            })

@HariEko - No trouble. Don't forget to up-vote helpful answers and tick one as the selected answer. — Enigmativity, Jun 22 '15 at 10:28

how to delete first field and all values of this field in .CSV file using C#

2 Answers2