[Solved] txt/csv file problems in 3.1.1

[coakley]

I create spreadsheets from fixed width ASCII text files from a server of virtually antique origins. I have been doing this for many years with a variety of spreadsheet programs from Lotus 123 on DOS to various Excels to Star Office and finally OpenOffice. I loaded a machine with Ubuntu 9.10 and OO 3.1.1 and the text import dialog box no longer works correctly. I have done this as a File/Open/txt,csv and as Insert/Sheet from File/txt,csv and the problem is identical. In the decimal number fields (which are actually dollar amounts) it adds a ' to the beginning and then does what appears to be a center alignment of the data instead of the usual flush right.

I can make the display correct with a little extra work but I get incorrect results when the data is used in formulas. If there is a way around this, I would love to know of it but I suspect this is not likely.

Was this change intentional for some strange reason or was this an unintentional side effect of another change that perhaps will be corrected?

I guess I can always revert to a prior version but that's not a very sound long term solution.

[Robert Tucker]

See:

http://user.services.openoffice.org/en/ ... =9&t=23070
http://user.services.openoffice.org/en/ ... =9&t=22539

[Villeroy]

It is impossible to give any advice without actually seeing a line of concrete text data.
I'm not aware of any csv import changes during the last 2 years.

I can not even find any way to import fields of fixed lenght as text unless I explicitly declare the respective fields as text.
The first 5 chars in the following table never import as text unless I mark this column as "Text" in the import dialog.
12345abc
54321xyz
Like any other spreasheet, Calc marks numeric text with a leading apostrophe in order to prevent all evaluation since this is exactly what you want when you declare it as text. The apostroph is not part of the text value.

In Calc it is extremely easy to convert text to number. Just replace search expression .+ with replace expression & and check option "Regular expression".

[vasa1]

....
In Calc it is extremely easy to convert text to number. Just replace search expression .+ with replace expression & and check option "Regular expression".

Hi Villeroy: Please could you confirm the parts in bold?

Is the first (search expression) a period followed by a plus sign without any intervening space?

And I don't understand the replace expression, &. This character is not present on my keyboard

[Villeroy]

Re: Convert mixed string to text
Basically it re-enters everything, replacing any content with itself. The result depends on the context (number format locale and number format being "@" or any other number format).
See help about "regular expressions"

However, I would be interested in coakley*s resp0onse why he imports numbers as text and then complains about it.

[coakley]

My complaint is that when I import numbers, Calc is turning them into text. A column of simple integers (12,6,6,...) comes in correctly, but a column of prices (132.00, 98.88,...) gets an apostrophe added to its start.

I will post an example when I get on a production machine.

[Villeroy]

This is the only way how I can import numbers as text from this text file:

Code: Select all

abc123.45xyz
cde098.33fgh

Import text fields of fixed length, split at positions 3 and 9, mark the numeric column as text

The result imports column B as text tagged with the leading apostrophe to prevent numeric evaluation.

fixed_text_import2.png (24.05 KiB) Viewed 8994 times

When I do not mark the second column as text I get decimal numbers.

[coakley]

Here are some examples.

Top is the raw fixed ASCII.

Then there is the 3.0.1 version of Calc (From Linux Mint 7 - identical results with 3.0.0 and SuSE 11.1 and all prior versions of OOCalc I have used.)

Lastly we have the 3.1.1 version from Linux Mint 8 (Ubuntu 9.10 has identical results.)

With 3.1.1 the numbers have all acquired a ' at their start. Formula results based on the numbers as text are, to say the least, unpredictable. I have used Calc for quite a few years, but 3.1.1 is the first version to exhibit this "new" feature.

[Villeroy]

This line:
12345 A AA aaaaaaaaaaaaaaa 0.750 6 982.98 922.12 2
imports numbers as numbers without apostrophes, no matter if I separate it like this:
12345| A| AA| aaaaaaaaaaaaaaa| 0.750| 6| 982.98| 922.12| 2
or like this:
12345 |A |AA |aaaaaaaaaaaaaaa |0.750 |6 |982.98 |922.12 |2

