jrepl.bat help for Oracle sqlplus output

[somasatish]

Hi,

Need help to replace leading and trailing spaces on to the csv file spooled out of Oracle Sqlplus. Unable to do it.

Below is the sample data

Code: Select all

ASSET       ,PARENT      ,SERIAL                                                          ,ASSET                                                           ,LOCATION     ,DESCRIPTION                                                                                         ,VENDOR      ,FAILUREC,MANUFACTURER,PURCHASEPRICE,REPLACECOST,INSTALLDATE        ,WARRANTYEXPDATE    , TOTALCOST,   YTDCOST,BUDGETCOST,CALNUM  , ISRUNNING,ITEMNUM                       ,UNCHARGEDCOST,TOTUNCHARGEDCOST,TOTDOWNTIME,STATUSDATE         ,CHANGEDATE         ,
12345       ,            ,2011124   7                                                     ,                                                                ,2500422      ,XFMR 1.8 MVA                                                                                        ,            ,        ,0000202695  ,            0,          0,                   ,                   ,         0,         0,         0,        ,         1,                              ,            0,               0,          0,2015-04-30 05:28:40,2015-04-30 05:28:40,
12345       ,            ,2011124   1                                                     ,                                                                ,2500448      ,XFMR 1.8 MVA                                                                                        ,            ,        ,0000202695  ,            0,          0,                   ,                   ,         0,         0,         0,        ,         1,                              ,            0,               0,          0,2015-04-30 05:33:03,2015-04-30 05:33:03,
12345       ,            ,52-637-0  2-29                                                  ,                                                                ,2500522      ,XFMR, 500 kVA, Padmount                                                                             ,            ,        ,0000239070  ,            0,          0,                   ,                   ,         0,         0,         0,        ,         1,                              ,            0,               0,          0,2015-08-07 03:10:30,2015-08-07 03:17:14,

Requesting somebody to please help urgently.

thanks
Satish

[Squashman]

I see some issues with your data.

The serial field has some extra spaces in between the data in the field.

The Description field has Commas in the data and it is not quote surrounded to protect them.

[somasatish]

That's right, we don't want to remove the comma's in between the text, Wanted to just remove leading and trailing spaces in between the data columns.

Not to remove text in between text's

[dbenham]

It is impossible to parse out columns using commas because some of your Description fields in the middle contain commas - there is no way to tell if the comma represents a column delimiter or a comma literal. Your only recourse is to parse out the fields positionally, which is possible because your output is fixed width.

I solve the problem by supplying a fix() JScript routing to trim leading and trailing spaces,double all existing quotes, add enclosing quotes,and optionally add trailing comma. It never hurts to quote all csv columns. Your data doesn't have embedded quotes, but I wrote the code to deal with them anyway.

I use batch to dynamically build the find and repl terms, and the /V options gets all the values from variables.

My code below assumes the source file is "output.txt" and the corrected file is "output.csv". Obviously you need to adjust to fit your needs.

Code: Select all

@echo off
setlocal enableDelayedExpansion
set "find=^"
set "repl=$txt="
set N=0
for %%N in (12 12 64 64 13 100 12 8 12 13 11 19 19 10 10 10 8 10 30 13 16 11 19 19) do (
  set /a N+=1
  set "find=!find!(.{%%N}),"
  if !N!==24 (
    set "repl=!repl!fix($!N!,0)"
  ) else (
    set "repl=!repl!fix($!N!,1)+"
  )
)
set "fix=function fix(str,comma) { return '\x22'+str.replace(/\x22/g,'\x22\x22').replace(/^ +| +$/g,'')+(comma?'\x22,':'\x22'); }"
call jrepl find repl /v /jq /jbeg fix /f output.txt /o output.csv

The above a fair amount of work to fix the output, especially if you have many and/or constantly changing csv formats.

It is just as easy to configure your SELECT statement to generate a proper csv in the first place. I used to do it all the time. Just have your SELECT statement return a single column that has all of your trimmed and quoted values for a row concatenated into a single string. Don't foret to double quote literals in your output. If you want column headers, then add an additional SELECT to output the header string before you SELECT your data. Don't forget to use SET PAGESIZE 0 to get continuous output without breaks or column headings, and SET TRIMSPOOL ON to prevent trailing spaces at the end of each row.


Dave Benham

[dbenham]

You also might want to check out Oracle's new command line processor - SQLcl

Included is the ability to automatically SELECT data into csv format by using set sqlformat csv


Dave Benham

[somasatish]

Hi,

Thanks for the response. How about if I change my delimiter to |(Pipe) instead of ,(comma).

Will that make our life easier.

thanks
Satish

[Squashman]

Hi,

Thanks for the response. How about if I change my delimiter to |(Pipe) instead of ,(comma).

Will that make our life easier.

thanks
Satish

Usually does. Our company has been using the | as a standard for over a decade. But I be you make your life even easier if you just export your query in a better format as Dave has pointed out.

[SIMMS7400]

Whether it's Oracle or Microsoft, think about incorporating the following syntax when spooling to a file:

Select DOS || ',' || DOS from DOS_TIPS_FORUMS;

You could also think about doing :

select rtrim(col1), rtrim(col2), ...
from my_table;

Also, ff leveraging SQLPLUS or SQLCMD, use the -S[ILENT] option as well.

[dbenham]

The final SELECT statement will typically be more complex than that. Non string based columns should be converted to strings, strings that may contain commas must be quoted, and quote literals must be doubled. Use format strings to specify the desired date/time format. Use a second SELECT to get the column headers.

For example (using bogus column names):

Code: Select all

select 'NUM1_COL,NUM2_COL,STR1_COL,STR2_COL,DATE_COL,TIMESTAMP_COL' from dual
union all
select to_char(num1_col) || ',' || to_char(num2_col) || ',"' || replace(str1_col,'"','""') || '","' 
       || replace(str2_col,'"','""')||'",'||to_char(date_col,'yyyy-mm-dd')||','||to_char(timestamp_col,'yyyy-mm-dd hh24:mi:ss.ff')
  from someTable
 ;

Dave Benham