Issues with CSV import

[stamatis]

Hi Mark and LiveCloud team,

ran into problems importing a CSV file into LiveCloud. This is a large file (122 Mb) with 127K + records

Issues:
1. only 103,000 of the 127,000 records were imported
2. the text was exported from an SQL query system that encloses text components in double quotes; there are leading or trailing double quotes (or both) in all fields. However it seems random if these are leading, trailing or both...
3. several fields also have a leading comma in their text (ie. the field starts with ,"field text"


Any advice? (i mean other than programmatically importing the data file to LiveCode and writing a handler to do this)

Many thanks
Stam

[mark_talluto]

Hi Stam,

We are probably not accounting for something in our CSV importer. If you try to import a single line that has data that failed in the large import, does it still fail? If so, would you send me any lines that fail to import? We can work on improving our importer with some good examples that do not work.

Thank you.

-Mark

[stamatis]

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

[mark_talluto]

Hi Stam,

I would like to get access to your test CSV file. I do not see an attached file. Would you send it to me directly via email? We will get to the bottom of this.

You are right that many systems rely on CSV. Our expectations are to support the CSV file format for as long as it is in use. With all the legacy systems out there, we will need to support it for decades to come.

-Mark

[stamatis]

Hi Mark -- my fault, i didn't realise i had to both 'choose file' and then 'add attachment' -- many forum systems just have the one button.

Furthermore, it seems like the forum system doesn't accept files with the extension .csv
I get the error:
Please correct the following errors before continuing:

• The type of file that you attached is not allowed. Please remove the attachment or choose a different type.
  

I've renamed the file with a .txt extension -- hopefully you'll receive this (I'll email as well)

Best regards
Stam