Forum Stats

  • 3,839,384 Users
  • 2,262,486 Discussions
  • 7,900,956 Comments

Discussions

Categories

Preserving "Leading Zeroes" when downloading a CSV File

Roxy rollers
Roxy rollers Member Posts: 69 Red Ribbon
edited Jun 3, 2021 5:41PM in SQL & PL/SQL

I have stumbled upon an issue which was working just fine in the past. I have an Interactive Report (Oracle APEX) where I have the option of downloading it as a CSV. I do have a STUDENT_ID field where it varchar2(4) in the database and I have my ID's saved as "0001", "0002", "1067" etc. As we all know, to preserve the leading zeroes when downloading as a CSV has it's challenges. So, in the past, I was having to do this:

select '="' || student_id || '"' from student_tb

This worked out well in the past. However, I am now noticing (Higher version of Oracle) that an annoying tab space is being injected in the beginning. When I open the CSV in notepad, I noticed that it was in the following fashion:

Exhibit #1

"	=""0002"""

So, when you open the Excel file, it looks like this:

image.png

and in the past it was as follows:

Exhibit #2

"=""0002"""

When you open the Excel File, it should look like this:

image.png


If I go and remove the tab space from Exhibit #1 and then open it in CSV, the problem gets rectified. Any idea if there is another way in SQL to format this so I preserve the leading zeroes and have it open correctly in Excel? I tried doing TRIM but that did not help either.

Thanks in advance

Tagged:
· Share on TwitterShare on Facebook

Comments

  • Paulzip
    Paulzip Member Posts: 8,721 Blue Diamond

    Excel mangles CSV files, especially leading zeroes, which is why I and many others don't use it. The ="..." is an Excel hack and is non standard for CSV.

    To fix your issues, either

    1) Don't use Excel for CSV

    • OR

    2)

    a) Don't open the CSV file directly in Excel (as per MS's recommendations with leading zeroes)

    b) Bring the data in through Data>Import External Data>Import Data.

    c) When you do that, the Text Wizard pops up and you can designate the format as Text


    Everything else you are doing is a side effect, NOT a solution.

    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 42,150 Red Diamond

    Agree with PaulZip. The issue isn't with Oracle or Apex, the issue is with how MS Excel opens CSV files and interprets the format of data if you don't use the proper data import wizard to specify the formats. Excel sees something that looks like a number, so it treats it like a number. If you want it treated as text, you have to tell Excel it's text when you import. Fudging your data to try and get it to represent properly in Excel is not the ideal solution. What if something else needs to then consume the CSV data, you'll have inserted a load of rubbish it needs to remove.

    · Share on TwitterShare on Facebook