Calc method to change whole column cell content

users
#1

I recently began to use the spreadsheet downloads from one of my
credit unions as it is so far superior to the home-grown method I have
been using it's not even funny.

(I know, way late... Cries of "luddite" in the background, etc.)

However, in the .csv file they send, the dollar transactions are all
given as text entries with ($xxx.xx) for negative entries and $xxx.xx
for positive entries. I don't see a way inside calc either to simply
convert that to a currency amount or to strip out the leading
apostrophe/quote mark that inhibits proper evaluation of the field.

I've also run into this with other numeric imports - that leading
quote is a pain that doesn't show in the .csv file and _only_ appears
inside calc.

What I've been doing is loading the .csv file into a text editor,
change those fields and then reload into calc. This is terribly
cumbersome.

I also note that there is no Currency option for columns in the import
wizard. The date fields I can do, but not these.

Finally, when I'm running writer and I tell it to load a recently used
.csv file, it loads that file into the writer, not into calc. I
couldn't find an option for this. My file manager understands that
.csv files are to be opened by calc, why not LO itself?

Any suggestions?

I'm running LO 5.1.2 (just downloaded :slight_smile: on Xubuntu 14.04.4 with the
Wily kernel (4.2).

Thanks.
MR

#2

Language = English(USA)
[X] Detect Special Numbers

#3

Method 1 - Using Value()
Use value() function to convert a text string into a number or date.
Copy the result; paste special as number or date.

#4

Method 3 - Text to Columns
Select a column of cells
Call Data>Text to Columns...
Choose tab as delimiter and hit [OK]

All 3 methods require that your locale setting matches with the data to
be converted.
($1,2345.98) requires English(USA) because of the $, the comma as
thousands separator and the decimal point.
(£1,2345.98) requires English(UK) because of the £, the comma as
thousands separator and the decimal point.
(€1.2345,98) requires German or French because of the € the point as
thousands separator and the decimal comma.

menu:Tools>Options>LanguageSettings>Locale (second option) determines
the cultual context of all text-to-number conversions.

Better you do not import numeric text in the first place. Wrong dates
(2/3/1999 vs 3/2/1999) and wrong numbers (1.234 vs 1,234) can be very
difficult to convert. Simply check the right locale in the import dialog
together with the "detect special numbers" option.

#5

Hi :slight_smile:
CSV format can be easily edited in normal text-editors before opening in
Calc. Sometimes its easier to use a simpler tool to do a part of the job
rather than use a complicated route in a heavier tool such as Calc.
Regards from
Tom :slight_smile:

Method 1 - Using Value()
Use value() function to convert a text string into a number or date.
Copy the result; paste special as number or date.
---
Method 2 - Search & Replace
Select (highlight) a cell range then:
Using Search & Replace (Ctrl+H):
Search for = .*
Replace = &
Under Other Options:
Y = Current selection only
Y = Regular expressions
Click Replace All

Method 3 - Text to Columns
Select a column of cells
Call Data>Text to Columns...
Choose tab as delimiter and hit [OK]

All 3 methods require that your locale setting matches with the data to
be converted.
($1,2345.98) requires English(USA) because of the $, the comma as
thousands separator and the decimal point.
(£1,2345.98) requires English(UK) because of the £, the comma as
thousands separator and the decimal point.
(€1.2345,98) requires German or French because of the € the point as
thousands separator and the decimal comma.

menu:Tools>Options>LanguageSettings>Locale (second option) determines
the cultual context of all text-to-number conversions.

Better you do not import numeric text in the first place. Wrong dates
(2/3/1999 vs 3/2/1999) and wrong numbers (1.234 vs 1,234) can be very
difficult to convert. Simply check the right locale in the import dialog
together with the "detect special numbers" option.

#6

Really? And how do you convert (£1,234.98) into -1234.98 in columns 3,
17 and 23 across thousands of rows using a text editor? Are YOU able to
do this actually?
Don't you think that checking "English(UK)" and "Interprete Special
Numbers" is by far easier?

#7

And there *are* text editors (even on Windows) that use regular expressions and thus can perform the transformation relatively painlessly.

All the best!

#8

Hi :slight_smile:
Yes, as it happens. It would sweep up negatives in other columns and that
might be a bonus. With a text-editor it's easier to remove the "s from
around numbers too. A whole load of tidying-up can be done to convert
non-standardised Csv files into something that works better in Calc or
Excel or whatever.

Each of us has a different way of doing things and it's good to have such a
wide range of choices so that we can do things in a fairly comfortable,
or/and in a fairly familiar way or choose to learn new skills while doing
something. OpenSource generally gives people freedom to choose their own
way of doing things, and that can be seen as a negative or a positive.
Regards from
Tom :slight_smile:

#9

Writer is one of them. Calc supports regular expressions as well.

I have tested dozends of different csv flavours with all types of text,
numbers and subtypes of numbers. With one exception they imported all
well. The exception was $(123.98) for a negative amount which could be
fixed with a simple regex replacing "^\$\(" with "($". Calc is an
excellent csv import tool which imports correct cell values where other
spreadsheet apps import text and try to interprete the text on the fly.

#10

If you would read the initial posts of the topics you are replying to ...

MRZenwiz:

What I've been doing is loading the .csv file into a text editor,
change those fields and then reload into calc. This is terribly
cumbersome.

However, in the .csv file they send, the dollar transactions are all
given as text entries with ($xxx.xx) for negative entries and $xxx.xx
for positive entries.

which is perfectly well handled by Calc's csv importer. 90% of all text
import trouble can be answered with:
1. Choose the right locale which is English(USA) in this particular case
because of the $ currency, the point decimals and propably there are
also M/D/Y dates in the file.
2. Always check "interprete special numbers". A special number is
anything beyond digits with decimal separator. This option is obsolete
since it should be always on. You can mark individual columns as text
when you do not want them to be interpreted as special numbers. But once
the option has been checked it remains checked for subsequent csv imports.

#11

I regularly use UltraEdit (UE) and you can edit files beyond 4GB with
millions of lines and it uses regular expressions. This would be a quite
easy conversion in UE. It has a column mode editing ability so you can
edit only in selected columns as well.

*Henry*