CSV export issues
Timothy R Barr
tim.barr at evalsvs.com
Thu Jul 6 06:48:14 PDT 2017
I agree with Scott Nelson. I refuse to to CSV because I can't guarantee
what the users have entered will work unless xlating the quotes and commas
during the export - and then I am modifying their data.
I prefer tab delimited.
export ascii rpt= /usr/tmp/report.txt r=\n f=\t
or
export ascii out=(filename) r=\n f=\t
Excel and SQL recognize it naturally, and a TAB cannot be entered by a user
into a data field.
If some recipient can't handle it, I will sometimes convert to Excel for
them.
PS. I'll tolerate a pipe delimiter (as used in Informix), since it is
unlikely to be entered by a user.
Timothy R Barr, CPE, VP/CTO
Evaluator Services and Technology, Inc
1801 Oberlin Rd, Suite 204
Middletown, PA 17057
717-985-1122 x 1131
855-569-6022 (Tollfree Middletown)
855-500-6022 (Tollfree Fax)
tim.barr at evalsvs.com
>
>
> ---------- Forwarded message ----------
> From: Scott Nelson <scott at logicdatasystems.com>
> To: Jean-Pierre Radley <appl at jpr.com>, "filepro-list at lists.celestial.com"
> <Filepro-list at lists.celestial.com>
> Cc:
> Bcc:
> Date: Sun, 2 Jul 2017 12:05:18 -0500
> Subject: Re: CSV export issues
> I stopped doing CSV because of the issues with commas and quotes. I do
> Pipe delimited files, save them as .pi or .txt and give the customer
> instructions on importing into excel.
>
> export word rpt=(f) is the correct sytax, but you don't want @td since it
> has slash in it.
>
>
>
> On 07/01/2017 03:44 PM, Jean-Pierre Radley via Filepro-list wrote:
>
>> I am trying to generate a spreadsheet using
>>
>> export word rpt= /usr/tmp/report.csv
>>
>> The filePro file is an inventory of metal parts. In the Description
>> field the single or double quote may show up as the conventional
>> abbreviation for feet or inches, e.g.:
>>
>> 6" DIAL CALIPER WHITE FACE
>>
>> Whenever this happens, processing goes nuts at the quote-mark: all the
>> fields of every record up to the next one with a quote-mark are output
>> on the same line of the CSV file. If there are over 600 records between
>> two fP records with a quote mark, the CSV file will have a line with
>> over 600 columns.
>>
>> I can of course use an edit to remove the quote-marks, but is there some
>> way to use some escape mechanism to preserve them in the CSV?
>>
>> Another minor annoyance: I wanted to define the output file from data
>> in the filePro input. Say field 1 is the customer number:
>>
>> f = 1 { "-" { @td; export word rpt=f
>> or
>> f = 1 { "-" { @td; export word rpt=(f)
>>
>> Neither works.
>>
>>
>
>
>
>
> ---------- Forwarded message ----------
> From: Bill Campbell <bill at celestial.com>
> To: filepro-list at lists.celestial.com
> Cc:
> Bcc:
> Date: Sun, 2 Jul 2017 12:58:08 -0700
> Subject: Re: CSV export issues
> On Sat, Jul 01, 2017, Jean-Pierre Radley via Filepro-list wrote:
> >I am trying to generate a spreadsheet using
> >
> > export word rpt= /usr/tmp/report.csv
> >
> As others have pointed out, CSV files have problems dealing with
> quotes, and not all CSVs are created equally depending on the
> spreadsheet program.
>
> I've found that using tab-delimiters instead of commas tends to
> have fewer issues as tabs aren't generally found in ascii fields,
> and are readily accepted by spreadsheets.
>
> My most frequent use is exporting data from my Unify based
> accounting software to LibreOffice Calc from SCO OpenServer to
> CentOS Linux where I avoid the issue by using a MySQL table as
> the transfer agent avoiding any issues with data types.
>
> Bill
> --
> INTERNET: bill at celestial.com Bill Campbell; Celestial Software LLC
> URL: http://www.celestial.com/ PO Box 820; 6641 E. Mercer Way
> Voice: (206) 236-1676 Mercer Island, WA 98040-0820
> Fax: (206) 232-9186 Skype: jwccsllc (206) 855-5792
>
> Foreign aid might be defined as a transfer from poor people in rich
> countries to rich people in poor countries -- Douglas Casey
>
>
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> http://mailman.celestial.com/mailman/listinfo/filepro-list
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.celestial.com/pipermail/filepro-list/attachments/20170706/c85410bf/attachment.html>
More information about the Filepro-list
mailing list