Import Excel Spreadsheet to Database Tables

  • gorthog

    Ten Centuries

    Points: 1004

    This may be a little off-topic, but is there a way to convert dBase files to xml for import into SQL Server 2008 (64-bit) tables?

  • Jim Cheong

    SSC Journeyman

    Points: 90

    Here is the Openrowset/Opendatasource Test Script you can use after the above installation.



    If you want to read the column headers into the result set (using HDR=NO even though

    there is a header) and the column data is numeric, use IMEX=1 to avoid crash.

    To always use IMEX=1 is a safer way to retrieve data for mixed data columns. Consider

    the scenario that one Excel file might work fine cause that file's data causes the

    driver to guess one data type while another file, containing other data, causes the

    driver to guess another data type. This can cause your app to crash.


    SELECT *



    'Excel 12.0 XML;HDR=YES;IMEX=1Database=c:\install\AgeHeartRate.xls;',

    'SELECT * FROM [Sheet1$]');

    Select * from opendatasource(


    'Excel 12.0 Xml;IMEX=1;Database=c:\install\AgeHeartRate.xls;')...[Sheet1$]

  • peterhe


    Points: 11363


    First, in Your scenario, the file needs to be in the C:\ drive of the SQL Server box;

    Second, check the file security:

    If you log on SQL Server by a SQL Server login, the SQL Server service (process) account is used to access the file.

    If you log on SQL Server by Windows Authentication, you can read only those files that can be accessed by your windows account, regardless of the security settings of the SQL Server service account.

  • peterhe


    Points: 11363


    Did you save your spreadsheet as xml in Excel?

  • Atradius

    SSC Eights!

    Points: 829


    what program creates your dBase files?

    Newer version of dBase, and their clones such as Visual Foxpro, have support for converting tables to XML files.

    Otherwise it is quite elementary, although not so fast, to skip through the table and adding XML tags around the values.

  • peterhe


    Points: 11363


    To import dbfile to SQL Server, a better way is to use SSIS. 64 bit sql server has both 32 bit and 64 bit SSIS command line. You can use the 32 bit command line (DTExec), which will use the 32bit jet provider, to import data to 64 bit SQL Server

  • sckemp1


    Points: 16

    Oh, I'm a moron. I thought that the stored proc did the conversion directly from xls. My bad.

    Thanks for pointing that out. ( sorry!! )

  • Denis W. Repke


    Points: 436


    That makes sense. Thank you.


    Denis W. Repke

  • changc-708876


    Points: 5

    It's not working for me when I use UNC path. give me this error:

    Msg 50000, Level 16, State 1, Procedure uspImportExcelSheet, Line 497

    Cannot bulk load because the file "\\Projects\FTP-Download\BatchData\CWR\Warrants_2010-02-16.xml" could not be opened. Operating system error code 5(Access is denied.).

    I ran this script against a development sql server from my local management studio. the file is locate at network drive.

    can anyone help?

  • gorthog

    Ten Centuries

    Points: 1004

    The program that creates the dBase file is a canned student information system that utilizes dBase for data storage. The system does not provide any tools for working with the dBase files. In an effort to migrate clients to a SQL Server based solution, I have used ad hoc openrowset queries to load the dBase files into SQL Server tables. This solution works well, except where a 64-bit version of SQL Sever is installed.


    SSC Guru

    Points: 281243

    Thanks for the article.

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • greg.staley

    Valued Member

    Points: 67

    This is a FANTASTIC article that makes a tedious process EASY.

    The only reason I can think of why someone would not rate this article as a 5 star is because somehow they just dont get it...or else they are a genius!! 🙂

  • JGay 2041

    SSC Enthusiast

    Points: 142


    I need help dissecting your ImportExcel.sql. Is this the appropriate forum or should I e-mail you directly?

  • peterhe


    Points: 11363

    Greg staley:

    Thanks for your kind words.


    As the error message said, it is the security setting issue. The account you used (the Windows account if you used trust authentication or the SQL Server service account if you used SQL login) to login to SQL Server does not have the permission to access the shared file in the remote machine.

  • peterhe


    Points: 11363

    JGay 2041,

    You can post it here.

Viewing 15 posts - 16 through 30 (of 70 total)

You must be logged in to reply to this topic. Login to reply