Profields Table CSV Importer / Exporter

[Lutz]

@adrian, good idea, yes, I will have a look...

[Lutz]

@adrian: I tested 2.0.5, everything worked as expected. Another improvement, thanks!

Just for the records, I only tested importing TSV by clipboard, not to import a CSV file. All possible variants: TSV with two columns, four columns (no empty cells and rows with two empty columns), TSV with five columns--into the mentioned field with four columns.

[adrian]

Great - thanks for testing. It all seemed right here too, but it's nice to have another pair of eyes on it.

[Tyssen]

I had this module working OK on a site at about this time last year. Not sure what's changed since then; site was already on 3.0.48, it's now at 3.0.62, but the module itself was at version 25. When I went to use it today it added rows for each row in the CSV but the columns were either blank or 0s. So I upgraded the module to 2.0.5 and now when I save the page I get The process returned no content.

[adrian]

1 hour ago, Tyssen said:

I had this module working OK on a site at about this time last year. Not sure what's changed since then; site was already on 3.0.48, it's now at 3.0.62, but the module itself was at version 25. When I went to use it today it added rows for each row in the CSV but the columns were either blank or 0s. So I upgraded the module to 2.0.5 and now when I save the page I get The process returned no content.

 

Hey @Tyssen - what version of the Profields Table are you running?

That error actually sounds more like something that might happen with the Export function than import. Can you please confirm that you are actually importing?

I just tested import and export here and everything seems to be fine, so unless it's an issue with an old version of the Table field, I will need a little more info to figure things out for you.

 

 

[Tyssen]

I was using an older version of Profields table. I've updated to the latest and now it's creating the rows, but everything is coming out blank.

Sample row looks like

1,21-Apr,1,"Team 1","Team 2","08:30","Field 1A"

I've tried with the above and wrapping the first three columns in "" too.

[adrian]

@Tyssen - can you send me an export of your Table field so I can see the settings you have and can test that example row import?

[Tyssen]

{
    "draw": {
        "id": 146,
        "type": "FieldtypeTable",
        "flags": 0,
        "name": "draw",
        "label": "Match schedule",
        "maxCols": 9,
        "col1sort": 1,
        "col2sort": 2,
        "col3sort": 3,
        "col1name": "round",
        "col1label": "Round",
        "col1type": "int2",
        "col1width": 10,
        "col2name": "date",
        "col2label": "Date",
        "col2type": "date",
        "col2width": 10,
        "col3name": "game",
        "col3label": "Game",
        "col3type": "int2",
        "col3width": 10,
        "col4name": "home",
        "col4label": "Home",
        "col4type": "text",
        "col4sort": 4,
        "col5name": "away",
        "col5label": "Away",
        "col5type": "text",
        "col5sort": 5,
        "col6name": "time",
        "col6label": "Time",
        "col6type": "datetime",
        "col6width": 15,
        "col6sort": 6,
        "col7name": "field",
        "col7label": "Field",
        "col7type": "text",
        "col7width": 10,
        "col7sort": 7,
        "col2settings": "dateInputFormat=Y-m-d\ndateOutputFormat=j M\ndatepicker=3\ndefaultToday=0\nyearRange=-10:+10",
        "col4settings": "textformatters=\nplaceholder=\ndefault=\nmaxLength=2048\nrows=1",
        "col5settings": "textformatters=\nplaceholder=\ndefault=\nmaxLength=2048\nrows=1",
        "col6settings": "dateInputFormat=\ntimeInputFormat=h:ia\ndateOutputFormat=\ntimeOutputFormat=h:ia\ndatepicker=\ndefaultToday=0\nyearRange=-10:+10\nplaceholder=",
        "col7settings": "textformatters=TextformatterEntities\nplaceholder=\ndefault=\nmaxLength=2048",
        "allow_overwrite": null,
        "col8name": "marshal",
        "col8label": "Marshal",
        "col8type": "text",
        "col8sort": 8,
        "col8settings": "textformatters=TextformatterEntities\nplaceholder=\ndefault=\nmaxLength=2048",
        "col9name": "second",
        "col9label": "Second game",
        "col9type": "text",
        "col9sort": 9,
        "col9settings": "textformatters=TextformatterEntities\nplaceholder=\ndefault=\nmaxLength=2048",
        "tags": "results",
        "collapsed": 0,
        "paginationLimit": 0,
        "useBigDelete": null,
        "col1options": "",
        "col1selector": "",
        "col2options": "",
        "col2selector": "",
        "col3options": "",
        "col3selector": "",
        "col4width": "",
        "col4options": "",
        "col4selector": "",
        "col5width": "",
        "col5options": "",
        "col5selector": "",
        "col6options": "",
        "col6selector": "",
        "col7options": "",
        "col7selector": "",
        "col8width": "",
        "col8options": "",
        "col8selector": "",
        "col9width": "",
        "col9options": "",
        "col9selector": "",
        "renameColumn": "",
        "orderByCols": "",
        "addRowLabel": "",
        "showIf": "",
        "themeColor": "",
        "themeBorder": "",
        "themeOffset": "",
        "columnWidth": 100,
        "required": "",
        "requiredIf": ""
    }
}

Not sure whether it's relevant, but my table has a column at the end that's not included in the CSV. 

[adrian]

Thanks for the field details. I just added the field to my system and then imported your example row and this is what I got:

[Tyssen]

So something somewhere is not right. I'm running PW 3.0.62 with Profields Table 0.1.9 and your module 2.0.5 on a server running PHP 7.

