CSV Open Excel Text Number As Text

[wsnyder]

Hi all,

Using SQL Developer, Microsoft 365.
In SQL, I convert an account_number to text:
to_char(acct_nbr)

I then export to csv file
but when I open in Excel, it is a number

I then tried adding an apostrophe to the front of the acct_nbr:
''''||to_char(acct_nbr)
And exported to CSV

When I open the file an acct nbr that has 5 char's now has length of 6 char's and I can see the apostrophe.

I also tried just 2 apostrophes but when I open the file, again it is a number not text.

Thanks,
-w

 

[Joe4]

When opening CSV files directly in Excel, Excel tries to "guess" the format of all the fields, and fields that look like numbers will be imported as numbers, which will drop leading zeroes.
You need to open the file using the "Data Import Wizard", which does not come up by default in the newer versions of Excel. You can choose to bring it back in a "legacy" view (as shown here" Text Import Wizard.), or use the method described here: Stop truncating / dropping leading zeroes when opening csv file

Either way, you want to format that field where you want to maintain the leading zeroes as Text.

 

[wsnyder]

Thanks Joe,

The Text Import Wizard link solved it

Thanks,
-w

 

[Joe4]

You are welcome!

Yes, I am a little annoyed that they took that away. That used to be the default thing that popped up when importing text files.
I like it because it gives me control of the data, as opposed to letting Excel "guess", and they often guess wrong.
Since CSV files do not have any formatting, there is no way for Excel to know that numbers are supposed to be text.

 

[Marc L]

Hi,​

​

numbers as text between double quotes and selecting the appropriate option in the Import Wizard …​