locked
Skipping first row in a CSV file RRS feed

  • Question

  • User-541003552 posted

    hi All,

    I can i skip the first row in a csv file and insert into a database. Below is the code I am trying to use. Any help and advice.

    Thanks

                string csvPath = Server.MapPath("~/Files/") + Path.GetFileName(FileUpload1.PostedFile.FileName);
                FileUpload1.SaveAs(csvPath);
    
                DataTable dt = new DataTable();
                dt.Columns.AddRange(new DataColumn[6] { 
                new DataColumn("SERIAL", typeof(string)),
                new DataColumn("BANK", typeof(string)),
                new DataColumn("ACCOUNT",typeof(string)),
                new DataColumn("T/C", typeof(string)),
                new DataColumn("AMOUNT",typeof(string)),
                new DataColumn("DepositorAcc",typeof(string)) });
    
          
                string csvData = File.ReadAllText(csvPath);
           //     for (int i = 0; i < dt.Rows.Count; i++)
            //        {
                    foreach (string row in csvData.Split('\n'))
                        {
    
    
                        if (!string.IsNullOrEmpty(row))
                            {
                            dt.Rows.Add();
                            int i = 0;
    
                            foreach (string cell in row.Split(','))
                                {
                                dt.Rows[dt.Rows.Count - 1][i] = cell;
                                i++;
    
                                }
                            }
    
                        }
             //      }
                string consString = ConfigurationManager.ConnectionStrings["SCBFileConnectionString1"].ConnectionString;
                using (SqlConnection con = new SqlConnection(consString))
                    {
                    using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                        {
                        //Set the database table name
                        sqlBulkCopy.DestinationTableName = "dbo.Inward";
                        con.Open();
                        sqlBulkCopy.WriteToServer(dt);
                        con.Close();
                        }
                    }

    Wednesday, December 2, 2015 4:06 PM

Answers

  • User-1716253493 posted
            bool skip = true;
            foreach (string row in csvData.Split('\n'))
            {
                if (!string.IsNullOrEmpty(row))
                {
                    if (skip == true)
                    {
                        skip = false;
                    }
                    else
                    {
                        dt.Rows.Add();
                        int i = 0;
                        foreach (string cell in row.Split(','))
                        {
                            dt.Rows[dt.Rows.Count - 1][i] = cell;
                            i++;
                        }
                    }
                }
            }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 3, 2015 7:34 AM

All replies

  • User-434868552 posted

    @KeeEdwins

    something as simple as this works as long as your first row actually is the header row:

    string csvData = File.ReadAllText(csvPath);
    Boolean headerRowHasBeenSkipped = false;
    foreach (string row in csvData.Split('\n'))
    {
        if (headerRowHasBeenSkipped)
        {
            if (!string.IsNullOrEmpty(row))
            {
                dt.Rows.Add();
                int i = 0;
                    foreach (string cell in row.Split(','))
                    {
                        dt.Rows[dt.Rows.Count - 1][i] = cell;
                        i++;
                    }
            }
        } // outer if
        headerRowHasBeenSkipped = true;
    } // foreach

    Wednesday, December 2, 2015 4:27 PM
  • User1428246847 posted

    You can consider the use of File.ReadAllLines instead of File.ReadAllText

    string[] records = File.ReadAllLines(yourpath);
    for (int reccnt = 1; reccnt < records.Length; reccnt++)
    {
        // process records here
    }
    

    It might also be advisable not to re-invent the wheel; if your data contains a comma or a newline in a field (both are valid), your approach will fail. If this is only about reading, you can consider the use of the textfield parser class (https://msdn.microsoft.com/en-us/library/microsoft.visualbasic.fileio.textfieldparser%28v=vs.110%29.aspx); you need to add a reference to Microsoft.VisualBasic to your project.

    Else there is a multitude of examples on the web, but not all of them handle embedded linefeeds correctly! I use a modified version of Andreas Knab's code (no longer to be found on the web).

    Thursday, December 3, 2015 4:40 AM
  • User614698185 posted

    Hi KeeEdwins,

    The TextFieldParser from Microsoft.VisualBasic.dll could help in this requirement:

    using (TextFieldParser MyReader = new TextFieldParser(csvPath))
    {
         MyReader.TextFieldType = FieldType.Delimited;
         MyReader.SetDelimiters(",");
         MyReader.HasFieldsEnclosedInQuotes = true;
         string[] currentRow;
         currentRow = MyReader.ReadFields();
         while(!MyReader.EndOfData)
         {
            DataRow row = dt.NewRow();
            currentRow = MyReader.ReadFields();
            for(int i = 0; i < currentRow.Length; i++)
            {
                row[i] = currentRow[i];
            }
            dt.Rows.Add(row); 
         }
     }

    Best Regards,

    Candice Zhou

    Thursday, December 3, 2015 5:45 AM
  • User-1716253493 posted
            bool skip = true;
            foreach (string row in csvData.Split('\n'))
            {
                if (!string.IsNullOrEmpty(row))
                {
                    if (skip == true)
                    {
                        skip = false;
                    }
                    else
                    {
                        dt.Rows.Add();
                        int i = 0;
                        foreach (string cell in row.Split(','))
                        {
                            dt.Rows[dt.Rows.Count - 1][i] = cell;
                            i++;
                        }
                    }
                }
            }
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 3, 2015 7:34 AM