I’ve got an Excel spreadsheet with about 250,000 rows, I want to import it into SQL but, by default SQL 2014 doesn’t allow importing of Excel 2010 files.
I’ve used this guide Excel Import to SQL Server using SSIS - www.Excel-SQL-Server.com to attempt to get it to work. I’ve installed the Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine_x64) but I don’t have the option to install fro Excel 201 and nor do I have the Office 12.0 access database engine provider as depicted in the screenshot at http://static.excel-sql-server.com/images/thumbnails/static.excel-sql-server.com/images/excel-sql-server/import-excel-sql-server-ssis-1-ace-1-fill-283x290.png
I know I’m missing something simple but can’t put my finger on it. Any pointers?!
@Microsoft
@robert-for-microsoft
4 Spice ups
Is it a single sheet? If so, save as a csv and import as a flat text file.
6 Spice ups
Nick-C
(Nick-C)
4
In SQL Management Studio right click the database to import into and then go to Tasks > Import Data and follow this wizard, it handles all versions of Excel files (worst case you may need to update your SSMS - this screenshot was taken from the latest standalone version):
2 Spice ups
The problem he is describing is that his SQL Server is 64 bit and his Excel is 32 bit so he has to install the right drivers.
1 Spice up
Nick-C
(Nick-C)
6
Surely just install the 32 bit SSMS then? This obviously can be installed anywhere, it doesn’t need to be on the SQL server so long as you can reach it.
I’ve just tested and my SSMS can read an excel file fine without any additional drivers or tools beyond SSMS when using the Import / Export Wizard (not SSIS) - at least as far as editing mapping anyway which does suggest it read the excel file ok.
It has nothing to do with SSMS. There has to be a match driver on the SQL Server itself.
Excel yes but not excel 2010. I tried the 32bit and 64 bit versions of the data access tool.
it is but there is a free text field that has comma’s in it which threw off the import quite badly. I might try that again.
Can SQL import from tab delimited? That would kill the comma problem.
Yes which would then involved changing the data, something that Excel keeps crashing out at when I try because it’s so big.
Interesting - so it will do save as CSV but save as tab delimited crashes out? Wouldn’t have seen that coming.
1 Spice up
“Yes which would then involved changing the data, something that Excel keeps crashing out at when I try because it’s so big”
How much memory does the machine you’re running this against have?
Depending on the number of columns in the rows and the size of the fields, it’s going to chew up a LOT of memory. Couldn’t you just split it into a few smaller sheets and then export/import each from there?
