CSV Import - Multi-Item List Field Workaround

Re: [Rusty] CSV Import - Multi-Item List Field Workaround

By Rusty - February 9, 2011

I know I can manually go through and edit each record individually, is there a simple way to replace the field for ALL the records in a table?

Right now I've only got about 100 records in the table, enough to be a pain but not impossible to do by hand, but I will have many many more.

I'm looking for an easy way to replace/update the values for a multi-item list field across the entire table.
Rusty

Re: [Rusty] CSV Import - Multi-Item List Field Workaround

By ross - February 10, 2011

Hi Rusty

I am not quite sure I understand the problem but it sounds like you are trying to import or export a field that is basically a list of values separated by commas (CSV). Does that sound right? Maybe you could give me a sample of the data you are working with.

On the other hand, if you are looking for a way to update the values in one field for all records in a table, you can always do it using the MySQL console plugin (free)

http://www.interactivetools.com/add-ons/detail.php?MySQL-Console-1011

That lets you run SQL right on your database. Make sure to always have backups before doing anything with this plugin :).
-----------------------------------------------------------
Cheers,
Ross Fairbairn - Consulting
consulting@interactivetools.com

Hire me! Save time by getting our experts to help with your project.
Template changes, advanced features, full integration, whatever you
need. Whether you need one hour or fifty, get it done fast with
Priority Consulting: http://www.interactivetools.com/consulting/

Re: [ross] CSV Import - Multi-Item List Field Workaround

By Rusty - February 11, 2011 - edited: February 11, 2011

Hi Rusty

I am not quite sure I understand the problem but it sounds like you are trying to import or export a field that is basically a list of values separated by commas (CSV). Does that sound right? Maybe you could give me a sample of the data you are working with.

On the other hand, if you are looking for a way to update the values in one field for all records in a table, you can always do it using the MySQL console plugin (free)

http://www.interactivetools.com/add-ons/detail.php?MySQL-Console-1011

That lets you run SQL right on your database. Make sure to always have backups before doing anything with this plugin :).


I have a section editor, where I have the basic outline/structure for mutliple items.

One of the fields in that section editor is labeled "Permission" and it is a multi-item list field (mutli-select drop down menu). This allows the admin to limit what pages on the site itself that a logged in viewer will have access to view. The permission field labels are pulled from the User Accounts Full Name field, and the permission field values are pulled from the User Accounts UserNum.

What I'm attempting to do is to import, via the CSV Import plugin, default "Permission" values, so that the admin won't have to sift through hundreds of files, going through and selecting all the users for each and every record.

I exported a few of the records, using the CSV Export plugin, just to see how the exported fields looked. And I tried to import a CSV with the same type of formatting, and got no where.

So what I'm trying to discern is, short of ME having to go into phpMyAdmin and manually pasting the permissions, if there is a way to set the "permission" values on ALL the records in that associated table.


Attached is the CSV Export..., and the way I had formatted my last CSV to try to CSV Import. All the fields except the permissions are getting properly imported.
I've tried calling Permission, all of the following and it just won't take: "permission" , "permission:labels", "permission:values" Obviously when I tried to import the permission lables, I replaced the UserNum with the FullName values.

When I would try to import the numerical UserNum values into the Permission field, it does get imported however what happens is that it's listed as "Previous selection (no longer in list)"


I took a look at the mySql Console plugin, I just don't know enough yet on how to duplicate the values for one record, and update/insert them across the rest of the records in that table. More reading to do.

From what I can see the Example provided:
UPDATE cms_exampleTable SET field='value';

So since my table is called forms, I would cahnge it look like this:
UPDATE cms_forms SET permission='84, 9, 10, 11, 69, 73, 35, 27, 64, 16, 34, 42, 37, 45, 60, 15, 28, 77, 25, 79, 38, 2, 23, 40, 3, 31, 80, 62, 65, 59, 70, 19, 24, 54, 36, 8, 78, 13, 1, 33, 17, 76, 56, 44, 43, 41, 7, 55, 21, ';

Or will I need to put it in as a Tab Separated like this:
84 9 10 11 69 73 35 27 64 16 34 42 37 45 60 15 28 77 25 79 38
Rusty
Attachments:

csv_export.png 101K

csv_import.png 98K

Re: [Rusty] CSV Import - Multi-Item List Field Workaround

By Rusty - February 11, 2011 - edited: February 11, 2011

Nevermind, I came to the answer myself. Trial and error :D

the mySql Plugin works great. I was able to update the Table field "permission" by dumping in the Tab Separated values and it took to the checkboxes no problem. Going to try the Drop Down List now... Just wanted to be able to visually see the entire list at once, to make sure that the scrolling action to see the entire list didn't screw it up and somehow un-select a bunch of the options.

Thanks for the help!!

PS. Is there an easy way to modify how many "Rows" or options are shown by default with a Multi-Select Drop Down list?

I know we can manually spec a height for a text input box etc from advanced options..
Rusty

Re: [Rusty] CSV Import - Multi-Item List Field Workaround

By Chris - February 19, 2011

Hi Rusty,

That's a great idea. No, unfortunately there's not, but I will add that to our wishlist. Thanks!
All the best,
Chris