[Solved] Open CSV-files Calc

[Albireo]

I have a CSV file with 25,000 rows and about 70 fields.
for example the content of a small file with two fields and two rows
"0160-1";"Test"
"0001";"Test1"

When it is opened with Calk, I always want to produce text Imports and selects "Semicolon" and "Field in quotes as text"
The file opens in Calc, and everything looks good. 0160-1 and 0001 is in column A and the text in column B
But, 0001 formatted as "text." 0160-1, Test and Test1 is formatted as "number".
That means if the hyphen in 0160-1 are removed, the result will be 1601 (not 01601)
Not even if I select "Text" on the head of the string field, it works for me.

The only way I manage to get all the fields to "text fields" is to select all cells and format these to "text fields"
(but it is not so easy with so many fields - some of them, is numeric fields)

Do I wrong, or have I misunderstood something?

[Villeroy]

menu:View>Value Highlighting... [Ctrl+F8]
All values that are displayed in blue font color are numbers.
All values that are displayed in black font color are text values regardless of formatting.
Just like colors, borders, fonts and sizes, the number formatting has no effect on the actual cell value.

[floris v]

On the import window, also tick "Quoted fields as text" to format them as text - at least, that seems to work for me.

On a side note, when you have that many data, consider using a database to manage it.

[Albireo]

Thanks!
I have LO 5.1.4.2 and Win10 (Svedish version)

This is the test file (test.txt)
0160-1;Test
0001:Test1

When I tried [Ctrl + F8] - nothing happens (no colors etc.)

If I don't select "Quoted fields as text" I got the following result in Calc
A1 = 0160-1
B1 = Test
A2 = 1
B2 = Test1
No fields are formatted as Text fields

If I select "Quoted fields as text" I got the following result in Calc
A1 = 0160-1
B1 = Test
A2 = 0001
B2 = Test1

The only difference is that the A2 was now formatted as text
I want all of these field, be formatted as text.

But if leading zeros disappear, when loading the textfile or the information is changed, it's not good

(It is easier to handle the information in cells in Calc - copy and move items, etc.)

[Villeroy]

LibreOffice Calc does what you want. It formats text cells as text.

[Albireo]

LibreOffice Calc does what you want. It formats text cells as text.

but, not if I check (freely translated) "Format / Cells ..."
There are all cells formatted as "number".
Only A2 "0001" is formatted as "Text"

Now if I remove the "-" on Cell A1 (0160-1) => I got the number "1601" (not 01601 as text)

[jrkrideau]

I am not sure but I think you need to do each column (Change from Standard to Text) at least in AOO Calc. I have no idea about LibreOffice but I have the feeling Villeroy is misunderstanding the question.

Sorry Villeroy

P.S I am not getting exactly the same result you are but the result is a mixture of numeric and text in any case.

[MrProgrammer]

There are all cells formatted as "number".

Note that the Format → Cells dialog shows you the cell's formatting but it does not indicate whether the value is numeric or text. To determine the latter you must use View → Value Highlighting. You can have a numeric value in a cell formatted as text. For example, I can enter a number in a cell, then change the format to text. The cell still contains a number after the format change: =ISNUMERIC(cell) is TRUE and =ISTEXT(cell) is FALSE. You can have a text string in a cell formtted as a number; this is common when column labels are placed in row 1; =ISTEXT(cell) is TRUE and =ISNUMERIC(cell) is FALSE. Read section 2. Controlling how data is displayed in Ten concepts that every Calc user should know.

I am not sure but I think you need to do each column (Change from Standard to Text) at least in AOO Calc.

[Tutorial] Text to Columns
Albireo, to quickly set all of them read the advice about "Select all fields by …" in the fourth paragraph.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Albireo]

I see! it's more complicated than i thought.
By changing the columns from "Standard" to "Text", nothing changes.

Structur on the orignal file
"0160-1";"Test"
"0001";"Test1"

After import to Calc
A1 = 0160-1
A2 = 0001
B1 = Test
B2 = Test1

When I make the test
C1 =ISNUMERIC(A1) result FALSE
C2 =ISNUMERIC(A2) result FALSE
D1 =ISNUMERIC(A3) result FALSE
D2 =ISNUMERIC(A4) result FALSE
and
E1 =ISTEXT(A1) result TRUE
E2 =ISTEXT(A2) result TRUE
F1 =ISTEXT(B1) result TRUE
F2 =ISTEXT(B2) result TRUE
(as expected)

If I change the contents of cell A1 from "0160-1" to "01601" the result is automatic changed to 1601
and the result is automatic changed to .:
C1 =ISNUMERIC(A1) result TRUE
C2 =ISNUMERIC(A2) result FALSE
D1 =ISNUMERIC(A3) result FALSE
D2 =ISNUMERIC(A4) result FALSE
and
E1 =ISTEXT(A1) result FALSE
E2 =ISTEXT(A2) result TRUE
F1 =ISTEXT(B1) result TRUE
F2 =ISTEXT(B2) result TRUE
_____________________________________

