Exporting data by cell to Excel from Database is something that is quite interesting. Usually there is no need to directly connect to the DB from excel but there was this scenario.
In the query generated, there are html style codes written in the cell. Copying directly from the table and paste to an excel will produce a very ugly pasting where it will overlap to different cells.
This is a sample table data.
Target Excel Export Data
Do note usually by copying and paste, we should usually get the data as below.
Instead, by copying the table in Label1 into excel, the data will be as such.
This will undoubtedly be not suitable for data clarity as it will be difficult to read the data on the excel.
Best Way to Export data by cell to Excel
In the end, the usual way of copy and paste is not the suitable way to do this. On other occasions, this is the perfect way to get data and then perhaps to share the excel file to other recipients. What can one do then? Copy the data elsewhere and perhaps paste to Excel one by one? This could work but it will be time consuming considering if there were like 100 or perhaps more than that.
Use Data Import From DB
Voila, the best way is to actually directly import the data from the database. Simply said, this will the best bet for one to grab the data as it is based on the column itself without the overlapping factor.
- Go to Data > Get Data > From Database > From <Database>
2. Enter the connection string to the DB, usually with the IP and the port number of the database.
After that depending on the configuration and the database selected, you can then run the SQL to grab the data or just browse through the tables to grab the desired data which will then display correctly as per the screen earlier.
There you have it, the best way to export data by cell to Excel is laid right down for you. This is perhaps the easiest way to get data directly to Excel, provided you know the credentials to the database.
If this helped you, you may subscribe to my feed and perhaps a round of donation to this blog. In the future, more tutorials will begin perhaps on YouTube.
