CODEWITHSUNDEEP
c#asp.netsql-serverexcelimport
Pepys
Pepys

Reputation: 991

Importing excel file to SQL database, how to ignore 2nd row; asp.net & C#

I'm importing data in a SQL Server 2008 database from excel file where the first row is headers (HDR=1). The thing is that the second row is also kind of headers which i don't really need to be imported. So how do I ignore the second row from that excel (I guess if the first row is the headers, the actual second row in excel is first)?

In MySQL is just about saying IGNORE LINES 1 in the end of import command ... How do I do it in SQL Server?

Here is part of the code doing that:

 //Create Connection to Excel work book
            OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);


            //Create OleDbCommand to fetch data from Excel
            OleDbCommand cmd = new OleDbCommand("Select [task_code],[status_code],[wbs] from [task$]", excelConnection);

            excelConnection.Open();
            OleDbDataReader dReader;
            dReader = cmd.ExecuteReader();
            SqlBulkCopy sqlBulk = new SqlBulkCopy(connectionString);
            //Give your Destination table name
            sqlBulk.DestinationTableName = "task";
            sqlBulk.WriteToServer(dReader);
            sqlBulk.Close();

Thanks

Upvotes: 1

Views: 2961

Answers (2)

sga101
sga101

Reputation: 1904

A quick solution would be to:

  1. Copy the file
  2. Use Office interop to delete the second line of the spreadsheet
  3. Import the amended spreadsheet

To delete the line from the spreadsheet:

public static void DeleteRow(string pathToFile, string sheetName, string cellRef)
{
    Application app= new Application();
    Workbook  workbook = app.Workbooks.Open(pathToFile);

    for (int sheetNum = 1; sheetNum <  workbook.Sheets.Count + 1; sheetNum++)
    {
        Worksheet sheet = (Worksheet)workbook.Sheets[sheetNum];
        if (sheet.Name != sheetName)
        {
            continue;
        }

        Range secondRow = sheet.Range[cellRef];
        secondRow.EntireRow.Delete();

    }

    workbook.Save();
    workbook.Close();
    app.Quit();
}

Upvotes: 1

Oleg Dok
Oleg Dok

Reputation: 21756

Use the following:

...
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
if( !dReader.Read() || !dReader.Read()) 
  return "No data";
SqlBulkCopy sqlBulk = new SqlBulkCopy(connectionString);
...

Upvotes: 1

Related Questions