Uploaded tables showing formulas instead values

Hi,

thanks for your post, and sorry for the trouble.

You are right, this originates in the TablePress 2.0 update. The reason for this new behavior is that the Excel import in TablePress 2.0 is much more powerful. For example, clickable links, basic text styling, but also formulas are now imported as such. This makes it possible to easily make modifications in a table after importing it, without values being wrong and similar.

Normally, this shouldn’t really cause problems, as TablePress will still evaluate these formulas when a table is shown. Isn’t that working for you?

Note that if you don’t want this behavior, the easiest solution should be to simply save the table to a CSV file in Excel, and then import the CSV file into TablePress. These CSV files will only contain the evaluated content.

Regards,
Tobias

I have the same issue reported. Unfortunately, the concat formula does not work.

The behavior of the import has changed for fields that contained a formula that produced an html link using text excel concat functions. The previous versions imported the interpreted contents, the new version 2 imports the actual formula.

Previous in the 1.x versions, a formula such as

=CONCAT("<a href=", CHAR(34), "[amazon_s3 bucket=orcasecure object=ReelNewsDigital/TRN", [@Year], "0", [@Iss], ".pdf]", CHAR(34),  " target=", CHAR(34),"_blank", CHAR(34), " rel=", CHAR(34), "noreferrer noopener", CHAR(34), ">", [@Year], "-", [@Iss], "</a>")

would be imported with the interpreted contents, which in this case produces a simple html link:

<a href="[amazon_s3 bucket=orcasecure object=ReelNewsDigital/TRN202301.pdf]" target="_blank" rel="noreferrer noopener">2023-1</a>

The new TablesPress import for version 2 now imports the field as a formula instead of contents, and produces:

"=_xlfn.CONCAT(""<a href="", CHAR(34), ""[amazon_s3 bucket=orcasecure object=ReelNewsDigital/TRN"", Table1[[#This Row],[Year]], ""0"", Table1[[#This Row],[Iss]], "".pdf]"", CHAR(34),  "" target="", CHAR(34),""_blank"", CHAR(34), "" rel="", CHAR(34), ""noreferrer noopener"", CHAR(34), "">"", Table1[[#This Row],[Year]], ""-"", Table1[[#This Row],[Iss]], ""</a>"")"

This formula produces an Error during preview, for reasons I could not figure out. I suspect that it is importing extra double-quotes. I understand the intent of the import was to enable certain excel functions, but in this case it does not seem to work. The only workaround I have is to create a new column that contains only the interpreted contents of the formula column (by copying and pasting contents into a new column), and then import that. This extra step does not cause enormous work, I just have to manually clean up the table and delete the formula column.

I just wanted to report this as an issue since it may be that you could make a change to make the formula for concat work like it does in excel, to the benefit of all users, or perhaps you can provide an option for importing Contents rather than Formula for a column or better yet the entire import.

Thanks for a great product, just trying to make it better!

Hi @kylures,

thanks for your feedback here! The problem is probably not so much the CONCAT() function, but the @ references, which might cause problems.

I’ll look into this more, and will also look into offering an option for whether to import with formulas or with results!

In the meantime, can you maybe check if it works to save your table to a CSV file in Excel and then import that into TablePress? This should also just use the results.

Regards,
Tobias

@tobiasbg

You are correct that exporting our table to csv results in formula values however in our case we use commas throughout the text data fields and the only delimiting character that would work might be the pipe |. However, it is easy enough to also just create a new column next to the formula, copy and paste values only into the new column and delete the old formula column and then import. But it would be nice not to have to do that.

I should report that this version 2 is 10X faster at editing and managing the tables. Previously I received many waits of 30 seconds to get the 1200 row table to render in the edit screen. It is 5 seconds or less now. However, there is odd editing behavior that I did not see in the previous version related to cut and paste of text fields that contain double-quotes. Sometimes when I copy this field directly from an excel sheet:

<a href="[amazon_s3 bucket=orcasecure object=ReelNewsDigital/TRN202301.pdf]" target="_blank" rel="noreferrer noopener">2023-1</a>

Then highlight a TableSpace cell and click paste I get

<a href=[amazon_s3 bucket=orcasecure object=ReelNewsDigital/TRN202301.pdf] target=_blank rel=noreferrer noopener>2023-1</a>

Why is is removing double-quotes? That never occurred in version 1.x. However, if I add the quotes back in with edit and copy to another cell and paste, it accepts them. It is as if there is some interpretation code looking at syntax and somehow changing contents.

I can start another thread or contribute to any thread on the topic of cut and paste behavior. It is not really related to the import, except I thought that the reason some formulas do not work may be related to missing double quotes or extra double quotes.

Thanks again for your response and consideration.

Hi,

however in our case we use commas throughout the text data fields and the only delimiting character that would work might be the pipe |

Can you explain why the comma is a problem? Excel should then be “escaping” the cells that have a comma (by wrapping them in quotation marks), so that this should not be an issue?

As for the copy/paste with quotation marks being removed issue: Yes, I have already noticed that as well, and I’m looking into it ??

Best wishes,
Tobias

@tobiasbg ,

I did confirm that exporting an excel file that contains formulas and then importing it to TableSpace does in fact allow the formula values to be correctly interpreted. My concern over text values that contain commas was not warranted, because the csv file correctly escapes the text fields with imbedded commas. So in conclusion, there are two solutions to the issue in this thread. 1) export the excel file to csv and import it 2) create a new column in the excel and copy the formula values into the new cell and delete the old formula cell. It would appear that solution 1 is fewer steps. However, solution 2 may be preferable if you need to review and validate a file before import, since it is more readable.

Thanks again for your support and continued product improvement. This version 2.x is so much faster and easier to work with.

Hi @kylures,

thanks for testing again! Yes, the CSV method is what I would suggest here then.

Best wishes,
Tobias