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.

    /*

    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$]

  • peterhe

    SSChampion

    Points: 11363

    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.

  • peterhe

    SSChampion

    Points: 11363

    sckemp1,

    Did you save your spreadsheet as xml in Excel?

  • Atradius

    SSC Eights!

    Points: 829

    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.

  • peterhe

    SSChampion

    Points: 11363

    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

  • sckemp1

    Grasshopper

    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

    SSC-Addicted

    Points: 436

    Peter,

    That makes sense. Thank you.

    Denis


    Denis W. Repke

  • changc-708876

    Newbie

    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.

  • SQLRNNR

    SSC Guru

    Points: 281243

    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

  • 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

    Peter,

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

  • peterhe

    SSChampion

    Points: 11363

    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.

  • peterhe

    SSChampion

    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