Using IBM System i Access can I replace zeros with blanks in a downloaded CSV file?

5 pts.
Tags:
AS/400
CSV
CSV file format
iSeries
Hi,

I have a simple file transfer from my iSeries(AS/400) to a CSV file.  I have a numeric field with 2 decimal places.  The file is sent to an outside agency.  They need zero values to contain blanks.  Is there a way to do this?

Thanks for your help.

 



Software/Hardware used:
iSeries AS/400 iAccess

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • philpl1jb
    I would guess that you would have to 1. Create a similar file but with Alpha fields 2. Create a program to format and write the data into the second file 3. Send or pull the new file Phil
    49,435 pointsBadges:
    report
  • TomLiotta
    As Phil describes, it's obviously possible by combining the transfer with a couple additional steps before or after the transfer. I'm not aware of a direct way of doing it in an iSeries Access Data Transfer. (There might be a way that I don't know.) Regardless, I wouldn't do it with iSeries Access in the first place if it couldn't be done in an encapsulated way. (I almost certainly wouldn't do it with iA transfers anyway.) My first try would probably be with Qshell's db2 utility. I'd code a SELECT statement that concatenated the field values into a single string, complete with commas, quote marks and whatever other customizations were needed. I'd have complete control over each character in every row while using a well known interface -- SQL SELECT. The output would be to a directory shared out to Windows so the PC could pick it up. Tom
    125,585 pointsBadges:
    report
  • philpl1jb
    We've made good suggestions but we don't know what tools BCF can use, we also don't know the frequency or volumn. Heck, if it's small and infrequent, he might edit his results in notepad and change the zeros to blanks. Phil
    49,435 pointsBadges:
    report
  • Craig Hatmaker
    An alternative uses Excel and the ODBC driver you already have with Client Access. You can use them to directly load the data into Excel from within Excel, apply any formatting needed (including making zero values disappear), and save results as a .CSV. The whole process can be automated down to, literally, a click of a button. Here is an overview of how to use MSQuery - a free tool in every copy of Excel - to find, select, and pull data from any database - including the iSeries DB2.: http://itknowledgeexchange.techtarget.com/beyond-excel/manually-linking-to-data-ms-query/ This shows you how to link the iSeries ODBC as a data source to Excel: http://itknowledgeexchange.techtarget.com/beyond-excel/data-sources/ This takes you more step by step through using MSQuery from Excel: http://itknowledgeexchange.techtarget.com/beyond-excel/creating-a-query/ If you have Excel and Client Access, you have everything you need. There is absolutely nothing else to buy. Continue with the blog if you want to learn how to automate the process more. Best wishes.
    1,705 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following