none
[C#] I'm trying to import an excel file to SQL via visual Studio. How do I make it read the data starting from the second row? RRS feed

  • Question

  • I'm trying to import an excel file to SQL server via visual Studio. How do I make it read the data starting from the second row and not the first row of the excel file? My code works perfectly as it is, but I want it to start reading the data from the 2nd row. Here is my code:

        using System;
        using System.Collections.Generic;
        using System.ComponentModel;
        using System.Data;
        using System.Drawing;
        using System.Linq;
        using System.Text;
        using System.Threading.Tasks;
        using System.Windows.Forms;
        using System.IO;
        using ExcelDataReader;
        
        namespace ImportDB2
        {
            public partial class Form1 : Form
            {
        
        
                public Form1()
                {
                    InitializeComponent();
                }
        
                private void button1_Click(object sender, EventArgs e)
                {
                    OpenFileDialog ope = new OpenFileDialog();
                    ope.Filter = "Excel Files|*.xls; *.xlsx; *.xlsm";
                    if (ope.ShowDialog() == DialogResult.Cancel)
                        return;
        
                    FileStream stream = new FileStream(ope.FileName, FileMode.Open);
                    IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                    DataSet result = excelReader.AsDataSet();
        
                    
                    DataClasses1DataContext conn = new DataClasses1DataContext();
        
        
           
                    foreach (DataTable table in result.Tables)
                    {
                       
                        foreach (DataRow dr in table.Rows)
                        {
                           
        
                            Employee addtable = new Employee()
                            {
        
                                Serial = Convert.ToInt32(dr[0]),
                                Name = Convert.ToString(dr[1]),
                                Class = Convert.ToString(dr[2]),
                                Department = Convert.ToString(dr[3]),
                                Status = Convert.ToString(dr[4]),
                                Position = Convert.ToString(dr[5]),
                                Email = Convert.ToString(dr[6])
                            };
                            conn.Employees.InsertOnSubmit(addtable);
                        }
                    }
                    conn.SubmitChanges();
                    excelReader.Close();
                    stream.Close();
                    MessageBox.Show("YEEESSSS FINALLY");
                }
            }
        }

    What line of code should I put for it to start reading on the second row, and where inside my code should I put it in? Hope someone can help, thank you. ^_^
    Thursday, March 22, 2018 12:26 PM

All replies

  • Does the first row in the WorkSheet have information in it? If so when inserting data it should start at the next available row e.g. if there is data in row 1 then the insert should go into row 2. Otherwise if there is no data in row one that is where the insert happens.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, March 22, 2018 12:34 PM
    Moderator
  • Does the first row in the WorkSheet have information in it? If so when inserting data it should start at the next available row e.g. if there is data in row 1 then the insert should go into row 2. Otherwise if there is no data in row one that is where the insert happens.
    Hello, yes, it's like a header file which contains the titles of each column, which I don't want to show. How exactly am I going to implement the insert statement in visual studio? Sorry I'm still new to all of this... :(
    Thursday, March 22, 2018 12:39 PM
  • Specify HDR=Yes in the Extended Properties of the connection string and the first row of data will be the second row of the Excel Worksheet.

    Here is an example:

    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                    "Data Source=C:\\Users\\...\\Documents\\My Database\\Excel\\BookTest.xlsx;" +
                    "Extended Properties=" + (char)34 + "Excel 12.0 Xml;HDR=YES;" + (char)34;


    Paul ~~~~ Microsoft MVP (Visual Basic)


    Thursday, March 22, 2018 12:51 PM
  • Specify HDR=Yes in the Extended Properties of the connection string and the first row of data will be the second row of the Excel Worksheet.

    Hi Paul, not sure if I'm right but what I did was:

    ope.Filter = "Excel Files|*.xls; *.xlsx; *.xlsm HDR=Yes";

    But it got an error. Pretty sure I'm wrong. If it's not too much, where exactly in the code am I supposed to put the HDR=Yes part? Sorry, still pretty stupid with programming. >_<

    Thursday, March 22, 2018 12:57 PM
  • Specify HDR=Yes in the Extended Properties of the connection string and the first row of data will be the second row of the Excel Worksheet.

    Hi Paul, not sure if I'm right but what I did was:

    ope.Filter = "Excel Files|*.xls; *.xlsx; *.xlsm HDR=Yes";

    But it got an error. Pretty sure I'm wrong. If it's not too much, where exactly in the code am I supposed to put the HDR=Yes part? Sorry, still pretty stupid with programming. >_<


    Ah, I see you are using the ExcelDataReader library. If it's the one from CodePlex/Github then try adding the following line before the AsDataSet method call:

    excelReader.IsFirstRowAsColumnNames = true;

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, March 22, 2018 1:12 PM
  • Specify HDR=Yes in the Extended Properties of the connection string and the first row of data will be the second row of the Excel Worksheet.

    Hi Paul, not sure if I'm right but what I did was:

    ope.Filter = "Excel Files|*.xls; *.xlsx; *.xlsm HDR=Yes";

    But it got an error. Pretty sure I'm wrong. If it's not too much, where exactly in the code am I supposed to put the HDR=Yes part? Sorry, still pretty stupid with programming. >_<


    Ah, I see you are using the ExcelDataReader library. If it's the one from CodePlex/Github then try adding the following line before the AsDataSet method call:

    excelReader.IsFirstRowAsColumnNames = true;

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Hello, I tried your tip but it became a red squiggly line and it the error said: Severity Code Description Project File Line Suppression State
    Error CS1061 'IExcelDataReader' does not contain a definition for 'IsFirstRowAsColumnNames' and no extension method 'IsFirstRowAsColumnNames' accepting a first argument of type 'IExcelDataReader' could be found (are you missing a using directive or an assembly reference?)
    Thursday, March 22, 2018 2:12 PM
  • Specify HDR=Yes in the Extended Properties of the connection string and the first row of data will be the second row of the Excel Worksheet.

    Hi Paul, not sure if I'm right but what I did was:

    ope.Filter = "Excel Files|*.xls; *.xlsx; *.xlsm HDR=Yes";

    But it got an error. Pretty sure I'm wrong. If it's not too much, where exactly in the code am I supposed to put the HDR=Yes part? Sorry, still pretty stupid with programming. >_<


    Ah, I see you are using the ExcelDataReader library. If it's the one from CodePlex/Github then try adding the following line before the AsDataSet method call:

    excelReader.IsFirstRowAsColumnNames = true;

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Hello, I tried your tip but it became a red squiggly line and it the error said: Severity Code Description Project File Line Suppression State
    Error CS1061 'IExcelDataReader' does not contain a definition for 'IsFirstRowAsColumnNames' and no extension method 'IsFirstRowAsColumnNames' accepting a first argument of type 'IExcelDataReader' could be found (are you missing a using directive or an assembly reference?)
    See the following explanation 

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, March 22, 2018 2:49 PM
    Moderator
  • I believe there are several ways to skip the first row!  For one thing, you can try this.

    IEnumerable<DataRow> newRows = dt.AsEnumerable().Skip(numberOfRows); DataTable dt2 = newRows.CopyToDataTable();

    You can try this as well.

    DataRow row = dt.Rows[0]; dt.Rows.Remove(row); techGrid.DataSource = dt;

    Finally, consider this.

    instead of:

    string query = String.Format("select * from [{0}$]", "Sheet1");

    use:

    string query = String.Format("select * from [{0}${1}]", "Sheet1","A2:ZZ");


    MY BOOK

    Sunday, March 25, 2018 2:11 AM