Data Import

[Nancy Palmquist]

I do imports for all kinds of things and the worst character people can 
put into a data field is the CRLF.

There is no way around this at all.

I get XML data and between tags, I will get fields that include CRLF.  
As you all note, the CSV files are also riddled with issues because the 
source is not as controlled as we would like.  One advantage of XML is 
that it does not need any CRLF codes to work, so you can strip them out 
without causing any data loss or other issues.  The other issue is any 
characters that would be changed by TOHTML() function.  There is no 
reverse function so each code must be searched out and reversed to the 
appropriate code manually. (This is a pain but necessary)

People put quotes - inside of quoted strings.  They put CRLF inside of 
quoted strings.  When CRLF is also the record delimiter this ruins 
everything.  FilePro should understand the difference but it does not.

I have had files so bad that the only way to read them is byte by byte.

It is important to look at a sample or small subset of samples and see 
exactly what the data will include to try to figure out what to do.

Always use a HEX editor to do this looking.  It is more informative.

I will resort to OPEN() READLINE() or READ() when IMPORT is just not 
working to load a file of data.  The rules are different.
Good luck Ken.

Nancy



On 3/12/2013 8:43 AM, scooter6 at gmail.com wrote:
>    Or you can run a simple sed script on the file first.....to remove the "
>    sed -e 's/"//g'
>
>
>
> On Mon, Mar 11, 2013 at 10:45 AM, Kenneth Brody <kenbrody at spamcop.net>wrote:
>
>> On 3/10/2013 10:05 PM, Scott Walker wrote:
>>> Ken,
>>>
>>> Thanks for the input.  It definitely handles the field surrounded by the
>> "
>>> characters.
>> FYI - The other option is to use the O= and C= flags.  ("Import word" just
>> sets the defaults to one of the common formats, CSV with quotes around
>> [some] values.)
>>
>>> It definitely does not handle the field with the " in the actual data.
>> Can you give a specific example that "doesn't work"?
>>
>>> This is what I always seem to find with import.  One way handles some
>> issues
>>> but not others.  Change it...you fix some problems, make others.
>>>
>>> I don't see this as a problem with fp import command.  I think the data I
>>> get is FUBAR.
>>>
>>> Thanks again for taking the time.
>>>
>>> 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
>> Of
>>>> Ken Cole
>>>> Sent: Sunday, March 10, 2013 9:09 PM
>>>> To: Scott Walker
>>>> Cc: filePro Mailing List
>>>> Subject: Re: Data Import
>>>>
>>>> "Import word" does it automatically.
>>>>
>>>> Ken
>>>>
>>>> On Monday, 11 March 2013, Scott Walker wrote:
>>>>
>>>>> I am importing from an .csv file.
>>>>>
>>>>> Data is separated by commas.
>>>>>
>>>>> If a field contains a comma in it the field is enclosed in "
>>>>>
>>>>> Example:
>>>>>
>>>>> Scott, Walker,"112 Main Street, Suite 12",Charlotte,NC,28212
>>>>>
>>>>>
>>>>> What's the best way to import into fp so I get the 112 Main Street,
>>>>> Suite
>>>>> 12
>>>>> in the field without the ".
>>>>>
>>>>> Sorry for this basic question, but imports always seem to befuddle me.
>>>>>
>>>>> Regards,
>>>>> Scott
>>>>>
>>>>>
>>>>> Scott Walker
>>>>> RAM Systems Corp
>>>>> (704) 896-6549
>>>>> Scott.Walker at RAMSystemsCorp.com
>>>>>
>>>>> www.RAMSystemsCorp.com
>>>>>
>>>>>
>>>>>
>>>>> _______________________________________________
>>>>> Filepro-list mailing list
>>>>> Filepro-list at lists.celestial.com <javascript:;>
>>>>> Subscribe/Unsubscribe/Subscription Changes
>>>>> http://mailman.celestial.com/mailman/listinfo/filepro-list
>>>>>
>>>> -------------- next part --------------
>>>> An HTML attachment was scrubbed...
>>>> URL: http://mailman.celestial.com/pipermail/filepro-
>>>> list/attachments/20130311/021ace82/attachment.html
>>>> _______________________________________________
>>>> 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
>>>
>>>
>> --
>> Kenneth Brody
>> _______________________________________________
>> Filepro-list mailing list
>> Filepro-list at lists.celestial.com
>> Subscribe/Unsubscribe/Subscription Changes
>> http://mailman.celestial.com/mailman/listinfo/filepro-list
>>
> -------------- next part --------------
> An HTML attachment was scrubbed...
> URL: http://mailman.celestial.com/pipermail/filepro-list/attachments/20130312/8f9aab15/attachment.html
> _______________________________________________
> Filepro-list mailing list
> Filepro-list at lists.celestial.com
> Subscribe/Unsubscribe/Subscription Changes
> http://mailman.celestial.com/mailman/listinfo/filepro-list
>

-- 
Nancy Palmquist         MOS & filePro Training Available
Virtual Software Systems    Web Based Training and Consulting
PHONE: (412) 835-9417           Web site:  http://www.vss3.com