• 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
    PERFSCAN

    Revolutionary Performance Management Software

    At Greymine, we recognize there is a void in the IT world for a dedicated performance management company and also for a performance management tool that’s modern, easy to use, and doesn’t cost an arm and a leg. That’s why we created PERFSCAN.

    PERFSCAN is designed to make your job easier. With revolutionary technology, an easy-to-read report and graphics engine, and real time monitoring, tasks that used to take days can now take minutes. This means you will know your system better and will be able to provide better service to your customers.

    OUR FEATURES

    PERFSCAN is full of robust features that don’t require you to take a three-day class in order to use the product effectively.

    Customizable Performance Reporting

    Whether you are troubleshooting a major system problem or simply creating a monthly report, PERFSCAN lets you select any combination of desired performance metrics (CPU, Disk, and Memory).

    User Defined Performance Guidelines

    No matter if you are a managed service provider managing complex systems in the cloud or a customer analyzing your on-premises solution, PERFSCAN gives you the flexibility to define all mission critical guidelines how they need to be.

    Understanding The Impact Of Change

    Tired of all the finger pointing when performance is suffering? PERFSCAN’s innovative What’s Changed and Period vs. Period analysis creates a culture of proof by correlating known environmental changes with system performance metrics.

    Comprehensive Executive Summary

    Creating performance graphs is easy. Understanding what they mean is another thing. With one mouse click, PERFSCAN includes an easy-to-understand executive summary for each core metric analyzed.

    Combined Real-Time Monitor And Performance Analysis Tool

    With PERFSCAN’s combined built in enterprise real-time monitor and historical performance analysis capability, you will always know how your mission-critical systems are performing.

    Cloud Performance Reporting Is Easy

    Managing performance for production systems in the cloud can be a black hole to many system administrators. The good news is PERFSCAN analyzes all core metrics regardless of the location. That’s why MSPs and customers love PERFSCAN.

    Detailed Job Analysis

    PERFSCAN shows detailed top job analysis for any desired period. All metrics are displayed in two ways: Traditional Report and Percentage Breakdown Pie Chart. This toggle capability instantly shows the jobs using the most system resources.

    Save Report Capability

    Your boss lost the report you gave to him on Friday. Now what do you do? With PERFSCAN’s save report capability, any report can be retrieved in a matter of seconds.

    Professional PDF Reporting With Branding

    Creating professional looking reports for your customers has never been easier with PERFSCAN. Branding for our partners and service provider customers is easy with PERFSCAN.

    Check it out at perfscan.com

    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

  • Power10 Boosts NVM-Express Flash Performance
  • Fortra Completes Postmortem Of GoAnywhere Vulnerability
  • Guru: Binding Directory Entries
  • How Does Your Infrastructure Spending Stack Up To The World?
  • IBM i PTF Guide, Volume 25, Number 22
  • Fortra Issues 20th State of IBM i Security Report
  • FNTS Launches Managed Services for Power Servers in IBM Cloud
  • Total LTO Shipped Capacity Up Slightly in 2022
  • Four Hundred Monitor, May 24
  • Update On Critical Security Vulnerability In PowerVM

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 © 2023 IT Jungle