02-08-2021, 12:15 AM
Hi Mark,
Sorry for the delay in responding - i had to act on this data and have processed it perfectly within a FileMaker Pro database instead, so this fell by the way side a bit.
Would be keen to see this work in LiveCloud though, so following this up.
The initial dataset had something like 127,000 rows; none of them imported correctly. These import correctly in any spreadsheet app (tested in MS Excel on Win/Mac and in Numbers on Mac).
I extracted the first 10 records and anonymised them (medical record numbers and names are substituted as mrn# and name#) - with this small number the import still failed terribly, creating 51 records instead of 10, misaligning data with keys, many stray quotes etc.
I'm not sure if i followed the correct process, but what i did was:
1. Created a new table
2. Copied the column names from the spreadsheet and paste them into the key creation dialog, replicating the spreadsheet names and order
3. right-clicked on table and selected 'import" -> "csv"
4. fail...
I attach the test csv file including the first 10 records exported from a hospital system as is, other than the hospital number/name substitutions as above.
To see what it should look like, open it in any spreadsheet app.
Hope this helps improve the CSV importer; sadly many hospital systems are fairly old and painfully can only export reliably in CSV (forget tab-separated and don't even mention JSON...), so from my point off view would be good to see this improved on...
Best regards
Stam
Sorry for the delay in responding - i had to act on this data and have processed it perfectly within a FileMaker Pro database instead, so this fell by the way side a bit.
Would be keen to see this work in LiveCloud though, so following this up.
The initial dataset had something like 127,000 rows; none of them imported correctly. These import correctly in any spreadsheet app (tested in MS Excel on Win/Mac and in Numbers on Mac).
I extracted the first 10 records and anonymised them (medical record numbers and names are substituted as mrn# and name#) - with this small number the import still failed terribly, creating 51 records instead of 10, misaligning data with keys, many stray quotes etc.
I'm not sure if i followed the correct process, but what i did was:
1. Created a new table
2. Copied the column names from the spreadsheet and paste them into the key creation dialog, replicating the spreadsheet names and order
3. right-clicked on table and selected 'import" -> "csv"
4. fail...
I attach the test csv file including the first 10 records exported from a hospital system as is, other than the hospital number/name substitutions as above.
To see what it should look like, open it in any spreadsheet app.
Hope this helps improve the CSV importer; sadly many hospital systems are fairly old and painfully can only export reliably in CSV (forget tab-separated and don't even mention JSON...), so from my point off view would be good to see this improved on...
Best regards
Stam