Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

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.OLEDB.4.0',
'Excel 8.0;Database=D:\Websites\d2v\D2V-database.xls', 'SELECT * FROM [Sheet1$]')
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?
0
tingleweb
Asked:
tingleweb
1 Solution
 
YZlatCommented:
I suggest that instead of SELECT * do SELECT Column1, Column2 ... etc.
0
 
matrix_aashCommented:
If you really want to automate the process it will be worth doing in SSIS in MS SQL 2005(Integration Services).
0
 
Sham HaqueSenior SAP CRM ConsultantCommented:
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...
0
 
ZberteocCommented:
I find this way prefferable:

INSERT INTO YourTable
      (field1, field2,...)
SELECT
      field1, field2,...
FROM
      OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;
                        Database=D:\Websites\d2v\D2V-database.xls', Sheet1$) excel


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.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now