FW: PROTIP: Importing data into filepro

[Brian K. White]

Yes it's possible to trip up import word if you have commas next to 
double-quotes inside of fields. You can pre-process with sed.

As Jay pointed out though, the real problem is that the format specifies 
that the quotes are optional.

This means that  ",  and  ,"  are valid field delimiters.

"field 1",field 2,"field 3"

You can't simply sed those into | unilaterally, because that would break:

"field 1","8', 6", beam","field 3"
That's:
1: field 1
2: 8', 6", beam
3: field 3

If the quotes were always required then this is no problem, because then 
the only 3 possible field seps are
^" - begin-line+"
"$ - "+end-line
and
","

And anything else, including embedded ", is safe.
fp will still trip on it, but if YOU know that the other side is sending 
quotes always, then you can safely pre-process with sed.

$: cat test.csv
"field 1","8', 6", beam","field "3""
$: sed 's/^"/|/;s/"$/|/;s/","/|/g' <test.csv
|field 1|8', 6", beam|field "3"|
$:


Also, the sender may also be doing the Excel quazi-standard of 
double-double-quotes, where you double-up any double-quotes that are 
meant to be data instead of field delimiter. In that case the csv will 
look like:

$: cat test.csv
"field 1","8', 6"", beam","field ""3"""

So add another sed command *after* the "," have been converted to | so 
that those "'s from "," are removed and can't be confused, ie so that 
the 3 consecutive " in ""3""" is handled correctly:

$: sed 's/^"/|/;s/"$/|/;s/","/|/g' <test.csv
|field 1|8', 6"", beam|field ""3""|

$: sed 's/^"/|/;s/"$/|/;s/","/|/g;s/""/"/g' <test.csv
|field 1|8', 6", beam|field "3"|


Then you can safely use
import ascii csv = (@pm) f=|

You can only do so much in the way of accepting bad data and dealing 
with it on your end. If the other side wants this to be automated and 
wants it to work, then they do have to adhere to some minimal rules 
themselves.

Otherwise it's always a manual job you have to charge hours for.

Put it to them like this, they will always have to pay consultants rates 
for data-entry.

Even if they are getting these files from some other 3rd party they 
don't want to annoy, it's still cheaper for them to have one of their 
own people clean the data up and re-export manually before giving it to you.

-- 
bkw


On 3/11/2013 1:14 PM, Scott Walker wrote:
>
>
> -----Original Message-----
> From: Scott Walker [mailto:ScottWalker at RAMSystemsCorp.com]
> Sent: Monday, March 11, 2013 1:04 PM
> To: 'Jay Ashworth'
> Subject: RE: PROTIP: Importing data into filepro
>
> Jay,
>
> Thanks for the input.
>
> I agree with you. If I ever have control I use "|".
>
> But, this is real world, and I often have no control, no input, no
> information, no common sense, no time, and no patience.
>
> Customer just gets a spreadsheet from somewhere and hand it to me and says
> "Can you make this work?".
>
> Regards,
>
> Scott
>
>> -----Original Message-----
>> From: filepro-list-
>> bounces+scottwalker=ramsystemscorp.com at lists.celestial.com
>> [mailto:filepro-list-
>> bounces+scottwalker=ramsystemscorp.com at lists.celestial.com] On Behalf
>> bounces+Of
>> Jay Ashworth
>> Sent: Monday, March 11, 2013 12:13 PM
>> To: filepro-list at lists.celestial.com
>> Subject: PROTIP: Importing data into filepro
>>
>> If you have any control at all over the format of incoming variable-
>> length data that you need to import into filePro, you should specify
>>
>> |
>>
>> as your field separator, and no added commas at all.
>>
>> While it's an ASCII character, it's not a character that anyone who
>> isn't a programmer ever types anywhere; I have been using it for this
>> for literally three decades, and it has never falsed *once* on me.  Ever.
>>
>> Just sayin'...
>>
>> Cheers,
>> -- jra
>> --
>> Jay R. Ashworth                  Baylink
>> jra at baylink.com
>> Designer                     The Things I Think                       RFC
>> 2100
>> Ashworth & Associates     http://baylink.pitas.com         2000 Land
>> Rover DII
>> St Petersburg FL USA               #natog                      +1 727 647
>> 1274
>> _______________________________________________
>> Filepro-list mailing list
>> Filepro-list at lists.celestial.com
>> Subscribe/Unsubscribe/Subscription Changes
>> http://mailman.celestial.com/mailman/listinfo/filepro-list
>
>
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> Subscribe/Unsubscribe/Subscription Changes
> http://mailman.celestial.com/mailman/listinfo/filepro-list
>