Re: Quoting text strings for SCV output

[Jon Paris <jon.paris@xxxxxxxxxxxxxx>]

Gotcha - thanks for clarifying.

The only way it would work with OA would be to automagically generate the DDS and create a temp file for the output. Then create a simple program source that specified that file with the CSV handler and copied it.

If you were only creating the excel for the subsequent creation of the CSV that would make for a faster alternative, but since you appear to need the excel anyway and already have that process in place then using OA in this context doesn’t make a lot of sense.

The OA CSV handlers (input and output) were designed to avoid the use of CPYxxIMPF and make it possible to directly read/write the CSV without needing an intermediate database.

 
Jon Paris

www.partner400.com
www.SystemiDeveloper.com

> On Nov 17, 2016, at 3:44 AM, Don Brown <DBrown@xxxxxxxxxx> wrote:
>
> 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.
>
> -- 
> 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.