Results 1 to 2 of 2
  1. #1
    jshockency is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2011
    Posts
    1

    Import Excel Headers to MS SQL

    Trying to import Excel Headers from an Excel 2003 file to MS Sql (MS SQL Server Managment Studio 2008) Table.
    I have a SQL table contains two fields, ID and name. ID is auto increment.
    Excel sheet contains headers and data.

    I have the following code:

    /* Enable SQL to work with Excel */
    EXEC sp_configure 'show advanced options', 1


    reconfigure
    GO
    EXEC sp_configure 'Ad Hoc Distributed Queries', 1
    reconfigure
    GO

    /* Insert in just the headers into mytable */
    Insert into mytable (name)
    SELECT TOP 1 *
    FROM
    OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=C:\customers.xls;IMEX=1;HDR=No;',
    'SELECT * FROM [Sheet1$]')

    I turned of Headers with HDR=no, selecting the top row works.
    I understand that I have selected to many columns to insert in to one row and it won't let me do so, but how can I accomplish this?
    For each value in my selection insert into my table?

  2. #2
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi,

    - I wouldn't use the * operator, but use the fieldnames. If not possible, try to narrow down the selection excel to the filled range.
    - Use SSIS, it's specially there for import/exports

    grNG

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import from Excel
    By Jonathan58 in forum Import/Export Data
    Replies: 18
    Last Post: 07-19-2013, 09:52 AM
  2. import txt file with multiple headers
    By vojinb in forum Import/Export Data
    Replies: 9
    Last Post: 07-25-2011, 10:37 AM
  3. Import excel worksheet by range with non identical headers
    By snoopy2003 in forum Import/Export Data
    Replies: 1
    Last Post: 03-14-2011, 01:04 AM
  4. Import Excel with SQL - again
    By tpcervelo in forum Queries
    Replies: 1
    Last Post: 08-06-2010, 08:13 PM
  5. VBA to open excel, import on close of excel
    By bdaniel in forum Programming
    Replies: 0
    Last Post: 03-20-2010, 02:45 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums