CSV import from OpenOffice

I cannot import at all, since updating to latest version.
Tried exporting and re-importing a single record, no dice
Keep getting “Invalid file for Import” myself…….

Yes, I had to increase the security of the file uploads, but it seems that some apps like Open Office don’t produce standard CSV files. The problem is the plugin is expecting the files to have a mime-type of “text/csv” and it will reject the upload if it doesn’t have that.

If you can set the correct mime type (don’t ask me how, I don’t know) it will work.

I thought mime types were assigned by internet browsers or web servers ‘on the fly’, basically, and not an actual part of a particular file. The only “how to” I can locate to change a mime type makes the change system-wide, and would apply to all files with a certain file name extension, like .csv.
There’s probably more to it than the little bit I could dig up in a few minutes though…

Yes, that is true, and I’m not entirely certain how it gets assigned…perhaps it’s not a good way to validate the file.

I have tried two different approaches, csv export from Microsoft Excel and an export created by Participants Database itself.
I would assume that Microsoft Excel creates “standard” csv files, but if not, your reply leads me to ask: Does Participants Database create “standard” csv files?

I agree. This is now broken. If i export a CSV file from Participants Database and immediately re-import it without touching it I get “Invalid file for import”. In other words, not even Participants Database can produce a file that is valid for import.

My IIS Server Manager says that the mimetype associated with .csv files is: application/octet-stream. This didn’t work either. Eventually, when i changed line 154 of xnau_CSV_Import.class.php to:

$check = pathinfo( $filename, PATHINFO_EXTENSION ) === ‘csv’ && $mimetype === ‘application/vnd.ms-excel’;

my file imported fine…

I’m off to try this on eHost (which I think is running some version of Linux)…

as a quick hack maybe change line 154 to

$check = pathinfo( $filename, PATHINFO_EXTENSION ) === ‘csv’;

and kill the mimetype check altogether until it’s better understood?

So basically the mimetype for .csv files has been hijacked somewhere from ‘text/csv’ to ‘application/vnd.ms-excel’. I’m using Chrome and I have Excel installed on my computer

A StackOverflow article says:

“An example: when uploading a CSV file from a Windows system with Microsoft Excel installed, Chrome will report this as application/vnd.ms-excel. This is because .csv is not specified in the first hard-coded list, so the browser falls back to the system registry. HKEY_CLASSES_ROOT\.csv has a value named Content Type that is set to application/vnd.ms-excel.”

Article is here:
https://stackoverflow.com/questions/1201945/how-is-mime-type-of-an-uploaded-file-determined-by-browser

So maybe change line 154 to:

$check = pathinfo( $filename, PATHINFO_EXTENSION ) === ‘csv’ && ($mimetype === ‘text/csv’ || $mimetype === ‘application/vnd.ms-excel’);

to support upload of csv files for Microsoft Windows users with Excel installed? Nice to see Microsoft adhering to standards as usual…

Seems the following are valid mimetypes for csv

text/plain
text/x-csv
application/vnd.ms-excel
application/csv
application/x-csv
text/csv
text/comma-separated-values
text/x-comma-separated-values
text/tab-separated-values

ref: https://stackoverflow.com/questions/7076042/what-mime-type-should-i-use-for-csv

I changed the code to remove the mime check (I know, bad practice, but it’s just temporary to allow importing some records) and although importing the .csv now works, there’s a new issue:

I’m not sure if I should mention this here, or in a new topic, but anyway… the new records created do not have a Private ID. Everything else looks ok, just no PID.

Thanks for that, I’ll look into that as a bug to fix. I should be able to get a fix out in the next day. You will need to re-upload in order to generate the PIDs once that is out.

Looking forward to it….