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}

Script that do extraction to file by sqlplus:

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


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