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

Question

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"

Charles · Accepted Answer · 2016-08-18 12:44:29Z

2

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...

answered Aug 18, 2016 at 12:44

Charles

24.2k3 gold badges23 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sooze Over a year ago

Thanks SO much! I knew there had to be a setting! NOW. if we could just TRIM off leading zeros in a text field - My users would be off & running & I'd be making progress on other things!

Charles Over a year ago

@Sooze, use TRIM(LEADING '0' FROM name)

B.Muthamizhselvi Over a year ago

@sooze, I want to bulk export export using IBM iseries. Is ther any possibility

Christoff Erasmus · Accepted Answer · 2016-08-18 07:48:14Z

1

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).

answered Aug 18, 2016 at 7:48

Christoff Erasmus

9851 gold badge11 silver badges28 bronze badges

6 Comments

jmarkmurphy Over a year ago

I don't think the database file that is being exported is in the IFS, thus the need to export it.

Christoff Erasmus Over a year ago

The Table file is not in the IFS, the newly created csv which is a Stearm file should be generated into the IFS.

danny117 Over a year ago

I would do it that way too. No sense hiding business logic inside a data transfer.

Sooze Over a year ago

Thanks ya'll!! All I really needed was the data transfer info! All of the other programming has been in place for a while. We have multiple banks that have required flat files - fixed length. A newer vendor wanted same data in .csv. THANKS!

Sooze Over a year ago

ps. You are correct the data file DID NOT reside on the IFS and I didn't really want to involve the IFS and put it there, either.

|

Collectives™ on Stack Overflow

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

2 Answers 2

3 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related