Import Excel Spreadsheet to Database Tables

  • 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?

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

    /*

    Ref: http://www.connectionstrings.com/excel-2007

    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 *

    FROM OPENROWSET(

    'Microsoft.ACE.OLEDB.12.0',

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

    'SELECT * FROM [Sheet1$]');

    Select * from opendatasource(

    'Microsoft.ACE.OLEDB.12.0',

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

  • Denis,

    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.

  • sckemp1,

    Did you save your spreadsheet as xml in Excel?

  • Gordon,

    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.

  • Gordon,

    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

  • 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!! )

  • Peter,

    That makes sense. Thank you.

    Denis


    Denis W. Repke

  • 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?

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

  • Thanks for the article.

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

  • 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!! 🙂

  • Peter,

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

  • Greg staley:

    Thanks for your kind words.

    changc-708876:

    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.

  • JGay 2041,

    You can post it here.

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

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