Export an entire sheet from Excel to SQL server

Question

I'm trying to create a query to export a sheet from excel to SQL Server, I came up with this query yet I'm getting the error Invalid object name Sheet1$

How can I select from the sheet: "Sheet1"?

s = "INSERT INTO TestTable SELECT * FROM [Sheet1$] "

cn.Execute s

score 1 · Answer 1 · edited May 23 '17 at 12:30

In your case i guess sql server doesnt have access to sheet1 file. Check here how to make file accessible or what could be the problem for sql to locate your file. There are 2 ways that i know of how you could achieve this.

1>>

BULK INSERT TestTable
    FROM 'C:\CSVData\sheet1.xls'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    ERRORFILE = 'C:\CSVDATA\SchoolsErrorRows.txt',
    TABLOCK
    )

But make sure on your system sql server has access to folder from where you want to take the excel file and you have bulk import rights Check out more info here

2>> Also you could use sql import wizard like this.

Export an entire sheet from Excel to SQL server

1 Answers1