• 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 a Tab-Delimited File

    April 6, 2005 Hey, Ted

    Thanks for the tip on creating CSV files with SQL. If the data itself contains commas or double quotes, import programs such as Microsoft Excel may get confused on where fields start and end. I think it’s much safer to use a tab (X’05’) as the delimiter and use the PC file extension “txt”.

    Keep up the great work!

    –Chris

    That sounded like a good suggestion to me, so I gave it a whirl, Chris. You were right. I had no trouble opening a tab-delimited file with embedded commas in Microsoft Excel. But that’s not to say I didn’t have any trouble.

    CRTPF FILE(QTEMP/CUSTEXPORT) RCDLEN(512)
    

    Next, I used SQL/400 to load some data into the file.

    INSERT INTO QTEMP/CUSTEXPORT
    SELECT char(CUSNUM)||x'05'||LSTNAM||x'05'||INIT||x'05'||
               CITY||x'05'||STATE||x'05'||char(baldue) from qiws/custody
    

    Then I copied the program-described file to a stream file.

    CPYTOSTMF FROMMBR('/qsys.lib/qtemp.lib/custexport.file/custexport.mbr')
               TOSTMF(custexport.txt) STMFCODPAG(*PCASCII)
    

    I FTP’ed the file to my PC and opened it with Excel, which made me go through an import wizard, but it made the correct guesses. By the way, the file that is generated with the SQL command shown above doesn’t have any commas in the fields, but I did test with data that included commas.

    That seems to work OK. However, when I tried to use Qshell to create the IFS file, I ran into a small problem. Qshell’s db2 command generates header and trailer lines in addition to the data. In the original version of this tip, Joe used the stream editor, sed, to select only the data lines. And how did he do that? By selecting lines that contained commas. Making sed select lines that contain a tab is another matter, since sed doesn’t allow escape sequences for control characters in the command expression. There may be a way to make this work, but I haven’t had enough time to find it yet.

    –Ted

    Click here to contact Ted Holt by e-mail.


    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Rocket Software

    Two Steps Forward, No Steps Back

    For over 35 years, Rocket Software’s solutions have empowered businesses to modernize their infrastructure, unlock data value, and drive transformation – all while ensuring modernization without disruption.

    LEARN MORE

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    NetManage Streamlines Mobile Access to Host Data SecureZIP for iSeries Now Available from PKWARE

    Leave a Reply Cancel reply

Volume 5, Number 14 -- April 6, 2005
THIS ISSUE
SPONSORED BY:

WorksRight Software
Advanced Systems Concepts
Guild Companies

Table of Contents

  • Using FTP to Poll for File
  • Yet Another Way to Build a Tab-Delimited File
  • Admin Alert: Using OS/400 Prestart Jobs for Repetitious Server Processing

Content archive

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

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

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