[Solved] Getting Strings from Calc to .txt

Discuss the spreadsheet application

[Solved] Getting Strings from Calc to .txt

Postby pechart » Fri Jan 25, 2008 11:10 pm

Hello everybody!
First of all: I'm new to Calc, so I don't even know where I can start to search for related topics. Sorry for that.

I would like to know, if it is possible to create a string from different cells and create a new string. Then I want to export the created new strings to a .txt File (or even better a .bat file).
But i just don't know where to start. I open up my document and create a new function. But what is the right syntax?
My columns are like [No.] [Name] [Startpoint] [Endpoint]. My goal is to have a Textfile with a string like " ...<Text> [No.] named [Name] from [Startpoint] to [Endpoint] <Text>... " for each row of the sheet.
Hmm, sorry if that's somewhat hard to understand.
My other problem is, that i'm short on time in a project and I can't spend to much time on reading the docs etc. yet. So it would be really great, if someone could help me with this.

Thank you!
Last edited by pechart on Sat Jan 26, 2008 1:45 pm, edited 2 times in total.
pechart
 
Posts: 3
Joined: Fri Jan 25, 2008 10:54 pm

Re: Getting Strings from Calc to .txt

Postby kingfisher » Fri Jan 25, 2008 11:54 pm

You could use the CONCATENATE function or simply =A1&B1&C1&D1

Copy and special paste to another column. De-select 'formulas' in the dialogue. Copy and paste the new column to your text document. (You may be able to copy and paste the formulas column straight to a text document; I forget.)
Apache OpenOffice 4.1.6 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am

Re: Getting Strings from Calc to .txt

Postby acknak » Sat Jan 26, 2008 12:20 am

You can use something like this to have more control over how the text is put together:

[No.] & " named " & [Name] & " from " & [Startpoint] & " to " & [Endpoint]

As an actual formula, you need the cell references in there, of course. Something like:

=A1 & " named " & B1 & " from " & C1 & " to " & D1

You can copy that to include all your raw data, then export the formatted data to a text file: File > Save As, file type = "Text (CSV)".
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Getting Strings from Calc to .txt

Postby pechart » Sat Jan 26, 2008 12:49 pm

Nice, thanks for your help! Works really good. One more Question then, what is the syntax for embedding a " in that string? like

=A1&" -proj "c:\documents and settings\" "&B2

I really appreciate your help! You don't know how much this speeds up my workflow, bacause I have to set up a whole bunch of commandlines in that style, that otherwise would take me hours or even days. Now i can enter the formula once and paste it through the whole document with automaticaly changing values. Awesome!

Oh, and one (hopefully) last question: Is there a way to declare variables like

string $mr=" -r blablabla";

and then use that in the formula?

Anyway, thanks again.
pechart
 
Posts: 3
Joined: Fri Jan 25, 2008 10:54 pm

Re: Getting Strings from Calc to .txt

Postby Hagar Delest » Sat Jan 26, 2008 1:29 pm

pechart wrote:what is the syntax for embedding a " in that string? like
=A1&" -proj "c:\documents and settings\" "&B2

What I would do is to put the following code in a cell and then refer to the cell instead of the code itself (note the single quote at the beginning)
Code: Select all   Expand viewCollapse view
' -proj "c:\documents and settings\"

pechart wrote:Oh, and one (hopefully) last question: Is there a way to declare variables like
string $mr=" -r blablabla";
and then use that in the formula?

Don't know if it's possible but I would rather put the string "-r blablabla" in a cell and refer to that cell when needed in a formula. Cells are containers that act like a variable.

Thanks to add '[Solved]' in your first post title (edit button) if your issue has been fixed.
LO 6.4.6 on Xubuntu 20.04 and Windows 10.
User avatar
Hagar Delest
Moderator
 
Posts: 29079
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Getting Strings from Calc to .txt

Postby pechart » Sat Jan 26, 2008 1:37 pm

Thanks again. Good point, that the cell is a container... That will work.
pechart
 
Posts: 3
Joined: Fri Jan 25, 2008 10:54 pm

Re: [Solved] Getting Strings from Calc to .txt

Postby acknak » Sat Jan 26, 2008 6:34 pm

You can include double quotes in a string constant by typing two quotes together:

="A hollow voice said ""Plugh"""

Right, cells are the containers, but you can easily use them as variables by giving the cell a name. This is a good practice, but often overlooked because it takes some time to set it up. Calc also may bite you at some point, because it has not been thorough treated to make everything work with names in place of a cell address. It's probably 95% there, so it's still useful.

Cell names are good for fixed references, whereas in your example you have columns of data. The reference needs to reference a relative location. You can set up column labels for that case:
col_labels.png
col_labels.png (6.26 KiB) Viewed 1966 times


Calc will automatically find the column labels, but it seems to get confused in this case. I had to use Insert > Names > Labels to specifically tell it that A1:D1 are the labels for the data in A2:D4.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Solved] Getting Strings from Calc to .txt

Postby kingfisher » Sat Jan 26, 2008 9:34 pm

pechart wrote:Is there a way to declare variables like string $mr=" -r blablabla";
and then use that in the formula?


Possibly. Menu: Insert >Names >Define. I've only used it for assigning names to formulas (or to ranges) but I think you could assign a name to a string.
Apache OpenOffice 4.1.6 on PCLinuxOS
User avatar
kingfisher
Volunteer
 
Posts: 2120
Joined: Tue Nov 20, 2007 10:53 am


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 27 guests