• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Yet Another Way to Build CSV Files

    February 2, 2005 Hey, Ted

    I know offshoring gets a lot of blame for the decline in programmer positions these days, but there’s another culprit that I never hear about: the vast array of reporting and data-mining tools available to users.

    Twenty years ago a programmer had to write code to build a report program. Now users create their own reports with Microsoft Excel. In my shop, that means we have lots of programs that create CSV (comma-separated values) files. Let me add a simple but effective technique to the Copy to Import File (CPYTOIMPF) command and the plethora of third-party products that create files in CSV (and other) formats.

    SQL presents an easy way to create CSV files. Use the CHAR function to convert numeric fields to alpha format. SQL puts in the necessary minus signs and decimal points. Concatenate all the fields together to get one big comma-delimited output field.

    The following SQL command is an example of the technique. I use Qshell to retrieve the data and load it into a file in the Integrated File System (IFS).

    db2 "SELECT char(CUSNUM)||','||LSTNAM||','||INIT||','||
        CITY||','||STATE||','||char(baldue) from qiws.qcustcdt"
        | sed -n '/,/p' >> custdata.CSV
    

    The CSV file looks like this:

       938472  ,Henning ,G K,Dallas,TX,37.00
       839283  ,Jones   ,B D,Clay  ,NY,500.00
       392859  ,Vine    ,S S,Broton,VT,439.00
       938485  ,Johnson ,J A,Helen ,GA,3987.50
       397267  ,Tyron   ,W E,Hector,NY,.00
       389572  ,Stevens ,K L,Denver,CO,58.75
       846283  ,Alison  ,J S,Isle  ,MN,10.00
       475938  ,Doe     ,J W,Sutter,CA,250.00
       693829  ,Thomas  ,A N,Casper,WY,.00
       593029  ,Williams,E D,Dallas,TX,25.00
       192837  ,Lee     ,F L,Hector,NY,489.50
       583990  ,Abraham ,M T,Isle  ,MN,500.00
    

    It isn’t necessary to run SQL under Qshell, of course, but doing so sure makes it easy to build an IFS file.

    –Just call me “Joe”


    If you need to get rid of extra blanks, use the TRIM function. If a character field might contain commas, use the TRANSLATE function to convert them to something else.

    –Ted

    Share this:

    • Share on Reddit (Opens in new window) Reddit
    • Share on Facebook (Opens in new window) Facebook
    • Share on LinkedIn (Opens in new window) LinkedIn
    • Share on X (Opens in new window) X
    • Email a link to a friend (Opens in new window) Email

    Tags:

    Sponsored by
    Maxava

    Migrate IBM i with Confidence

    Maxava Migrate LiveTM minimizes disruption with seamless transitions. Upgrading to Power11, or cloud hosted system, Maxava has you covered!

    MIGRATE NOW

    Share this:

    • Share on Reddit (Opens in new window) Reddit
    • Share on Facebook (Opens in new window) Facebook
    • Share on LinkedIn (Opens in new window) LinkedIn
    • Share on X (Opens in new window) X
    • Email a link to a friend (Opens in new window) Email

    Repeated Characters in SQL Admin Alert: Things to Check After Upgrading OS/400 V5R1 to V5R3

    Leave a ReplyCancel reply

Volume 5, Number 5 -- February 2, 2005
THIS ISSUE
SPONSORED BY:

WorksRight Software
Advanced Systems Concepts
COMMON

Table of Contents

  • Repeated Characters in SQL
  • Yet Another Way to Build CSV Files
  • Admin Alert: Things to Check After Upgrading OS/400 V5R1 to V5R3

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • IBM i Delivers New Development Tooling With 2026 Fall Tech Refreshes
  • Big Blue Says AI Is Not Behind The Big Uptick In IBM i Security Alerts
  • Guru: Web Service With Pagination
  • The GenAI Revolution And Product Shortages Send IT Budgets Skyward
  • IBM i PTF Guide, Volume 28, Number 40
  • IBM i PTF Guide, Volume 28, Number 41
  • Bob Code Assistant Gets Better Understanding Of IBM i Applications
  • IBM i In The Cloud And The Case For Reversing Replication
  • Guru: From Hiking Boots To Long-Term Memory
  • Inside IBM’s New PowerHA Cloud Offering, Full System Manager

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle