Search code examples
csvexport-to-csvibm-midrangedata-transfer

When using IBM iSeries Client Access to create a .CSV file from an AS400 db file, can I squeeze out leading blanks?

I have a very simple iseries AS400 DDS file defined in this way

A    R U110055R                  TEXT('POSITIVE PAY') 
A            ACCT55   9A 
A            SERL55        10A 
A            ISSD55        10A 
A            AMT55         13A 
A            NAME55        50A

I am using Data Transfer from System i (IBM I Access for Windows V6R1) to output a csv to the desktop. It will then be used by our banking PC software. I cannot find a setting or a file type that will yield the data without leading blanks. It consistently holds the file sizes of the Database file (without trailing blanks).

I need:

  "192345678","311","07/22/2016","417700","ALICE BROWN CO."
  "192345678","2887","07/22/2016","4124781","BARBIE LLC."
  "192345678","2888","07/22/2016","4766","ROBERT BLUE, INC."
  "192345678","2889","07/22/2016","71521","NANCYS COOKIES, INC"
  "192345678","312","07/22/2016","67041","FRANKS MARKET"

But I get: 

 "192345678","       311","07/22/2016","     11417700","ALICE BROWN CO."
 "192345678","       887","07/22/2016","      4124781","BARBIE LLC." 
 "192345678","       888","07/22/2016","      3204766","ROBERT BLUE, INC." 
 "192345678","       301","07/22/2016","      2971521","NANCY, INC" 
 "192345678","       890","07/22/2016","       967041","FRANKS MARKET"
Solution

  • The Data Options button of the Data Transfer from IBM i application brings up a window where you can explicitly specify the SQL statement used to extract data.

    You can use the SQL TRIM() function there... enter image description here