Preserving Leading Zeros in Excel Output
We have alphanumeric data, such as serial numbers (e.g., ‘00123’), that loses its leading zeros when exported to Excel because Excel interprets it as a numeric value. How can I modify the SQL query to force Excel to treat these columns as text, preserving the leading zeros?
7 replies
To prevent Excel’s automatic type detection from treating alphanumeric data with numeric content as a number, you must explicitly format the column output in the SQL to enforce text formatting . The workaround involves prefixing the column data with an equal sign and enclosing it in double quotes . The SQL should look like this: `’=”‘ || cii.serial_number || ‘”‘ serial_number` .
No, if you apply this specific SQL workaround and run the report in the recommended XLSX output format, Blitz Report is designed to automatically remove the redundant prefix (`=”`) and suffix (`”`) characters from the data upon generation . This ensures the content displays correctly as alphanumeric text in Excel .
The documentation specifies that the automatic removal of the prefix/suffix characters (`=”` and `”`) occurs when the report is run in XLSX output format . While CSV format also preserves alphanumeric data, the specific automated cleaning process is noted for the XLSX format . If using CSV, you should verify if the raw CSV output still retains the extra quotes/equals signs.
- You must be logged in to reply to this post.
Sign in with LinkedIn or Google to take part.