Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Syntax error in From running SMSS import Excel to SQL Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 11:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 9, 2013 11:03 AM
Points: 21, Visits: 140
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.
Post #1451700
Posted Friday, May 10, 2013 12:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:55 AM
Points: 12,905, Visits: 32,165
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1451707
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse