Re: Quoting text strings for SCV output

[Don Brown <DBrown@xxxxxxxxxx>]

Hello Jon

Hopefully the following will answer your question ...

We allow the users to create an output format.

Simply this is a list of available fields with descriptions a user can 
clearly understand.  They can choose to create a new extract format and 
select the fields they want in the output and sequence the fields in the 
order required. (These fields come from multiple tables and may be a 
calculated values)

When they then run the extract they select the required format and the 
processing will create an excel spreadsheet with the requested data in the 
format they have created/selected.  All good we have a spreadsheet.

We have now been advised they need a format in csv format with all 
alphanumeric fields enclosed in double quotes for an external entity.

I thought an opportunity to use Open Access.  But then looking at the 
example and thinking about it a bit more writing to excel allows me to 
write any cell at any time whereas if I am writing out to a file I need to 
have the fields in the correct sequence.

My next thought was that instead of having each field defined in the file 
I could just have a single field large enough to support the entire 
record, I would build the string up in the same program that generates the 
Excel and for each new line I would write one record to the Open Access 
file.

The problem when I looked closer at the program that produces the Excel 
was it just passed all the required fields to our utility service program 
which then put the data in the correct column/cell.

So I did not go with Open Access at this stage but did create your sample 
and had it working :-)

Thanks

Don Brown
 



From:   Jon Paris <jon.paris@xxxxxxxxxxxxxx>
To:     Midrange-L Midrange-l <midrange-l@xxxxxxxxxxxx>
Date:   17/11/2016 10:40 AM
Subject:        Re: Quoting text strings for SCV output
Sent by:        "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



You mentioned my CSV Open Access tool earlier Don.

You say the choice of file has to be dynamic - but how is it populated? 
You mentioned user created but that’s all I know.

I can think of ways to adapt the OA tooling but I need to know more about 
the creation of the data.


Jon Paris

www.partner400.com
www.SystemiDeveloper.com

> On Nov 16, 2016, at 6:23 PM, Don Brown <DBrown@xxxxxxxxxx> wrote:
>
> Quite possibly but I have no Python skills, we already have a working 
ILE 
> RPG environment producing excel output and all the logic in that 
program.
> Simply adding the code to also create a ifs csv file in the required 
> format will take a couple of hours.
>
> But I appreciate your response.
>
>
> Don Brown
>
>
>
>
>
>
> From:   Roger Harman <roger.harman@xxxxxxxxxxx>
> To:     Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> Date:   17/11/2016 08:39 AM
> Subject:        Re: Quoting text strings for SCV output
> Sent by:        "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
>
>
>
> Correct me if I am wrong as I've only dipped a toe in the Python 
waters...
> Wouldn't the csv module be a workable solution?
>
> Roger Harman
> COMMON Certified Application Developer - ILE RPG on IBM i on Power
>
> --
>
>
>
>
>
> From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxx> on behalf of Don 
Brown 
> <DBrown@xxxxxxxxxx>
> Sent: Wednesday, November 16, 2016 2:24 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: Quoting text strings for SCV output
>
> Thanks John,
>
> You are totally correct - we have built tools that allow .xls and .xlsx 
> file to be generated with external user defined formatting.
>
> But in this case we have been advised by a third part they will ONLY 
> accept the information in their specific format.
>
> Also as you pointed out we need to know the data we are providing and 
yes 
> we do.  The data is actually imported from a MSSQL server and we parse 
the 
> data and guarantee no quotes, double quotes or imbedded commas.
>
> I am adding a new option to the export definition that provides an 
option 
> to additionally generate a csv file, for that matter I am providing two 
> options so I can allow the user to dictate if double quotes are required 

> around alphanumeric fields or not.
>
> This way we can just build up a text string for each record as the excel 

> file is produced with the appropriate csv delimiters and if the flag is 
> set write the record to the ifs file.  Then both files are attached to 
the 
> email and sent.
>
>
> Would like to thank everyone that replied
>
> Great group with awesome knowledge!!
>
>
> Don Brown
>
>
> --
>
>
>
> From:   John Yeung <gallium.arsenide@xxxxxxxxx>
> To:     Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
> Date:   17/11/2016 06:13 AM
> Subject:        Re: Quoting text strings for SCV output
> Sent by:        "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
>
>
>
> On Wed, Nov 16, 2016 at 12:30 PM, Kevin Adler <kadler@xxxxxxxxxx> wrote:
> > If you were to use Python, you could use the xlsxwriter package and 
> build
> > an actual Excel file and not worry about CSV.
>
> That's normally *my* pat response when I wedge Python into the 
> conversation.
>
> However, the OP was very specific about needing to output CSV, and
> even then, a dialect of CSV that is NOT produced by Excel. He started
> the thread by saying that he's already got POI stuff set up to
> generate Excel files, and that it's not meeting his requirements for
> this particular task.
>
> John Y.
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
>
>
>
> MIDRANGE-L Info Page
> lists.midrange.com
> To unsubscribe from MIDRANGE-L, get a password reminder, or change your 
> subscription options enter your subscription email address:
>
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> midrange.com -- MIDRANGE-L mailing list archive
> archive.midrange.com
> midrange.com MIDRANGE-L mailing list archive
>
>
> Please contact support@xxxxxxxxxxxx for any subscription related 
> questions.
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related 
> questions.
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
> list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related 
> questions.
>
>
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related 
questions.