Import from Excel to SQL Server causing problems

Posted on 2007-10-05
Last Modified: 2010-03-19
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?
Question by:tingleweb
    LVL 35

    Expert Comment

    I suggest that instead of SELECT * do SELECT Column1, Column2 ... etc.
    LVL 8

    Accepted Solution

    If you really want to automate the process it will be worth doing in SSIS in MS SQL 2005(Integration Services).
    LVL 18

    Expert Comment

    by:Sham Haque
    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...
    LVL 26

    Expert Comment

    I find this way prefferable:

    INSERT INTO YourTable
          (field1, field2,...)
          field1, field2,...
          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:


    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.

    Featured Post

    Live: Real-Time Solutions, Start Here

    Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

    Join & Write a Comment

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now