Using Openrowset to import data from a CSV file

If you are using a 64 bit edition of Windows then you are most likely using a 64 bit edition of Office and as a result of Access too. If you however have a 32 bit version of Office installed you will face issues when trying to create an openrowset connection to a csv file as I did recently. The solution is to use to install the 32 bit access database engine and install the 64 bit access database engine from the below link.

https://www.microsoft.com/en-in/download/details.aspx?id=13255

The link also contains the 32 bit engine as well for vice versa.

Once installed you will be able to query the csv file as shown in the screenshot below

uing openrowset to query CSV file

This problem manifests itself in a number of ways with different error messages so the best way to implement this is to start with a clean slate.