Wednesday, May 13, 2009

How to preserve leading zeros in a CSV file using Oracle

Some time back i was working on alerts which will extract the data from Oracle and send alert extracts to the users via email as attachments in 'CSV' format. But when i received the notifications i noticed that the leading zeros in the segment values of the code combinations were truncated.

After some research i found that when ever the Excel opens up a CSV file it defaults all cells to "General" formatting and removes the leading zeros on cell values thus converting them from strings to numbers.

The simple solution is to SELECT the value such that Excel will see it as a string and not a number. So we prefixed an equals sign and double quote on to the value and post fix a double quote. So

SELECT xx_column
FROM xx_table


SELECT '="'||xx_column||'"'
FROM xx_table

No comments: