Post reply

Syntax error in From running SMSS import Excel to SQL

  • May 10, 2013 at 11:38 am

    #273842

    I am trying to import multiple .xls files into a single SQL 2008 db table using a query. The import wizard works fine for a single file, but I have over 300 that need to go into the sql table.

    This is the query I am using in the import/export wizard:

    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=\\files\census\Census_02092013', [Census$])

    that returns this error:

    Syntax error in FROM clause.(Microsoft JET Database Engine)

    Ultimately, I want to save as SSIS Package and run for all the files with the filename_mmddyy but it would help to know why I am getting this error.

  • May 10, 2013 at 12:07 pm

    #1614286

    if your SQL2008 is 64 bit, you must install the 64 bit ACE drivers; the JET driver is only valid for 32 bit, and it won't work on .xlsx files(or files saved from office 2007/2010 even if .xls?) at all .

    This is a working, tested, openrowset command that works for me:

    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1

    GO

    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

    GO

    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',

    'Excel 12.0;Database=C:\Data\BlockGroups_2010\AKblockgroup.xls',

    'SELECT * FROM [AK$]')

    for installation/setup instructions for the ACE drivers, take a look at this thread:

    http://www.sqlservercentral.com/Forums/Topic1407044-391-2.aspx

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

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