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

Discuss the spreadsheet application

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

Postby JimTrail » 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.
Attachments
Price Grid.ods
(12.68 KiB) Downloaded 36 times
64-bit, Windows 7, Open Office 3.3.0 (previously using 3.4.1. The 3.4.1 had bugs in it and would not work properly with my 64-bit operating system)
JimTrail
 
Posts: 21
Joined: Sat May 09, 2009 6:47 pm

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

Postby Villeroy » 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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17311
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby acknak » 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.
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17421
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

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

Postby rudolfo » 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   Expand viewCollapse view
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.
OpenOffice 3.1.1 (2.4.3 until October 2009) and LibreOffice 3.3.2 on Windows 2000, AOO 3.4.1 on Windows 7
There are several macro languages in OOo, but none of them is called Visual Basic or VB(A)! Please call it OOo Basic, Star Basic or simply Basic.
rudolfo
Volunteer
 
Posts: 1416
Joined: Wed Mar 19, 2008 11:34 am
Location: Germany

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

Postby JimTrail » 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.
64-bit, Windows 7, Open Office 3.3.0 (previously using 3.4.1. The 3.4.1 had bugs in it and would not work properly with my 64-bit operating system)
JimTrail
 
Posts: 21
Joined: Sat May 09, 2009 6:47 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 13 guests