Import weirdness - quotes surround data if comma is in the data
Tom Langton
files, saved as text.
If the imported field contains a comma, the imported data field is
surrounded by quotes when imported.
Right now this is an issue with a city, state field that becomes "city,
state" on import.
The client is supplying the data and it can't be practically broken into
two fields.
What's going on here? Is there a quick way to fix? I could do some kind of
"on creation" script in FM, but I'd like to fix at the source, if possible.
FM11 is the database, LP 8.6, on a Mac.
TIA, all
Tom Langton
#############################################################
This message is sent to you because you are subscribed to
the mailing list Lasso
La...@lists.lassosoft.com
To unsubscribe, E-mail to: <Lasso-un...@lists.lassosoft.com>
Send administrative queries to <Lasso-...@lists.lassosoft.com>
Johan Solve
implementerd as a state machine to handle those optional wuotes around
field values.
2012/3/25 Tom Langton <tomla...@verizon.net>:
> Rick,
>
> The source of the file is a Lasso export, which is really a text file
> which I append with ".xls" so that the user opens it in Excel.
> (Excel is a comfortable and familiar editing environment for the end
> users.)
>
> The user makes their edits, passes it on to the next department, who make
> their edits, passes it back to an admin user, who makes final edits and
> checks for data validity, etc., and then saves the Excel file as tab
> delimited text in Excel. That user imports txt file into Lasso.
>
> (I have been doing this manually (for 2 years) from supplied Excel files
> directly into FM, but for a lot of reasons, they want to control the flow,
> which will be one fewer things I have to worry about in the future.)
> (I have looked for a way to directly import .xls or .xlsx files directly
> with no success. It would be nice to be able to do that because the PHP
> folks across the hall can import .xls files directly - doesn't make me
> look good.)
>
> I could direct the admin user to "save as" anything that Excel can save
> as, but not if it breaks everything I've done regarding the import to date.
>
> I pass the imported text file through Lasso to do the parsing and have an
> iterative function that breaks every field into a variable, so I could
> process the undesirable quotes out of the string there.
>
> And the data is legacy (messy), so there could be quotes in almost any
> field. So, I'm looking for a way to universally deal with this for 32
> imported fields per record.
>
> Thanks,
>
> Tom
>
>
>
> On 3/24/12 3:51 PM, "Rick Draper" <Lass...@webmail.amtac.net> wrote:
>
>>Hi Tom,
>>
>>The quotes are text qualifiers that are normally optional in the
>>export/'save as' - I have been dealing with a similar issue this week,
>>where a CSV file has some field contents defined with quotes and others
>>not (suggesting those fields aren't text).
>>
>>What is the separator between fields? Comma, tab, something else?
>>
>>You say the data can't be broken into two fields, but can the format of
>>the export be changed? Do you get the Excel or text files?
>>
>>Are you passing this through Lasso, or just importing it directly into FM?
>>
>>Very best regards,
>>
>>Rick
--
Mvh
Johan Sölve
____________________________________
Montania System AB
Halmstad Stockholm
http://www.montania.se
Johan Sölve
Mobil +46 709-51 55 70
jo...@montania.se
Kristinebergsvägen 17, S-302 41 Halmstad, Sweden
Telefon +46 35-136800 | Fax +46 35-136801
Tom Langton
I will check it out when I'm over the current hump.
Best regards,
Tom
Marc Vos
I always use TAB-delimited export from Excel and then read the file, split on line endings and then split each record on \t.
- -
Marc
Sent from my iPhone