The Linux distributors destroy OpenOffice.org in many different ways. They do not provide any documentation how their fork differs from the original Sun build, even when you ask them for a list.

Do you have to edit the rows and save them back to the same file format?

[RPG]

Hello

It seemes to me that the linux editions of OOo work good.

When you insert the line of Villeroy as csv in a spreadsheet then in the openings dialog you can choose the field and the kind of field you want have.

You can select with the mouse the field width and also select the kind of field you want have.

I did have to learn it how it was working.
I also choose for standard
There was a problem for the comma and point in the decimals. I choose for English US
I have also to make clear the program must search for numbers. There it was the first time for me I did use it I must learn how it works. But I think every one who needs this kind of tool can learn it in a short time.

I must say I have to try it maybe 10 times but then it works


Romke

[coakley]

To Villeroy

Thanks for looking into this.

My temporary solution has been to load an older version of OO - I used a 3.0.0 RPM from SuSE. This version works just swimmingly.

I will try building 3.1.1 from OOO download thereby bypassing the whole Debian/Ubuntu/Mint world.

[coakley]

To Romke

Unfortunately, in the text file import dialogue there is no way to indicate NUMBER. You can force it to import a number as text and sometimes I need to do that, but I know of no way to get it to stop changing my numbers to text.

[RPG]

a) open a file :import as CSV
b) charater set is unicode UTF8 or System
c) Language English USA For the decimal point and only for the input text
d) from row 1
e) separator option: fixed width
f) Detect special numbers
g) Column type Standard

Then I get back numbers with a comma instead of a decimal point

You donot tell if you need a decimal point or decimal comma but that can be a problem for you

Romke

[coakley]

Romke
Thanks Dude.

The key was "Detect Special Numbers." I'm not sure what that means and I'm not at all clear about why, but the numbers are still numbers if it's checked.

I owe you one. If you're ever in Northern Virginia, the beer's on me.

[vasa1]

....
And I don't understand the replace expression, &. This character is not present on my keyboard

Okay! It's shift+7 (ampersand, or the symbol for "and") for me. Villeroy's tip works perfectly for me.

Another somewhat unrelated tip was posted by acknak here (http://user.services.openoffice.org/en/ ... =7&t=23474), that helps get rid of non-breaking spaces in stuff copy/pasted from elsewhere into Calc.

[sergio]

After a bout a days searching I came up with this answer - perhaps this will help:

When importing csv text into calc, Calc is not able to see the numeric values of a cell as numbers but as text. So assuming that you have been able to separate all the text from the numbers then you could try these 3 steps to make the mass data be numbers and not the ( '123.456.789,00 ) ... particularly when importing bank statements in csv.

In my case the local currency setting used but the banks is : 123.456.789,00 ( you will notice the numeric separator is a " . " and the decimal is a " ," (irritating).

The following steps involve using the find & replace (assuming you know how to use that, and that you can make the criteria apply only to the columns or cells that you want)
Step one:
select the cells or columns with the numbers, (recognised by calc as text in the import) then ask find & replace to find the " . " and replace with... (don't fill it in leave blank). the result should be 123456789,00 (i.e. no " . " just the " , " remains)

Step two:
Ask find & replace to find the " , " and replace with " . " the result should be 123456789.00
This should make calc automatically assume the cell or column values as numbers

Step three:
Formate the numbers cells or columns in you local setting as a number or currency

Your data should be useful now.
Hope this helps,
Regards, Sergio

[Villeroy]

When importing csv text into calc, Calc is not able to see the numeric values of a cell as numbers but as text

You can simply switch the apllication wide locale during the import. Once you've got the right values you can switch it back (Tools>Options>LangSettings>Languages>Locale).

When you use a comma locale (1,23) and the text file to import has English decimals with points (1.23) you could also declare all the columns or selected columns as "US English".
Simply select a collection of columns in the preview or all the columns clicking on the grey top-left border. Then use the list box to choose between import modes "Standard" (as you would type into blank cells), "US English" (dot decimal and MM/DD dates) and "Text". The latter option is often required to inhibit all evaluation (literal strings 1-2, 12/04, IDs/ZIP codes with leading zero).