Darn, got pretty far into a response before I caught your last "no updating" line.
Could you show a screenshot of the import step? Also, in the lower-left, click the right arrow so the first line of data is showing and a count of the records FM is seeing. Is that 18,000 or ~9,500?
Another thought: is it possible that the end of line character is incorrect, at least in some cases? That could cause two rows to be interpreted as one, and the fields from the second would be in different columns and therefore not imported.
Kind of shooting in the dark here. Let me know if I hit anything!
Yes, I had same csv import issues too. Record count would be cut off at some point.
It's most likely you have a wrong character somewhere in the data row(s) that messes it up.
If column values in csv were in quotes, you wouldn't have this problem. Only thing filemaker will import the quotes and will have to be massaged.
Maybe a number column that has a "," for decimal? Or a text field.
In my case I switched to TAB separation ( exporting of data from other system was done by myself ) to avoid quoting everything.
1 of 1 people found this helpful
James, try opening the csv with a spreadsheet and or a text editor (free TextWrangler). See if all is ok, and save with appropriate OS end of line characters. Do a count of carriage returns into a variable, see if it matches the number of lines. Import the file into a container field, then export it as File.txt into the Temporary path, then Insert From URL to a WebViewer. Or import the csv file directly into a WebViewer using Import From URL, see if all lines are there.
Probably best, just drop the csv file on the FM icon, it should create a new FM database, see if all the records are present.
any chances that a line contains line-breaks or other odd characters? Check the import files using a text-editor (BBEdit, TextWrangler, etc)
check one of the lines (and the line before..) that are missing (TextEditors will have line-numbers)
I'll second the conversion of the CSV to FMP format. Much can be revealed that way. You would, for example, see extra fields if, as Chris mentioned, data rows were getting glued together by missing EOL characters. If you find odd or inexplicable characters you might be able to do something like copy the csv file contents and paste into a text field in FMP then use Filter(that text field;"letters and numbers and quotes etc. and line feeds") to get that data into another field. Copy that, paste to excel, save as CSV then import. Barely elegant but would, I suspect, work.
This would be OK for one-time import but would be a serious drag for doing often. In the "often" mode I'd imagine you could script some fancy data parsing of the clean data to write the data into your solution.
"Commas in The New Yorker fall with the precision of knives in a circus act, outlining the victim." ~E. B. White
Thanks to everyone who's contributed to this. Having inspected the file with text wrangler, I think it certainly looks like malformed data, or odd EOL characters or something. I should have stated earlier that this file is produced automatically by a third party system, and needs to be imported every night as part of an automated schedule, so I think I'm going to need to go back to the system that generates the file and see if I can make some changes there. Interesting that electon found that a tab separated data format cured his/her problems, I'll try that and see what I can find.
Thanks again to the community, you never fail me!
James, yes, definitely find out what the EOL is for the file(s). It could be CRLF which some apps (even wrangler) might "convert" for human consumption and not reveal the true EOL. And FM import might then interpret as CRCR (thus a blank line!)
And some apps (even FM) might convert a 'return-in-field' upon export such that the values for EOL is CR, but the in-field part is the "odd" character. These could be re-imported incorrectly. (although FM14 can export .tab/.txt and re-import with no problems)