Click here to Skip to main content
15,887,175 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i have a small website where users upload excel sheet and is stored in db. the sheets uploaded have a heading in A1:D then column names and then the data. i would like to skip the heading in A1:D and start importing from there

ive tried to copy what my sheet looks like:
Report for March 2019				
Task    Caption   Start Date	
19139   GP        01/11/2017		

i want to skip the heading 'Report for March 2019'

i am using sqlbulk copy and oledb and i have set HDR=Yes in connection string

What I have tried:

//declare destination table
        string ssqltable = "mytab";
        // make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have different
        string myexceldataquery = "Select * FROM [Details$])";
        try
        {
            //create our connection strings
            string sexcelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelfilepath + ";Extended Properties=" + "\"excel 12.0;hdr=yes;\"";

            string sclearsql = "TRUNCATE TABLE " + ssqltable;
            SqlConnection sqlconn = new SqlConnection(strConnString);
            SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
            sqlconn.Open();
            sqlcmd.ExecuteNonQuery();

            //series of commands to bulk copy data from the excel file into our sql table
            OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
            OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
            oledbconn.Open();
            OleDbDataReader dr = oledbcmd.ExecuteReader();




            SqlBulkCopy bulkcopy = new SqlBulkCopy(strConnString);
            bulkcopy.DestinationTableName = ssqltable;
            //Mapping Table column    

            bulkcopy.ColumnMappings.Add("Task", "[Task]");
            bulkcopy.ColumnMappings.Add("Caption", "[Caption]");
            bulkcopy.ColumnMappings.Add("Start Date", "[Start_Date]");



            {
                bulkcopy.WriteToServer(dr);

            }
            oledbconn.Close();
            sqlconn.Close();
Posted
Updated 12-Apr-19 5:22am
v3

Try this:
string myexceldataquery = "Select * FROM [Details$A2:G];";
 
Share this answer
 
Comments
Member 14183767 12-Apr-19 9:00am    
hi thanks , i tried that first , i got an error saying 'syntax error in from clause'
Member 14183767 23-Apr-19 4:01am    
Many Thanks , it works
Maciej Los 23-Apr-19 4:02am    
You're very welcome.
Your select statment has to provide the range of rows/columns to retrieve, not just the starting cell.

Something like SELECT * FROM [Details$A2:C3]
 
Share this answer
 
Comments
Member 14183767 23-Apr-19 3:22am    
hi , i get the following error 'Syntax error in FROM clause.' and the number of rows in my sheets vary
Member 14183767 23-Apr-19 4:00am    
Hi Thank you it works! i had a bracket that was causing the syntax error
Try doing a read on the data reader before passing it to WriteToServer.

If WriteToServer doesn't "reposition" (most "stream readers" don't), then bulk copy should start on the second record.

OleDbDataReader dr = oledbcmd.ExecuteReader();
dr.Read();
...
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900