0

I'm trying to build an import utility for users to import Excel files into SQL Server 2017.

I've hit the 32bit/64bit roadblock with OLEDB providers.

I'm using a 32 bit Microsoft Access front end, and SQL Server is 64 bit. You can't install both 64bit and 32bit ACE OLEDB providers. Microsoft have even built two different import/export wizards for this problem (one for 32bit, one for 64bit).

Does anyone know a way around this problem, or an alternative method of getting Excel data into SQL Server from a Microsoft Access front end that doesn't rely on OLEDB providers? (saving as a text file is not an option, neither is installing 64bit Office)

Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Tim
  • 69
  • 5
  • You can try https://datasavvy.me/2017/07/20/installing-the-microsoft-ace-oledb-12-0-provider-for-both-64-bit-and-32-bit-processing/, not tested! But what is wrong with csv? – ComputerVersteher Aug 24 '18 at 10:55
  • Thanks.Seen that before, doesn't help. You end up breaking Access so it won't start – Tim Aug 24 '18 at 11:19
  • Having said that, that was when Access was on the same machine as SQl Server. I now have SQL Server and access on two different virtual machines, I'll have a play – Tim Aug 24 '18 at 11:20
  • There is an open source project I believe is called OpenXml that works for xlsx files. There is a tool for a very small fee that I don't remember the name of - something like csvreader(that works with most excel files depending on the origin of the file). I had used a combination of those tools to automate table creation and import of all types of excel and csv/text. – Joe C Aug 24 '18 at 11:22

3 Answers3

0

i think BULK INSERT will help you solve this. See here link

Danielson Sanches
  • 124
  • 4
0

We supply an Excel Add-In which you can use to build solutions to let end users insert and update data in SQL Server directly from Excel.

You can download a fully functional trial here.

Disclaimer: I am the founder of the company.

JohannesGbg
  • 101
  • 1
  • 4
0

You can use SSIS package to load EXCEL file into SQL Server. You can have data flow task and have Excel source and Sql server target.

UPDATE
If you are having 64 BIT, 32 BIT related issues, you can execute SSIS package in 32 bit runtime using dtexec.exe.Read more dtexec

You can execute SSIS package from C#

Generally, Servers and Desktop machines will have 32 BIT OFFICE drivers installed already. So, you should not have issues. If required, you would need to run dtexec.exe in 64 BIT. You can download dtexec.exe accordingly.

More information on using dtexec.exe

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58
  • When I used the Import Export wizard (32 version for some files, and 64bit version for others, not practical), and looked at the .dtsx file in notepad, it had the same OLEDB connection strings I was using, so doesn't overcome the 64 bit/32 bit issue, unless I'm missing something – Tim Aug 29 '18 at 09:53
  • @Tim, I have updated the answer. You can execute the package in 32 bit runtime, using dtexec.exe – Venkataraman R Aug 29 '18 at 12:15
  • Will this still require installing 32bit drivers on the server? – Tim Aug 30 '18 at 10:51
  • @Tim, I have updated the answer. You will not generally need to install 32 bit drivers on the server. The drivers will be there with Servers and desktop machines. – Venkataraman R Aug 30 '18 at 15:43