Excel automatically changes format from numeric to either date or other number format

Question

I wrote a query in postgresql to get the data I need and want to further analyze it in excel. But everytime I past my numeric variables it either turns into a short date format or a number like 27.1630333333333 is turned into 271.630.333.333.333. I could round it (which will mean loss of information) such that it turns into just 27 but sometimes I have values like 1085.01 and this is too transformed into 108.501, which is not desirable. How an I keep the original formatig from the query?

This is an extract from the data output that I query: Query Output

This is the paste option I can choose from, after copying the query output to clipboard: Paste Option

And when I copy it to the clipboard and paste it I get an output looking like this: Output pasted in Excel

Answer 1

It's difficult to say what the issue here is, but since you are saying you have potentially a locale mismatch between either the database and your system, or Excel and your system, then it's likely related to that.

Your Query results look as if the period is the decimal separator, in which case the comma would be the thousand separator. However, when you're pasting the number into Excel (which you said was German), the period is treated as a thousand separator, and the numbers are pasted as very large integers, separated into groups of three digits by periods.

So, you can either:

a) cast the query results to text then replace the period with a comma, something like this:

replace(query_column::text,".",",")

Then copy the output and paste.

OR

b) Change the separator settings in Excel. Go to File>Options>Advanced, un-tick the "Use system separators" and then change Excel to use a period for the decimal separator and a comma for the thousand separator.

Answer 2

I am assuming you are using Office 365, since you did not specify a version of Excel.

If you want to prevent Excel from attempting to automatically categorize (and potentially corrupt) your data, then before pasting any data you want to change the numerical format of the field. Ctrl+a, and change the format ("General" by default) to "Text"

Now the format of the each field will not "want" to change the format of your numbers. However, depending on the data you are pasting in, Excel may interpret your data as including formatting of its own. This can lead to the the cell formats being changed to "Date," "Currency," etc., and can still ruin your data.

To prevent this and keep your formats as "Text," use the "Paste Special" option under the paste menu. The paste option you are looking for is called "Paste Text" or "Keep Text Only," depending on which menu you look in.

Doing these two things should prevent Excel from messing with your data.