Apache OpenOffice Community Forum

Posted: **Thu Mar 28, 2013 5:15 am**

Hello Open Office forum!

Question. How can I save i). the first 14 lines of a spread sheet; as ii). a text file; and iii). without the dollar signs; and iv). separated with tabs - not commas?

I am working on pricing for a web site. I need to put together pricing grids for 2,000+ products. I am uploading the pricing grid(s) to a web site. The web site uses PHP to parse the pricing grids and output them to a website.

i). The first fourteen lines are the pricing grid. I don't want to save the entire spread sheet - just the fourteen lines.

ii). Calc has an option to save as a .cvs file. There is no option to safe as a plain text (.txt) file. I would like to save the file as a .txt file.

iii). The PHP script might not like the $ signs. The script might interpret the number with a dollar sign as a variable. I would like to save the the numbers without the dollar ($) sign.

iv). Calc will save the pricing grid with tabs instead of commas - but you have got to go through an extra step in the "Export of text files" box that pops up. The default value under "Field delimiter" is a comma. I would like the default value to be {Tab} instead of comma. I will be saving the pricing grids for over 2,000 products. Eliminating one step would make my job a lot easier.

In summary I just want to save the first fourteen lines as a text file with values separated with tabs and without the dollar sign without having to go through the extra steps.

I would appreciate any suggestions.

Thanks.

Posted: **Thu Mar 28, 2013 10:23 am**

File>Save As...
Type: Text (*.csv)
Specify the file name with .txt suffix
Automatic file name extension = OFF
Edit filter settings = ON
In the filter settings dialog:
Delimiter = {Tab}
Save as displayed = OFF

Posted: **Thu Mar 28, 2013 2:29 pm**

OOo Calc was recently updated to remember the csv export settings so you may not have to make the filter settings after the first time.

The only catch is that sometimes it remembers and sometimes it doesn't--I haven't figured out the pattern.

If it doesn't remember for you, try a search here: there are lots of threads where people need to repeat the same export operation. If I recall correctly, you can even find a macro to automatically make all the desired settings for you.

Posted: **Thu Mar 28, 2013 2:58 pm**

Villeroy's solution is not as painful as it might look on first sight. At least on my Apache OpenOffice 3.4.1 the Delimiter defaults to the one that was chosen the last time (if you don't close OpenOffice in the meanwhile). As far as I remember it should be like that since 3.3, because in that version the ASCII Import/Export filter has been improved in many ways.

Still if you think a macro can speed this up some more you can follow the way that is described in the Macro Wiki:

Code: Select all

 sUrl = "file:///path/to/your/filename.txt"
 FileProperties(0).Name = "FilterName"
 ' "Text CSV" is displayed in the SaveAs Dialog, but the Macro Recorder uses:
 FileProperties(0).Value = "Text - txt - csv (StarCalc)"
 FileProperties(1).Name = "FilterOptions"
 ' The first columns are Ascii Values of the separator and the Text Delimiter
 ' We don't need a text delimiter, so we set it to zero (34 would be the double qoute)
 FileProperties(1).Value = "9,0,ANSI,1,,0,false,true,false"
 oDoc.storeAsURL(sUrl, FileProperties())

Note: This code is not complete it is simply taken from what the Macro recorder has listed when doing the steps that you described. Read first the Wiki page that I have mentioned above and complete the macro based on what you find ther. (The 2 commas without anything in between are not a typo, that's how it came out from the macro recorder). Either the 0 or the one of the false are responsible for saving the cells not as shown. If you really need to know exactly, you can record different constellations with the macro recorder by yourself. The code of the macro recorder is low quality, but at least in this case it gives you some hints what to use in the obscure PropertyValue array FileProperties.

Posted: **Fri Mar 29, 2013 3:33 pm**

Thanks villeroy, acknak, rudolfo for responding to my question. I will look into the macro function and see if I can get one to work.

Thanks again.

Apache OpenOffice Community Forum

How to save Calc file as .txt, with tabs, and omit $ signs

How to save Calc file as .txt, with tabs, and omit $ signs

Re: How to save Calc file as .txt, with tabs, and omit $ sig

Re: How to save Calc file as .txt, with tabs, and omit $ sig

Re: How to save Calc file as .txt, with tabs, and omit $ sig

Re: How to save Calc file as .txt, with tabs, and omit $ sig