CSV Archives - IT Jungle

Guru: Use SQL To Replace Reports

June 14, 2021 Ted Holt

Does anybody write report programs anymore? If so, why? Reports are so twentieth-century. The people we serve want spreadsheets, Web pages, cell-phone apps and other modern conveniences. What we need is an easy way to make our report programs write the same information to modern interfaces without having to perform major surgery on them.

Some 10 years or so ago, I built a scripting language to enable me to reformat spooled files into stream files in CSV format. I called my utility BASS. It was, and continues to be, a successful project.

But BASS is not the only way …
Read more
Guru: Date Format Confusion

May 10, 2021 Ted Holt

Hey, Ted:

I use the Copy to Import File (CPYTOIMPF) command to build a CSV file to be sent to a MySQL system. My colleagues are unable to import the file because of the format of a timestamp field. CPYTOIMPF puts a hyphen between the date and time (e.g. 2021-05-10-04.36.24.849555), but the other system wants a space between them. Other than writing a custom program do you know of another way to change the format of the timestamp output?

— Richard

You won’t need a custom program, Richard. There’s another way to handle this requirement.

IBM has already very thoughtfully …
Read more
Kisco Locks Down IBM i Report Distribution

October 28, 2020 Alex Woodie

Users will gain more control over how their IBM i spool files are distributed and displayed, with a new release of WebReport/i, which is Kisco Information Systems’ spool file utility automatically reformatting and distributing spool files.

WebReport/i can reformat IBM i spool files in a variety of different ways. It can change them into HTML, RTF, PDF, XLS, XLSX, TIF or CSV formats, and it can also distribute them via HTTP, FTP, email, Google Drive, Dropbox, and even fax.

With Release 14, which was unveiled earlier this month, Kisco has shored up the security of the product. Specifically, when posting …
Read more
Guru: Scalar Use of LISTAGG

August 24, 2020 Ted Holt

The SQL LISTAGG function is as handy as a pocket. Only recently I used it to build a string of comma-separated values (CSV) to populate a drop-down box. It sure beats a cursor and a loop. I’ve noticed in my reading that LISTAGG can be used for both aggregate and scalar purposes under Oracle. Db2 doesn’t support the scalar use, but I found another way to do the same thing.

To see what I’m talking about, check out example 7 in the article Oracle LISTAGG Function Usage, Tips & Examples, by Ben Brumm. I recommend studying the entire article; …
Read more
Guru Classic: A Bevy Of BIFs — Updates

February 5, 2020 Jon Paris

In my previous tip I returned to the “Bevy” series to tell you about the latest addition to the family: %ScanRpl. Shortly after completing that tip I realized that there have been a number of more recent BIFs such as %SubArr and some enhancements to existing BIFs like %Trimx that seem to have escaped people’s notice. In this tip I attempt to fill those omissions.

%Trim: Specify Characters to Trim

Let’s start with a BIF that hopefully you are all familiar with: %Trim. Before going any further I should point out that when I say %Trim, I am referring to …
Read more
Latest TRs For IBM i Now Available

November 20, 2019 Alex Woodie

IBM last week delivered the latest IBM i operating system Technology Refreshes, giving good IBM i boys and girls around the world early Christmas presents in areas like programing, operations, database, and security. Here’s a quick recap of the new stuff in IBM i 7.4 TR1 and 7.3 TR7.

There were several general enhancements with IBM i 7.4 TR1 and 7.3 TR7. For starters, administrators gained more control over resources available to the Integrated Web Server (the one powered by Apache) with the new capability to install it the Web server in a user-specified sub-system. Admins also get new functions …
Read more
Guru Classic: The Efficiency of Varying Length Character Variables

August 14, 2019 Jon Paris

Remember the bad old days when dinosaurs still roamed the earth and the only way to build strings in RPG involved playing silly games with arrays? Or worse still, obscure combinations of MOVE operations? Thankfully those days are far behind us — although sadly there are still a few RPG/400 dinosaurs coding away!

RPG IV introduced many powerful new string handling options, such as the %TRIMx family of BIFs, but even now there are capabilities in the language that few programmers fully exploit. One of my favorites is variable length fields. This lack of familiarity made this tip an obvious …
Read more
Guru: Creating Excel Spreadsheets With Python

April 29, 2019 Mike Larsen

Over the past several years, I’ve seen a growing demand to provide business data in Excel. Some shops are content to receive their data in .csv format and then manually apply formatting to make it presentable. Wouldn’t it be better if we could provide a spreadsheet that is already formatted?

In my opinion, the answer is yes. Most recently, I’ve been using PHP to accomplish this task. PHP works great for me, but I always like to have different options from which to choose. This is important to me because some shops may not have PHP installed. Based on that …
Read more
Guru: Open Access To The Rescue

February 11, 2019 Jon Paris

Recently I came upon a couple of questions on internet lists asking for an easy method to create flat files in the IFS. Not CSV files, but rather simple text files where individual data items are in fixed character positions. Various somewhat convoluted solutions were offered to the requestors, but to me this problem shouted “Open Access!”

So, I set about quickly building an Open Access (OA) handler to perform the task. I find OA really useful for this kind of job because, once the handler is written, creating these kinds of files in the future become a trivial task, …
Read more
Guru: A Bevy of BIFs – %Dec to the Rescue

August 1, 2018 Susan Gantner

Author’s Note: This tip was first published back in 2009. I’ve updated the code examples here to use free format declarations. In addition I’ve added the potential use of %ScanRpl, which didn’t exist at the original publication date.

More and more in RPG applications these days, it seems we need to process data that comes from “the dark side.” Translation: from a non-i system. This data could be coming from a browser screen via an RPG CGI program, from a CSV (comma-separated values) flat file, from an XML or JSON document, or myriad other ways. One thing these dark sources …
Read more

Content archive

Recent Posts

Subscribe

Pages

Search

%Trim: Specify Characters to Trim

Content archive

Recent Posts

Subscribe

Pages

Search