You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openoffice.apache.org by Bill Dillinger <cj...@his.com> on 2012/05/02 16:25:54 UTC
OO spreadsheet question
When I download my bank statements they come as .csv files. When I
open these in the Open Office spreadsheet the fields with numbers come
in as characters and the formula window shows them to be preceded by
a quote, ', mark. I have looked through 'help' but have not found a way
to remove the leading quote or to reformat the column to numbers so
they can be used in calculations.
I would appreciate it if someone would tell me how to change these columns
of characters into numbers.
Re: OO spreadsheet question
Posted by Bill Dillinger <cj...@his.com>.
Thanks to All! Really quick responses. I have created a new folder in
Thunderbird to save mail from this group as the responses are really
good. Paul's response was first and lead me to an easy answer. My dialog
box had a check for "Quoted field as text". When I unchecked this box
the numbers came in as numbers.
In my files most fields were included in double quotes,", rather than
the single quotes as in Paul's example. Interesting to me the date field
was preceded by 3 quotes and followed by one and it still comes in with
a leading ' in the formula window but that doesn't hurt. All text fields
still come in as text so I am good.
Thanks again.
Bill Dillinger
On 5/2/2012 10:48 AM, Paul M. Nguyen wrote:
> I believe the dialog box that appears when you attempt to open a .csv
> file allows you to specify various characteristics of how the file is
> structured. The fields could be tab-delimited or comma-delimited (or
> any arbitrary character) and fields may be enclosed in other
> punctuation that OOo can strip out for you in interpreting that
> file... Would you mind attaching a couple rows worth of your sample
> input so we can help you find the right import settings?
>
> If your data looks like what is in the file I attached, then the
> correct settings for you would be to set the Separator options to
> comma-delimited and change the drop-down "Text delimiter" to a single
> quote (') instead of backtick (`) which was the default on my system
> (OOo 3.3 on Mac OS X 10.6.8). You'll see the quotes removed in the
> preview before you finish opening the file.
>
> Good luck!
>
>
> --
> Paul M. Nguyen
>
>
> On Wed, May 2, 2012 at 10:25 AM, Bill Dillinger <cjbill@his.com
> <ma...@his.com>> wrote:
>
> When I download my bank statements they come as .csv files. When I
> open these in the Open Office spreadsheet the fields with numbers come
> in as characters and the formula window shows them to be preceded by
> a quote, ', mark. I have looked through 'help' but have not found
> a way
> to remove the leading quote or to reformat the column to numbers so
> they can be used in calculations.
>
> I would appreciate it if someone would tell me how to change these
> columns
> of characters into numbers.
>
>
Re: OO spreadsheet question
Posted by "Paul M. Nguyen" <li...@gmail.com>.
I believe the dialog box that appears when you attempt to open a .csv file
allows you to specify various characteristics of how the file is
structured. The fields could be tab-delimited or comma-delimited (or any
arbitrary character) and fields may be enclosed in other punctuation that
OOo can strip out for you in interpreting that file... Would you mind
attaching a couple rows worth of your sample input so we can help you find
the right import settings?
If your data looks like what is in the file I attached, then the correct
settings for you would be to set the Separator options to comma-delimited
and change the drop-down "Text delimiter" to a single quote (') instead of
backtick (`) which was the default on my system (OOo 3.3 on Mac OS X
10.6.8). You'll see the quotes removed in the preview before you finish
opening the file.
Good luck!
--
Paul M. Nguyen
On Wed, May 2, 2012 at 10:25 AM, Bill Dillinger <cj...@his.com> wrote:
> When I download my bank statements they come as .csv files. When I
> open these in the Open Office spreadsheet the fields with numbers come
> in as characters and the formula window shows them to be preceded by
> a quote, ', mark. I have looked through 'help' but have not found a way
> to remove the leading quote or to reformat the column to numbers so
> they can be used in calculations.
>
> I would appreciate it if someone would tell me how to change these columns
> of characters into numbers.
>
>
Re: OO spreadsheet question
Posted by Tom Young <ty...@ufm.biz>.
On 5/2/2012 10:25 AM, Bill Dillinger wrote:
> When I download my bank statements they come as .csv files. When I
> open these in the Open Office spreadsheet the fields with numbers come
> in as characters and the formula window shows them to be preceded by
> a quote, ', mark. I have looked through 'help' but have not found a way
> to remove the leading quote or to reformat the column to numbers so
> they can be used in calculations.
>
> I would appreciate it if someone would tell me how to change these
> columns
> of characters into numbers.
>
>
Bill -
I had the same situation and found the answer by looking up "leading
zeros" in the Help system of OO. It explains how to replace the " ' "
with numbers. Hope this helps.
Tom
---------------------------------------------------------------------
To unsubscribe, e-mail: ooo-users-unsubscribe@incubator.apache.org
For additional commands, e-mail: ooo-users-help@incubator.apache.org
Re: OO spreadsheet question
Posted by Niall Martin <ni...@rndmartin.cix.co.uk>.
I open csv files in notepad (actually textpad, which is better in various ways), then highlight
the download, copy, then open my spreadsheet, where there are columns set for date,
currency etc., so that I can paste it in at the point you choose. Up comes a dialogue. The
important box to tick is the one that says the divider is a comma. You don't want tab here
(csv is after all comma separated variable!) There is another that allow you to set single or
double quotes. Experiment to see what works best (a box shows the effect) -- I set single
quotes. Then hit the OK button.
That should do it.
On 2 May 2012 at 10:25, Bill Dillinger wrote:
Send reply to: ooo-users@incubator.apache.org
Date sent: Wed, 02 May 2012 10:25:54 -0400
From: Bill Dillinger <cj...@his.com>
To: ooo-users@incubator.apache.org
Subject: OO spreadsheet question
> When I download my bank statements they come as .csv files. When I
> open these in the Open Office spreadsheet the fields with numbers come
> in as characters and the formula window shows them to be preceded by
> a quote, ', mark. I have looked through 'help' but have not found a
> way
> to remove the leading quote or to reformat the column to numbers so
> they can be used in calculations.
>
> I would appreciate it if someone would tell me how to change these
> columns of characters into numbers.
>
>
Niall Martin
Phone 0131 4678468
Please reply to: niall<at>rndmartin.cix.co.uk
---------------------------------------------------------------------
To unsubscribe, e-mail: ooo-users-unsubscribe@incubator.apache.org
For additional commands, e-mail: ooo-users-help@incubator.apache.org
Re: OO spreadsheet question
Posted by Brian Barker <b....@btinternet.com>.
At 11:02 02/05/2012 -0400, Dan Lewis wrote:
>Frustrating isn't it?
>
>Create a column to the right of each column of numbers. (for
>example, Column C has numbers in it beginning with cell C3). In cell
>D3 enter this formula including =:
> =VALUE(C3).
>Tap the enter key. Now cell D3 has C3's number as a value. It also
>has a box around it with a little square at the bottom right of the box.
Er, no: D4 is now highlighted (and empty), not D3.
>Bring the cursor over it, and the cursor will turn to a +. Drag down
>column D to the row containing the last number in column C. Do this
>for each column of numbers. Then delete the columns of numbers with
>a ' in front of all its numbers.
Close, but no cigar. Once you delete the original values, the
formulae can no longer produce the required results! If you delete
the values in column C, your D values will become zeroes; if instead
you delete column C itself, the D values will be rendered as #REF! .
That could be just as frustrating.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: ooo-users-unsubscribe@incubator.apache.org
For additional commands, e-mail: ooo-users-help@incubator.apache.org
Re: OO spreadsheet question
Posted by Dan Lewis <el...@gmail.com>.
On Wed, 2012-05-02 at 10:25 -0400, Bill Dillinger wrote:
> When I download my bank statements they come as .csv files. When I
> open these in the Open Office spreadsheet the fields with numbers come
> in as characters and the formula window shows them to be preceded by
> a quote, ', mark. I have looked through 'help' but have not found a way
> to remove the leading quote or to reformat the column to numbers so
> they can be used in calculations.
>
> I would appreciate it if someone would tell me how to change these columns
> of characters into numbers.
>
Frustrating isn't it?
Create a column to the right of each column of numbers. (for
example, Column C has numbers in it beginning with cell C3). In cell D3
enter this formula including =:
=VALUE(C3). Tap the enter key. Now cell D3 has C3's number as a
value. It also has a box around it with a little square at the bottom
right of the box. Bring the cursor over it, and the cursor will turn to
a +. Drag down column D to the row containing the last number in column
C. Do this for each column of numbers. Then delete the columns of
numbers with a ' in front of all its numbers.
There may be a simpler way to do this that I have forgotten from the
past.
--Dan
---------------------------------------------------------------------
To unsubscribe, e-mail: ooo-users-unsubscribe@incubator.apache.org
For additional commands, e-mail: ooo-users-help@incubator.apache.org