Maybe it is impossible to handle, when a CSV file is imported?
The only way (I know now) is, direct after the CSV-file is imported, select column A and B, then "Format/Cells..." and select "text"
(it's easy to forget and the information can be changed to wrong values.)

[Villeroy]

I see! it's more complicated than i thought.
By changing the columns from "Standard" to "Text", nothing changes.

Strict separation between data and representation. Same as it ever was. Simple and safe.

Maybe it is impossible to handle, when a CSV file is imported?
The only way (I know now) is, direct after the CSV-file is imported, select column A and B, then "Format/Cells..." and select "text"
(it's easy to forget and the information can be changed to wrong values.)

Install LibreOffice. LIbreOffice Calc adds text format to all columns that have been imported as text values.

[Albireo]

...Install LibreOffice. LIbreOffice Calc adds text format to all columns that have been imported as text values.

Thanks for your time
Yes!
However, if the content of the cell is changed, also the cell format is changed as above (and I think this is my problem).

[Villeroy]

...Install LibreOffice. LIbreOffice Calc adds text format to all columns that have been imported as text values.

Thanks for your time
Yes!
However, if the content of the cell is changed, also the cell format is changed as above (and I think this is my problem).

No. Your "problem" with OpenOffice is that it imports numeric text into unformatted cells, therefore it adds the apostrophe which marks a numeric text as a text value so it remains always the same text. The apostrophe is not part of the value. In order to enter a text value starting with an apostrophe you've got to type two apostrophes. When you replace the numeric text in the unformatted column with some number, you get just that number if you forgot to type the leading apostrophe. This is your problem.
Number format text does not affect any existing value (which is a very good thing indeed). Number format "Text" affects how newly entered data are interpreted. With number format "Text", no input will be interpreted as number nor formula. With number format "Text" you can enter "0123" as a 4-digit text and =SUM(A1:B4) as a text with a leading "=".
LibreOffice Calc formats imported text values as "Text" so the uneducated users won't be worried by the leading apostrophe and the type of value will not change when you enter some numeric expression.
LibreOffice Writer has a serious problem with numeric text in table cells. For instance, it does not let you enter any "special numbers". It puts text into text table cells. When you manually format the "special numbers", it converts them to number. This is just silly, but LO users seem to accept it.

[Albireo]

....No. Your "problem" with OpenOffice is that it imports numeric text into unformatted cells...

Thanks for the problem description, but it does not solve my wishes.
I have the same problem with both OpenOffice / LibreOffice.

Is it possible to make text formatted cells and import to these?
How? (using API / Macros?)
//Jan

[MrProgrammer]

Is it possible to make text formatted cells and import to these?

Here's what I'd do in your situation.

• First: mark all the columns as Text during the Text Import dialog, as you've done.
• Second: Format → Styles and Formatting → Default → Right click → Modify → Numbers → Category → Text → OK.

Changing the Default style will prevent the "0160-1" to "01601" change in cell A1 from being interpreted as a number. Every cell modification will be accepted as entered, since you only have one style in your spreadsheet, Default, which has Category → Text. Read section 5. Understanding data entry in Ten concepts that every Calc user should know.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Villeroy]

The attached file has been imported from a text file with LibreOffice 5.1.3 and then saved without further modification.
It has numeric text without leading apostrophes and number format "Text". The numerals remain text when editing.

[Albireo]

The attached file has been imported from a text file with LibreOffice 5.1.3 and then saved without further modification.
It has numeric text without leading apostrophes and number format "Text". The numerals remain text when editing.

What content / structure had the imported text file?
How do you make the import?
(Why does it not work for me?)

[Villeroy]

The file was

Code: Select all

1
2
3
4
5

I imported this field as text values. The result is a column of text values in text formatted cells.
When you click the grey box on the top-left corner of the preview, you select all fields which can be marked as text then.

Same result with this file ...

Code: Select all

"1"
"2"
"3"
"4"
"5"

... and option "quoted fields as text"

[Albireo]

But, this give another result in column "B"

Code: Select all

"1";"01A"
"2";"02A"
"3";"03A"

The first column is the cell formated as TEXT (as I want), but column "B" is the cell formatted as "number" (standard).
But, if the value is not changed, the values look like as TEXT.

My wish had been, that even the cell been formatted as text (when the quotation mark is around the imported text)

[Villeroy]

Format the active sheet's used area to number format "@" (Text) using the first cell's locale:

Code: Select all

Sub UsedArea2NFText()
	view = ThisComponent.getCurrentController()
	nfx = ThisComponent.NumberFormats
	sh = view.getActiveSheet()
	rg = getUsedRange(sh)
	c = rg.getCellByPosition(0,0)
	nkey = c.NumberFormat
	nf = nfx.getByKey(nkey)
	lc = nf.Locale
	nkey = nfx.getStandardFormat(com.sun.star.util.NumberFormat.TEXT, lc)
	rg.NumberFormat = nkey
End Sub

Function getUsedRange(oSheet)
Dim oRg
	oRg = oSheet.createCursor()
	oRg.gotoStartOfUsedArea(False)
	oRg.gotoEndOfUsedArea(True)
	getUsedRange = oRg
End Function