W
WindowsRich
Apologies. This is likely not the right Category. Tips to fix this appreciated.
Environment:
Workstation: 16GB RAM. 500MB SSD
Windows 10 Pro (64-bit)
Microsoft Office 2010? 2016? (Home/Student?) Not sure how to tell exactly. Office reports the version as 18.2005.1191.0 ; Excel 2016
SQLServer 2019 (Express Edition) Just installed 2 weeks ago.
Not available on workstation (though some supporting libraries may be present, e.g. .NET framework 3.5 && 4.8 for Windows 10):
Visual Studio
C# compiler
etc.
Task:
Import a USA-state-government-supplied .csv (converted by me to an Excel spreadsheet) into SQLServer.
The spreadsheet ~20 columns and ~500K rows of data to be imported into 1 fairly simple SQLServer table. No stored procedures. No constraints, 1 Primary (Identity) Key.
Microsoft SQL Server Management Studio query windows limits an insert statement to 1,000 records/rows at a time. I did load 2,000 rows successfully. I do *not* want to break the Excel spreadsheet into 500 pieces to load the entire dataset. So, I thought it would be best to use the database Import and Export Wizard to load the spreadsheet.
Error/roadblock (steps to reproduce):
Within Microsoft SQL Server Management Studio,
I select the database.
I select Tasks > Import Data ...
I follow the wizard's prompts, selecting Data Source -> Microsoft Excel; Excel file path (path to local spreadsheet file); Excel version -> Microsoft Excel 97-2003 (or Microsoft Excel 2007-2010 or Microsoft Excel 2013 or Excel 2016); First row has column names; Next
An error is displayed:
TITLE: SQL Server Import and Export Wizard
The operation could not be completed.
ADDITIONAL INFORMATION:
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)
How can I fix this? TIA
Additional info:
ODBC Data Source Administrator (64-bit) Drivers tab has entries for
Microsoft Excel Driver 16.00.4513.1000 (ACEODBC.DLL)
SQL Server 10.00.18362.01 (SQLSVRV32.DLL)
ODBC Data Source Administrator (32-bit) Drivers tab has entries for
Microsoft Excel Driver 10.00.18362.01 (ODBCJT32.DLL)
SQL Server 10.00.18362.01 (SQLSVRV32.DLL)
Finally -
I saw this: datasavvy.me/2017/07/20/installing-the-microsoft-ace-oledb-12-0-provider-for-both-64-bit-and-32-bit-processing/
I tried the steps for Option B, but they did not change my outcome.
Continue reading...
Environment:
Workstation: 16GB RAM. 500MB SSD
Windows 10 Pro (64-bit)
Microsoft Office 2010? 2016? (Home/Student?) Not sure how to tell exactly. Office reports the version as 18.2005.1191.0 ; Excel 2016
SQLServer 2019 (Express Edition) Just installed 2 weeks ago.
Not available on workstation (though some supporting libraries may be present, e.g. .NET framework 3.5 && 4.8 for Windows 10):
Visual Studio
C# compiler
etc.
Task:
Import a USA-state-government-supplied .csv (converted by me to an Excel spreadsheet) into SQLServer.
The spreadsheet ~20 columns and ~500K rows of data to be imported into 1 fairly simple SQLServer table. No stored procedures. No constraints, 1 Primary (Identity) Key.
Microsoft SQL Server Management Studio query windows limits an insert statement to 1,000 records/rows at a time. I did load 2,000 rows successfully. I do *not* want to break the Excel spreadsheet into 500 pieces to load the entire dataset. So, I thought it would be best to use the database Import and Export Wizard to load the spreadsheet.
Error/roadblock (steps to reproduce):
Within Microsoft SQL Server Management Studio,
I select the database.
I select Tasks > Import Data ...
I follow the wizard's prompts, selecting Data Source -> Microsoft Excel; Excel file path (path to local spreadsheet file); Excel version -> Microsoft Excel 97-2003 (or Microsoft Excel 2007-2010 or Microsoft Excel 2013 or Excel 2016); First row has column names; Next
An error is displayed:
TITLE: SQL Server Import and Export Wizard
The operation could not be completed.
ADDITIONAL INFORMATION:
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)
How can I fix this? TIA
Additional info:
ODBC Data Source Administrator (64-bit) Drivers tab has entries for
Microsoft Excel Driver 16.00.4513.1000 (ACEODBC.DLL)
SQL Server 10.00.18362.01 (SQLSVRV32.DLL)
ODBC Data Source Administrator (32-bit) Drivers tab has entries for
Microsoft Excel Driver 10.00.18362.01 (ODBCJT32.DLL)
SQL Server 10.00.18362.01 (SQLSVRV32.DLL)
Finally -
I saw this: datasavvy.me/2017/07/20/installing-the-microsoft-ace-oledb-12-0-provider-for-both-64-bit-and-32-bit-processing/
I tried the steps for Option B, but they did not change my outcome.
Continue reading...