Hello,

I need to change the data in one cell in multiple sheetnodes.
The data is situated in the same cell in each sheetnode, so I thought, I could do a find-replace with sql in the database. I tried some days ago with the data in another cell and it worked fine.
The first time I used the mysql "replace" function to change the value "2012" to "2013" in about 20 sheetnodes at once.
This time I need to change a text string, replace a word with another. When I manually change the data in this cell in phpMyAdmin and open the sheetnode in Drupal, the node shows no spreadsheet. When I change the data in the cell back to the previous value, the sheetnode is working again.
I would appreciate some advice, how to change the data in multiple sheetnodes at once, if there is safe way. Of course I could change it in Drupal, but I have a great number of sheetnodes which need to be changed.

Thanks in advance for your help. Sheetnode is a great module, I'm really glad I found it!
Regards,
rapport

Comments

infojunkie’s picture

infojunkie
Location Vancouver
commented

The problem with directly manipulating the sheetnode storage in MySQL is that spreadsheet data is stored as a *serialized* string, meaning that the data is formatted as

s:N:"data";

where N is the string length of the data. This was a design mistake of Sheetnode that has unfortunately stuck on.

You have two options:
a) Make your string changes and make sure that the N value is correctly updated to account for the string difference, or
b) Modify the Sheetnode code to avoid storing serialized versions of the spreadsheet data, which is OK since spreadsheet data is a string to start with. This would require updating all the existing database entries to strip the serialization enclosure.

I prefer approach (b) which is a bit riskier up-front but more manageable on the long run.

infojunkie’s picture

infojunkie
Location Vancouver
commented
Version: 6.x-1.5 » 6.x-1.x-dev
Status: Active » Fixed

I removed the serialization code in the latest dev. Please try it (dev date should be Jan 31st or later), making sure to run the database updates first, then execute your MySQL search-replace procedure.

rapport’s picture

rapport commented

Thanks a lot for your answer. This sounds good.
If I could make the second option work, this would facilitate my work on this site enormously!
I'll try the second option with the latest dev of Sheetnode on a test system and see how it works.
I'll post again about the result.
Kind regards,
rapport

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.