Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 18 Nov 2014 15:55:02 +0100How do I export and import formulas to CSV?https://ask.libreoffice.org/en/question/42657/how-do-i-export-and-import-formulas-to-csv/I'm using LibreOffice 4.3.0.4 on a Mac running OS/X Yosemite. I'm trying to follow the instructions at https://help.libreoffice.org/Calc/Importing_and_Exporting_Text_Files because I have a PHP program creating a spreadsheet out of a server database. The PHP creates a CSV file and I want to import that into LibreOffice.
If the csv file says
"=sum(2,3,4)"
"=hyperlink(""http://bbc.co.uk"", ""BBC"")"
then libreoffice imports those cells as free text, not as formulae, independent of the setting of the `libreoffice - preferences - calc - view - formulas` checkbox. If I click in that free text and edit it by adding a space at the end and hitting return, it changes into a formula.
If I have a real formula in a spreadsheet giving a calculated value, the wiki page suggests that I can export the formula (as opposed to a computed value) to a csv file by setting the `libreoffice - preferences - calc - view - formulas` checkbox. When I do that, I *can* see the formulas in the cells on the screen in LibreOffice. Then when I export it to a CSV file, the CSV file contains:
9
"BBC"
and not the formula.
**Is it possible to import formulas from, and export formulas to, a CSV file?**
**Are the instructions at https://help.libreoffice.org/Calc/Importing_and_Exporting_Text_Files incorrect?**
Thanks!Tue, 18 Nov 2014 11:55:05 +0100https://ask.libreoffice.org/en/question/42657/how-do-i-export-and-import-formulas-to-csv/Answer by Lupp for <p> I'm using LibreOffice 4.3.0.4 on a Mac running OS/X Yosemite. I'm trying to follow the instructions at <a href="https://help.libreoffice.org/Calc/Importing_and_Exporting_Text_Files">https://help.libreoffice.org/Calc/Imp...</a> because I have a PHP program creating a spreadsheet out of a server database. The PHP creates a CSV file and I want to import that into LibreOffice. </p>
<p>If the csv file says</p>
<pre><code>"=sum(2,3,4)"
"=hyperlink(""http://bbc.co.uk"", ""BBC"")"
</code></pre>
<p>then libreoffice imports those cells as free text, not as formulae, independent of the setting of the <code>libreoffice - preferences - calc - view - formulas</code> checkbox. If I click in that free text and edit it by adding a space at the end and hitting return, it changes into a formula.</p>
<p>If I have a real formula in a spreadsheet giving a calculated value, the wiki page suggests that I can export the formula (as opposed to a computed value) to a csv file by setting the <code>libreoffice - preferences - calc - view - formulas</code> checkbox. When I do that, I <em>can</em> see the formulas in the cells on the screen in LibreOffice. Then when I export it to a CSV file, the CSV file contains:</p>
<pre><code>9
"BBC"
</code></pre>
<p>and not the formula.</p>
<p><strong>Is it possible to import formulas from, and export formulas to, a CSV file?</strong></p>
<p><strong> Are the instructions at <a href="https://help.libreoffice.org/Calc/Importing_and_Exporting_Text_Files">https://help.libreoffice.org/Calc/Imp...</a> incorrect? </strong></p>
<p>Thanks!</p>
https://ask.libreoffice.org/en/question/42657/how-do-i-export-and-import-formulas-to-csv/?answer=42660#post-id-42660You have to use 'Save As...', choose Text (CSV) as file type, confirm the format in the first popup and check 'Save cell formulae instead of calculated values' in the second popup.
If you don't want to save a complete file but rather to copy/paste formulae from a range into an existing text file: (Temporarily) choose 'Formulae' in the 'Display' section of the 'View' options for Calc. Copy your range and paste wherever you want.Tue, 18 Nov 2014 12:35:20 +0100https://ask.libreoffice.org/en/question/42657/how-do-i-export-and-import-formulas-to-csv/?answer=42660#post-id-42660Comment by Emrys57 for <p>You have to use 'Save As...', choose Text (CSV) as file type, confirm the format in the first popup and check 'Save cell formulae instead of calculated values' in the second popup.</p>
<p>If you don't want to save a complete file but rather to copy/paste formulae from a range into an existing text file: (Temporarily) choose 'Formulae' in the 'Display' section of the 'View' options for Calc. Copy your range and paste wherever you want.</p>
https://ask.libreoffice.org/en/question/42657/how-do-i-export-and-import-formulas-to-csv/?comment=42675#post-id-42675Thanks! That does work.Tue, 18 Nov 2014 15:55:02 +0100https://ask.libreoffice.org/en/question/42657/how-do-i-export-and-import-formulas-to-csv/?comment=42675#post-id-42675Answer by Emrys57 for <p> I'm using LibreOffice 4.3.0.4 on a Mac running OS/X Yosemite. I'm trying to follow the instructions at <a href="https://help.libreoffice.org/Calc/Importing_and_Exporting_Text_Files">https://help.libreoffice.org/Calc/Imp...</a> because I have a PHP program creating a spreadsheet out of a server database. The PHP creates a CSV file and I want to import that into LibreOffice. </p>
<p>If the csv file says</p>
<pre><code>"=sum(2,3,4)"
"=hyperlink(""http://bbc.co.uk"", ""BBC"")"
</code></pre>
<p>then libreoffice imports those cells as free text, not as formulae, independent of the setting of the <code>libreoffice - preferences - calc - view - formulas</code> checkbox. If I click in that free text and edit it by adding a space at the end and hitting return, it changes into a formula.</p>
<p>If I have a real formula in a spreadsheet giving a calculated value, the wiki page suggests that I can export the formula (as opposed to a computed value) to a csv file by setting the <code>libreoffice - preferences - calc - view - formulas</code> checkbox. When I do that, I <em>can</em> see the formulas in the cells on the screen in LibreOffice. Then when I export it to a CSV file, the CSV file contains:</p>
<pre><code>9
"BBC"
</code></pre>
<p>and not the formula.</p>
<p><strong>Is it possible to import formulas from, and export formulas to, a CSV file?</strong></p>
<p><strong> Are the instructions at <a href="https://help.libreoffice.org/Calc/Importing_and_Exporting_Text_Files">https://help.libreoffice.org/Calc/Imp...</a> incorrect? </strong></p>
<p>Thanks!</p>
https://ask.libreoffice.org/en/question/42657/how-do-i-export-and-import-formulas-to-csv/?answer=42658#post-id-42658Aha! On import, in the "Text import" popup that appears when I open the CSV file, I have to uncheck the `Text Import - Other options - Quoted field as text` checkbox. Then I can import formulas from CSV as formulas, not text. Importing plain text still seems to work.
But I still cannot export formulas to a CSV file.Tue, 18 Nov 2014 12:08:42 +0100https://ask.libreoffice.org/en/question/42657/how-do-i-export-and-import-formulas-to-csv/?answer=42658#post-id-42658