asked
Import multiple excel 2010 files to SQL 2008 r2
I have bunch of excel 2010 files, need them to import into SQL 2008 r2. Explain me various ways to import them. (I have tried using SSIS, SQL Import Wizard, Open Rowset, Manual Copying by editing the table, but failed in some situations) appreciate your concerns !!
Finally managed to solve using "OPENROWSET". Gone through various forums to fix it (
http://visakhm.blogspot.in/2013/12/how-to-solve-microsoftaceoledb120-error.html)
If the structure is same for all the files across all sheets then ssis is the best bet....search for loading multiple excel files with multiple sheets into sql database; you will get tons of articles. .. It would require 2 looping mechanisms ...one through sheets and one through files
If the structure is same for all the files across all sheets then ssis is the best bet....search for loading multiple excel files with multiple sheets into sql database; you will get tons of articles. .. It would require 2 looping mechanisms ...one through sheets and one through files
Useful breaks --Excel 2007-2010 SELECT * --INTO #productlist FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;Database=C:\temp\Products.xlsx', 'SELECT * FROM [ProductList$]'); --Excel 97-2003 SELECT * --INTO #productlist FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;Database=C:\temp\Products.xls', 'select * from [ProductList$]');
18 People are following this question.
