Importing large CSV file

[Lawlerdev]

Problem: importing CSV file with 12242 records only shows 8110 records after importing into FM9. Import the same file into Excel or FM6 and you get the correct 12242 records. Any ideas or is this a bug?

I enclose sample files

Text File.txt - original text file containing 12242 lines/records CSV from windows platform

Text File Converted.FP5 - original file opened in FM 6 12242 records

Text File Converted.FP7 - original file opened in FM 9 Advanced, 8110 records and prices missing also.

Archive.zip

[LaRetta]

Your prices are there but they are on the second line. Click into the field to see them.

If you create a calculation with: ValueCount (f1), you will see towards the end, you have multiple values (as many as 800) in one field. It is because the commas should be converted to record delimiter.

Is this the text field you will be dealing with regularly or is this a one-time thing? Where did this file come from and can you get the data in a consistent format?

It goes belly-up on you on record number 8086. I believe you are mixing tab delimited and comma delimited together. You must split them.

Edited July 17, 2008 by Guest

[Lawlerdev]

I agree with what you're saying, but how come both Excel and FM6 can read the file without difficulties?

[Fenton]

FileMaker's Import of CSV does not handle problems as well as Excel. What is making it choke are quotes within the data.

POST MORTEM NEEDLE 4"

Line 264. FileMaker thinks everything after that is part of the same field, until it hits another quote. Which is quite a while and many (120) lines later. (FileMaker includes the whole line of the beginning, ie., before the actual quote, but does not include the quote itself, as it's a "separator".)

Create a calculation, PatternCount (f1; ¶)

You will see there are 21 problem records. That is where all the missing lines went.

You could use a text editor [bBEdit, TextWrangler (free)] to Find/Replace quotes with something else (HTML entity perhaps), then Import, and use Substitute to get your quotes back. If on a Mac, AppleScript could do this instead/or with the text editor.

[Oops, that was line 264 not 464. I looked thru a few of these errors. They are caused when there is a single set of quote marks, usually for inches. It is not an error if there are 2 sets of quotes, such as line 19, "Ouch".]

Edited July 18, 2008 by Guest

[Vaughan]

The in-data commas aren't being delimited out correctly in the import file. Each field should be surrounded by double-quotes:

"field1","field2", ...

"field1","field2", ...

Try this: import into FMP 6, since it works. Export from FMP 6 to csv file. Import this new csv into FMP 9. If it works, compare this new file to the original and see what's different.