Import from Excel to SQL Server causing problems
Hello all.
I've seen lots of questions relating to this subject so please excuse me for repeating question but....
I am using :
SELECT * INTO Product FROM OPENROWSET('Microsoft.Jet.
'Excel 8.0;Database=D:\Websites\d
to import frm an Excel spreadsheet but its causing me problems.
Its bringing in about 200 extra rows than are actually populated in Excel and some of the columns dont import correctly. I also need to drop the table first for the import to happen which I'd rather not do due to table relationships, etc.
What is the correct script I should be using please?
I've seen lots of questions relating to this subject so please excuse me for repeating question but....
I am using :
SELECT * INTO Product FROM OPENROWSET('Microsoft.Jet.
'Excel 8.0;Database=D:\Websites\d
to import frm an Excel spreadsheet but its causing me problems.
Its bringing in about 200 extra rows than are actually populated in Excel and some of the columns dont import correctly. I also need to drop the table first for the import to happen which I'd rather not do due to table relationships, etc.
What is the correct script I should be using please?
YZlat
I suggest that instead of SELECT * do SELECT Column1, Column2 ... etc.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
if it works for you, my preferred approach is to send the data to SQL Server from Excel, using VBA and linking to the database with ADO.
This then gives you much more control over the data in Excel.
Your SELECT ... INTO ... FROM ... is useful for creating a table first time around, but after that, you should get the data you need from a consistently formatted Excel sheet with INSERT...FROM...
You can use 'SELECT * FROM [Sheet1!A1:Z100$]' to select the cells to be transferred.
alternatively, as suggested above, use SSIS (2005) / DTS (2000) and create a package to do the job...
This then gives you much more control over the data in Excel.
Your SELECT ... INTO ... FROM ... is useful for creating a table first time around, but after that, you should get the data you need from a consistently formatted Excel sheet with INSERT...FROM...
You can use 'SELECT * FROM [Sheet1!A1:Z100$]' to select the cells to be transferred.
alternatively, as suggested above, use SSIS (2005) / DTS (2000) and create a package to do the job...
I find this way prefferable:
INSERT INTO YourTable
(field1, field2,...)
SELECT
field1, field2,...
FROM
OPENROWSET('Microsoft.Jet.
Database=D:\Websites\d2v\D
The reason your columns are imported correctly is because they have heterogenous data in them, numeric and alphanumeric. You have to make sure that all the values in a column are either numeric or alphanumeric.
You don't have to DROP the table but you can use:
TRUNCATE TABLE YouTable;
This keeps all yor defaults, relationsheips and resets identity column if you have one. Is also very fast compared to DELETE FROM YourTable if the table is big.
INSERT INTO YourTable
(field1, field2,...)
SELECT
field1, field2,...
FROM
OPENROWSET('Microsoft.Jet.
Database=D:\Websites\d2v\D
The reason your columns are imported correctly is because they have heterogenous data in them, numeric and alphanumeric. You have to make sure that all the values in a column are either numeric or alphanumeric.
You don't have to DROP the table but you can use:
TRUNCATE TABLE YouTable;
This keeps all yor defaults, relationsheips and resets identity column if you have one. Is also very fast compared to DELETE FROM YourTable if the table is big.