saniya_pawat saniya_pawat - 4 years ago 124
SQL Question

MySQL export to MS Excel : 1-9 becomes 9-Jan or 42013

I use Workbench to query database at work. We have a field which indicates company size and has the following options :
1-9
10-49
50-99
100-499
500+

When I export the results containing this field in Excel(which I use for analysis), 1-9 becomes 9-Jan, when I change the format of the cell to text, it becomes 42013. Similarly, 10-49 becomes Oct-50 and in text - 18537. Is there a way to avoid this?

I know this may seem trivial but I take a download of the results every couple of hours or so, and currently, I use the Replace function in Excel to fix this which is a time cost. Also, adding manual intervention increases the probability of error which I want to minimize. I would ideally like the result to export as 1-9, as it exists in the database, based on which the analytical model is built to take input.

I would appreciate any help or pointers on how to fix this issue.

Thanks!

Answer Source

You are not saying how you are bringing the data into Excel. The simplest method is to bring the column in as "text". You can do this when you are importing the data into Excel, by setting the column type to "text".

Alternatively, when you create the output file, you can prepend the value with a single quote or some other character:

select concat('''', company_size)

select concat('_', company_size)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download