CSV Import Standards

0

Hi there,

Just spent hours trying to move around 2000 records from a database to Pods. Still haven't got it.

I'm getting closer with every try, however now fields with commas are causing records to skip to the next column (eg, when a comma is in the middle of a single-line text field). On Pods 1.11 using Pods API to import. The import package (both 1.0 and 1.1) only spits out errors in the pods functions file.

I've tried enclosing each field with ' or ", however pods just adds those to the field, and continues to skip columns which it encounters a comma.

I tried exporting one of my other pods to examine the generated csv, however some fields are enclosed with " and some aren't - I can't find a pattern. The default csv export from phpMyAdmin encloses everything with this: ";" Perhaps pods could also consider this and escape characters accordingly.

What csv structure does Pods use? The only guidance I have found is "a standard csv file", however there is no standard for csv's...

Thanks,

Harley.

asked Nov 16 '11 at 8:57

Guil

5

I have gotten multi-picks working. You cannot have a space after each comma separating each entry. Each array must have quotation marks also. Also fixed the comma issue - after dozens of sql queries, I've wrapped every field that contains a comma in quotation marks. If not, leave it. Now only another issue I've left: Warning: trim() expects parameter 1 to be string, array given in D:\xampp\htdocs\fad\wp-content\plugins\pods\classes\PodAPI.php on line 1699 Warning: trim() expects parameter 1 to be string, array given in D:\xampp\htdocs\fad\wp-content\plugins\pods\classes\PodAPI.php on line 1699 Fatal error: Maximum execution time of 30 seconds exceeded in D:\xampp\htdocs\fad\wp-content\plugins\pods\functions.php on line 34 And only a hundred-something entries are saved. The same lot every time :( – Guil Nov 16 '11 at 1:03
add comment
enter at least 15 characters
most votes oldest newest

1 Answer

1

For the timeout, with a large csv file, you will probably have this timeout problem. You can change this in your php.ini by setting a larger value for max_execution_time, or by calling set_time_limit() in your function.

The trim() warnings you are getting may be from empty fields. I don't think these are the root cause of your problem. They are probably getting buffered and displayed when you have the fatal timeout error.

For strings and quotes, the CSV format can be maddening. The semicolon delimiter phpMyAdmin uses isn't much better. What I found worked best was to encode all commas in my data as their ASCII value ,, which keeps them from confusing fgetcsv() during the import. If you can't deal with these characters at runtime, convert them back just before you save using the API call. (The 1.0 version of the plugin does that automatically).

The array() syntax in the import is not part of the CSV standard. I invented that to allow for multi-picks. All I did was embed a php array() statement as part of the CSV data. As the data is parsed, if it finds array, it interprets this as a php array, so it must follow the php syntax rules: "array('one', 2, $data)",

For the format, you are right. There is no standard for it. The function uses fgetcsv() to read the data. Reading through the comments on php.net will give you an idea of its particular quirks.

answered Nov 16 '11 at 2:20

chris.pilko

889

Thanks once again Chris. Spent around 10 hours (I wish I was exaggerating) modifying my database, importing, deleting, importing backups, repeat. Getting there. It's almost working now. A few hundred entries are still messing up quite badly. I'll post again if I come across anything else somebody might find helpful, should they find themselves in this position. – Guil Nov 19 '11 at 5:13
add comment
enter at least 15 characters