CODEWITHSUNDEEP
csvexport-to-csvibm-midrangedata-transfer
Sooze
Sooze

Reputation: 27

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"

Upvotes: 1

Views: 1090

Answers (2)

Charles
Charles

Reputation: 23813

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

Upvotes: 2

Christoff Erasmus
Christoff Erasmus

Reputation: 975

Limitation of DDS:

Use of TRIM in DDS

The answer seems to be "You can't." If I were doing this, I'd probably create a SQL VIEW with a DDL statement rather than try to get it done with DDS.

Solution: (W3School) or (IBM Knowledge Center)

CREATE VIEW U110055V1 as
    SELECT  TRIM(ACCT55),
            TRIM(SERL55),
            TRIM(ISSD55),
            TRIM(AMT55),
            TRIM(NAME55)
    FROM U110055
    RCDFMT U110055V1R;

Suggestion:

  • Export the newly created View to a .CSV file (refer here or here)
  • Share the IFS file on the network for easy consistent automated access, (No manual export with i Access required).

Upvotes: 1

Related Questions