I've looked in the logs and nothing showing up there. What else should I be looking for?

[adrian]

10 hours ago, Tyssen said:

What else should I be looking for?

You could start debugging around this line: https://github.com/adrianbj/TableCsvImportExport/blob/a4e6ad668fd92f668546ba62cf726d29871b6d05/TableCsvImportExport.module#L424

Make sure that $rows contains the data.

Then continue through the rest of importCsv() to see if you can figure out where it's failing. If you don't have any luck, is this site live somewhere I can take a look?

Do you have debug mode on and Tracy installed?

[Tyssen]

Turns out the module hadn't actually been updated and was still running the old version even though I ran the update from the admin, saw that the version number had changed to 2.0.5. So not sure what happened there, but it's all good now. ?

[martind]

hi,

the file-based csv-import (both, on a fresh import or update) quits for me in case, when i set orderByCols AND a paginationLimit >0 (onyl orderByCols set without pagination limit seems to work fine) :

Fatal error: Class 'TableRows' not found in /home/.sites/102/site1425/web/v17/site/assets/cache/FileCompiler/site/modules/TableCsvImportExport/TableCsvImportExport.module on line 386

i'm on 'version' => '2.0.5'
pw 3.0.110
table 0.1.9

the table-field has one more col than the csv-file, if that could be the problem. But works fine for everything else but the pagination thing.

thanks.

[adrian]

Hi @martind - sorry about that. I am not sure when this started happening, but I feel like PW's file compiler is no longer correctly dealing with adding the namespace to classes in modules.

Anyway, I have implemented a fix - please check the latest version and let me know how it goes.

[martind]

thanks adrian,

works perfect now also with pagination, even the ignore-first-row feature is working, which wasn't possible too, to get working by module-settings in version 2.0.5

great module, thank you!
martin

[Robin S]

Just popping in to say thanks for this awesome module! The "Paste in CSV Data" field saved me a heap of time today.

[adrian]

1 minute ago, Robin S said:

Just popping in to say thanks for this awesome module! The "Paste in CSV Data" field saved me a heap of time today.

Glad you found it useful - thanks for the kudos!

Not sure if it's what you used, but pasting directly from Excel (using the tab delimiter) works great.

[Robin S]

1 minute ago, adrian said:

Not sure if it's what you used, but pasting directly from Excel (using the tab delimiter) works great.

Exactly that. It's great when you have a big messy spreadsheet and you just want to grab selections of data to add to the Table.

[adrian]

Just wanted to make a note here that I just wasted several hours trying to debug why I couldn't manually add or delete rows from a table when this module was installed. Turns out it was PHP's max_input_vars setting being too low, but no error was being logged anywhere.

In the process of debugging I did fix a few bugs related to Table fields inside Repeaters and some other cleanup, so perhaps it was a worthwhile waste of time after all ?

[thausmann]

Hey @adrian, thanks for this great module.

Is there a way to choose a page field instead of the id when exporting a table with a page reference? I have a table with users and it would be awesome to export the name or email instead of the id. As an idea, maybe just a checkbox to export "labelField" instead of value. 

[adrian]

Hi @thausmann - when exporting a table with a page reference field, it should export the title of the selected page(s), and not the ID so I am maybe confused about what you are seeing. The option to export a field from the referenced page would be great for sure. The way I'd envision this working would be for the "Columns / Order to Export" selection to work like Lister does with its ASM parents functionality where you select a parent and then get to select subfields, but I am rushed for time, so for the moment, I've just made it possible to select them as separate top-level items.

Please try the not-well-tested version attached. Please feel free to make improvements.

TableCsvImportExport.zip

[thausmann]

Hey @adrian,

thanks a lot, I've tested your modified version. For me, subfields as top level options are totally fine, already adds a lot of possibilites for user references!

Quote

when exporting a table with a page reference field, it should export the title of the selected page(s), and not the ID

I found that my columns have type pageAutocomplete and not pageSelect, I think that's why they were exported as IDs only. I added pageAutocomplete to the fieldType checks and this works! – unless I export both "User" and "User.email", then somehow the $subfields indexes are off (Undefined offset ...). So I made one more change where the main page "User" is not added as an option anymore. To cover all cases I also added id as a subfield option. I marked my edits with //@TH SUBFIELDS

There is also pageAutocompleteMultiple, that seems to procude other problems I didn't dig into.

 

TableCsvImportExportSubfieldsTH.zip

[adrian]

Thanks @thausmann for those fixes and improvements - I've made a couple of small tweaks:

1) the title subfield is automatically selected now so that users don't need to do anything when exporting to get each column automatically added.

2) I added support for pageAutocompleteMultiple

New version is committed to the repo and updated in the modules directory.

[webhoes]

I keep getting an error for an frontend page to import csv.

$wire->modules->get('TableCsvImportExport'); // load module

$options = array(

    'delimiter' => ',',

    'enclosure' => '"',

    'convertDecimals' => false,

    'multipleValuesSeparator' => '|',

    'namesFirstRow' => false

);

$page->importTableCsv('importTableCsv', $csvData, $options);

For testing page table field is also called importTableCsv

[adrian]

Hi @webhoes - firstly, sorry for the late response, I was on vacation last week.

I have fixed the issue you were seeing. Not sure when that showed up - seems like maybe a change to the PW may have caused it. Anyway, should be fine now, but please let me know how it goes for you.

PS - is your table field really named "importTableCsv" ? You probably want to change the first parameter in that call to the $page->importTableCsv() method to the fieldname.