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

Improve this question
4
  • You can try datasavvy.me/2017/07/20/…, not tested! But what is wrong with csv? Commented Aug 24, 2018 at 10:55
  • Thanks.Seen that before, doesn't help. You end up breaking Access so it won't start Commented Aug 24, 2018 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 Commented Aug 24, 2018 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. Commented Aug 24, 2018 at 11:22

3 Answers 3

Reset to default
0

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

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Looks like that's for text files
not just txt files, in my case i used this method to upload a *.csv file, go to the doc page and see how can use it to fit your case!
csv is a text file. I can't see anything on the internet using this to import excel files (.xls/.xlsx extension)
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.

Improve this answer

Comments

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

Improve this answer

4 Comments

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, I have updated the answer. You can execute the package in 32 bit runtime, using dtexec.exe
Will this still require installing 32bit drivers on the server?
@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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.