Monday, February 17, 2014

How to remove trailing zeros from csv files


I had a task to do extraction from Oracle DB to CSV files and then remove all trailing zeros at last column.

Trim trailing zeros from all file:
ls $OUT_PATH/*.csv | while read file; do
   sed -i 's/[ ]*$//;s/\.00$//;s/\.0$//;s/\(\.[0-9]\)0$/\1/' ${file}
done


Script that do extraction to file by sqlplus:

Bash script:
#!/usr/bin/env bash

CONNECT="user/password@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=SERVER)(Port=1521))(CONNECT_DATA=(SID=my_sid)))"

sqlplus $CONNECT @extract.sql result.csv


SQL file (extract.sql):

set echo off
set feedback off
set verify off
set pagesize 0
set head off
spool '&&1'
select /*csv*/ name||','||TO_CHAR(value,'fm999G999G990D00') from my_table;

Explanation why Oracle can not do human readable formatting for decimal numbers:
Require a Number Format Mask to show leading zeros on decimals
Number format (trailing zeros)

No comments:

Post a Comment