[Solved] Open CSV-files Calc

Help with installation and general system troubleshooting questions concerning the office suite LibreOffice.

[Solved] Open CSV-files Calc

Postby Albireo » Fri Jul 22, 2016 2:05 pm

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?
Last edited by MrProgrammer on Wed Oct 28, 2020 7:34 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OOo 4.1.X on Windows XP, Win7, 10
Albireo
 
Posts: 59
Joined: Wed Apr 15, 2009 12:05 pm

Re: Open CSV-files Calc

Postby Villeroy » Fri Jul 22, 2016 2:52 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 29294
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Open CSV-files Calc

Postby floris v » Fri Jul 22, 2016 2:59 pm

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.
AOO 4.1.6 op Linux Mint
If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar.
Nederlandstalig forum
User avatar
floris v
Volunteer
 
Posts: 4231
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

Re: Open CSV-files Calc

Postby Albireo » Fri Jul 22, 2016 4:04 pm

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.)
OOo 4.1.X on Windows XP, Win7, 10
Albireo
 
Posts: 59
Joined: Wed Apr 15, 2009 12:05 pm

Re: Open CSV-files Calc

Postby Villeroy » Fri Jul 22, 2016 4:24 pm

LibreOffice Calc does what you want. It formats text cells as text.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 29294
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Open CSV-files Calc

Postby Albireo » Fri Jul 22, 2016 4:52 pm

Villeroy wrote: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)
OOo 4.1.X on Windows XP, Win7, 10
Albireo
 
Posts: 59
Joined: Wed Apr 15, 2009 12:05 pm

Re: Open CSV-files Calc

Postby jrkrideau » Fri Jul 22, 2016 6:26 pm

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.
LibreOffice 6.4.3.2; Ubuntu 20.04
jrkrideau
Volunteer
 
Posts: 3806
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Open CSV-files Calc

Postby MrProgrammer » Fri Jul 22, 2016 7:36 pm

Albireo wrote: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.

jrkrideau wrote: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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3910
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Open CSV-files Calc

Postby Albireo » Fri Jul 22, 2016 10:47 pm

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.)
OOo 4.1.X on Windows XP, Win7, 10
Albireo
 
Posts: 59
Joined: Wed Apr 15, 2009 12:05 pm

Re: Open CSV-files Calc

Postby Villeroy » Fri Jul 22, 2016 11:09 pm

Albireo wrote: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.
Albireo wrote: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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 29294
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Open CSV-files Calc

Postby Albireo » Sat Jul 23, 2016 12:02 am

Villeroy wrote:...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).
OOo 4.1.X on Windows XP, Win7, 10
Albireo
 
Posts: 59
Joined: Wed Apr 15, 2009 12:05 pm

Re: Open CSV-files Calc

Postby Villeroy » Sat Jul 23, 2016 12:33 am

Albireo wrote:
Villeroy wrote:...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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 29294
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Open CSV-files Calc

Postby Albireo » Sat Jul 23, 2016 11:39 pm

Villeroy wrote:....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
OOo 4.1.X on Windows XP, Win7, 10
Albireo
 
Posts: 59
Joined: Wed Apr 15, 2009 12:05 pm

Re: Open CSV-files Calc

Postby MrProgrammer » Sun Jul 24, 2016 12:45 am

Albireo wrote: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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3910
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Open CSV-files Calc

Postby Villeroy » Sun Jul 24, 2016 1:10 am

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.
Attachments
numerals.ods
Numbers imported from csv as text with LibreOffice
(7.85 KiB) Downloaded 153 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 29294
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Open CSV-files Calc

Postby Albireo » Sun Jul 24, 2016 10:50 am

Villeroy wrote: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?)
OOo 4.1.X on Windows XP, Win7, 10
Albireo
 
Posts: 59
Joined: Wed Apr 15, 2009 12:05 pm

Re: Open CSV-files Calc

Postby Villeroy » Sun Jul 24, 2016 11:04 am

The file was
Code: Select all   Expand viewCollapse view
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   Expand viewCollapse view
"1"
"2"
"3"
"4"
"5"

... and option "quoted fields as text"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 29294
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Open CSV-files Calc

Postby Albireo » Sun Jul 24, 2016 11:31 pm

But, this give another result in column "B"
Code: Select all   Expand viewCollapse view
"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)
OOo 4.1.X on Windows XP, Win7, 10
Albireo
 
Posts: 59
Joined: Wed Apr 15, 2009 12:05 pm

Re: Open CSV-files Calc

Postby Villeroy » Mon Jul 25, 2016 11:49 am

Format the active sheet's used area to number format "@" (Text) using the first cell's locale:
Code: Select all   Expand viewCollapse view
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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 29294
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to LibreOffice

Who is online

Users browsing this forum: No registered users and 1 guest