MS SQL Server Studio, can't import Excel any more since upgrade to Win 11

Factual Questions
1

For the last six years it has been part of my job to import data from Excel workbooks into MS SQL Server. I would open Microsoft SQL Server Management Studio (it says it is version 18.12.1) and then use the Object Explorer (a floating window of MS SQL Server Mgmt Studio). In Object Explorer I’d locate the database on the list, right-click it, select the Tasks submenu and pick “Import Data”, navigate to the appropriate Excel file, then confirm which columns go into what and let it do its thing.

About 2 weeks ago, IT replaced all the Windows 10 boxes with Windows 11 boxes on our floor. Now I can’t import Excel files into SQL.

After picking the Excel file and hitting the “Next” button I get this error:

I did some searching online using that exact error message text and found several hits with similar problem descriptions, so it’s not just me. Found people who claimed to have figured the problem out, too – they’re saying the Import Wizard of SQL Server is 32 bit and its a 32-bit vs 64-bit problem.

another such link:

https://stackoverflow.com/questions/9943065/the-microsoft-ace-oledb-12-0-provider-is-not-registered-on-the-local-machine/10655821#10655821

I don’t have the necessary privileges to install diddly squat on this machine, so I have to tell the IT Department what they need to install. Our IT Department is umm, not ideal. They did open a ticket for this issue and they reinstalled the 64 bit driver (they left it behind in C:\Temp, files named AccessDatabaseEngine.exe and AccessDatabaseEngine_X64.exe).

I watched as they installed the 64 bit variant, and on a later return they also tried installing the 32 bit version only to get error messages that it would conflict with the 64 bit Microsoft Office installation. They basically shrugged, then, and closed the ticket and said they’d done all they could do.

On one of the linked pages of answers and other people complaining about this, I encountered someone claiming that there exists a 64 bit version of the Import Wizard. And that it is the default 32-bit version that is causing the problem. I tried following their description of where to invoke the 64 bit version but don’t seem to have any such animal installed on my computer.

That seems like a hopeful sign though: if I could find out more about this alleged 64 bit Import Wizard, I could tell IT what it is officially called, where they would find it, and request that they install it for me.

Anyone with relevant info?

Option Two is to save the Excel files as .csv or tab-delimited text, since the Import Wizard can also import from a flat file format (it recognizes either of those two). But I can’t figure out how to bypass or fix the “mismatched data type” complaint that pops up when I attempt import. The text file format data is perceived as text data in places where the destination columns in SQL are integer numerical.

2

This issue doesn’t surprise me; Office updates would cause many headaches for us trying to use MS import tools.

Two ideas: it may have the same problem, but do you have access to SSIS (Integration Services)?

I ended up relying on third party tools for the last 10 years; my favorite is TOAD for SQL Server. I believe there is a free/trial version. It offers many things beyond import/export.

3

Search bar “SSIS” = no result found; “Integration” = no results found

4

This seems clearly like an IT screwup, and it shouldn’t be your job to do theirs for them. If this is crucial for your job, then they need to either fix it via elevation to those who care (i.e. your boss and above) or give you the access to fix it.

5

I hesitate to nudge you down the SSIS rabbit hole, because thar be dragons, but it is the MS official tool for import, export, and transformation. It is included with MS SQL Server, but IT would have to give you access/install the client. I would definitely not characterize it as a generic end-user tool, but it is likely what MS would suggest and it is already included.

Switching to CSV or other old-school format would be the easiest, quickest thing to try. It is still a go-to in many situations, even between professional DBAs who just want to get the job done, reliably.

6

Do you have a SQL engine installed locally? Or do you just have SSMS and are connecting to a remote instance?

SSMS itself is still 32 bit, it’s a bit of a dinosaur. The 2nd comment in the link in your OP has instructions for how to install BOTH the 32 bit and 64 bit access engines; I’ve had to do that before, and that’s probably the case here. If your IT department can’t follow those instructions…

Good luck.

7

On a good day our IT Department can lift the telephone receiver and figure out which end to talk into. I do indeed have to do their job for them if it’s outside the scope of whatever is routine for them, and if I want it to actually get done.

8

I don’t know if it will fox your problem, but the newly releases SSMS version 21 (with copilot integration, barf) is 64 bit. You could try using that.

9

Well of course it would conflict: they just installed the 64 bit version.

The first message “is not installed” suggests that before they installed the 64 bit version, it was not installed, which suggests that, what ever version of Office or Office-like you are using, it did not include MSAccess.

So uninstalling the 64 bit mistake won’t have any effect on anything.

So, uninstall the 64 bit mistake, install the 32 bit version.

I could be wrong, And there may be better tools. Whatever.

You correct the csv import problem by making sure that the top line of the CSV file is unambiguous, or by changing the registry value that tells it how many lines to check before deciding what the column type is, or by telling the tool what columns to import as what.

(Or by telling the tool to import text into a temp table, and converting types in SQL Server after the import. With very messy imports, you import the whole line as text, and then use code to break it up into columns.)

10

Well, Access of some form was definitely already installed before I attempted import into SQL. I only import certain (minority) file types into SQL whereas I import into Access daily and sometimes correct prior entries.