Using the SQL Server Export generating an excel spreadsheet from an access database
What a wordy title, go read it again, it's crazy but true.
This post covers generating an Excel spreadsheet from an Access Database but using a SQL Server tool. It's crazy but great and easy!
I am assuming you have SQL Server installed and the import export wizard, if you don't have this go get it first before carrying on.
First up open the Import and Export Data application, note this has to be the x86 edition so if you are running an x64 machine, which I think most dev's are nowadays, ensure you choose the correct edition.
This will give you the welcome screen.
Click next to get the ball rolling, this first screen is where we want to get the data from, in this case access, so use the drop down box to select Microsoft Access. Then provide the path to the database file, you can see my file path was actually on the network and it worked fine.
Next up the destination, well again Excel is what we want so highlight in the list and provide a path to save too. You can also choose the version of Excel you want the file format to be, lots of options which is great.
Now upon clicking Next here two things may happen, you might proceed to the data copy screen or your might get an error. On my machine I got an error however on others I did not. The error was: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)
If you don't have office installed going to http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=13255 and downloading the 32bit Access Database Engine will fix this however I already has Office 2010 so this wasn't the issue. However if you remember earlier I mentioned about the difference between x86 and x64 well it's back to haunt us, I installed 64bit Office, at the time I thought why not I have 16GB ram, something needs to attempt to use it.... well this meant that only a 64bit provider was registered. I attempted to download the 32bit access database engine but it won't install if you have 64 bit office, which does make sense. So I rolled myself back to 32bit Office, sad but true.
Any how, with all that sorted you will be at the copy or query screen, if you simply want all your data out of your database in the form it is stored you could use the copy table option. However I wanted to pull data from several tables and output it into a specific format so I chose the query option.
You then get a nice big text box to enter your query, you could be hard core and enter this direct and ensure it parses etc but I'm guessing like most people you would have this query tested in Sql Management Studio and would simply paste it in or use the browse button to point it to a saved query.
It is worth noting here that I believe there is a bug with this window, if you browse and load a file I was finding that only half of my query was in the window, however if I copy pasted it worked fine.
The next window asks you to confirm the tables and views to copy, when you are using a custom query there isn't a lot more you can do on this screen so click next again.
This next screen, Run Package, gives you the option to run the export now or to save it as a package that can be reused or used later. However you can only do this on SQL Server Standard, Enterprise, Developer or Evaluation. If you have SQL Server Express, Web, or Workgroup you can only run it immediately.
When you choose to run the package you get a progress window showing what's happening, what's left etc, very similar to the SQL installation progress screens, however when it's done you should have all green ticks and an excel file with you access data in.
Cracking stuff, if you haven't before you should take some time looking at the SQL Import and Export tool and DTS more fully, it allows you to import and export data from a vast array of sources into just as many again. I have used it to take old Access DB's and create SQL ones and managing data migrations, it's a tool that should be in every developers belt. We don't need to continually write console or windows form apps for managing data we already have a tool that will do the majority of use cases, we just have to use it and write the queries!
I hope this helps, I know it saved me a ton of time :)
This post covers generating an Excel spreadsheet from an Access Database but using a SQL Server tool. It's crazy but great and easy!
 |
| Welcome! |
First up open the Import and Export Data application, note this has to be the x86 edition so if you are running an x64 machine, which I think most dev's are nowadays, ensure you choose the correct edition.
This will give you the welcome screen.
 |
| Source Screen |
 |
| Destination Screen |
Next up the destination, well again Excel is what we want so highlight in the list and provide a path to save too. You can also choose the version of Excel you want the file format to be, lots of options which is great.
Now upon clicking Next here two things may happen, you might proceed to the data copy screen or your might get an error. On my machine I got an error however on others I did not. The error was: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)
 |
| Copy or Query |
Any how, with all that sorted you will be at the copy or query screen, if you simply want all your data out of your database in the form it is stored you could use the copy table option. However I wanted to pull data from several tables and output it into a specific format so I chose the query option.
 |
| What a query |
It is worth noting here that I believe there is a bug with this window, if you browse and load a file I was finding that only half of my query was in the window, however if I copy pasted it worked fine.
The next window asks you to confirm the tables and views to copy, when you are using a custom query there isn't a lot more you can do on this screen so click next again.
This next screen, Run Package, gives you the option to run the export now or to save it as a package that can be reused or used later. However you can only do this on SQL Server Standard, Enterprise, Developer or Evaluation. If you have SQL Server Express, Web, or Workgroup you can only run it immediately.
 |
| Success! |
Cracking stuff, if you haven't before you should take some time looking at the SQL Import and Export tool and DTS more fully, it allows you to import and export data from a vast array of sources into just as many again. I have used it to take old Access DB's and create SQL ones and managing data migrations, it's a tool that should be in every developers belt. We don't need to continually write console or windows form apps for managing data we already have a tool that will do the majority of use cases, we just have to use it and write the queries!
I hope this helps, I know it saved me a ton of time :)
Comments
Post a Comment