Not seeing how to have CSV values default to text only

English
#1

I’ve been using LibreOffice to read CSV files, because I like its Text Import options (I have to deal with varying kinds of delimiters and formatting), but I just am not seeing how to:

A: Get values to show as text only, not scientific numbers, not having leading zeros chopped off, etc.
B: How to have x-separated files (like .csv, .tsv. psv, .scsv, etc) always use text only for all values, including headers.

Nothing I set in the Text Import options make this happen, and for certain kinds of values, big numbers that start with zeros, nothing I do anywhere gets them to just show the whole number like I see when I open the file in a text editor. Since our user team hasn’t found yet in Excel how to do this, I’m hoping LibreOffice allows at least A, but ultimately both A and B. If it doesn’t, is there a feature request already made somewhere that I can look at and vote on?

Also, why is the initial box that opens when I open a .csv file named “Text Import” when there’s apparently no way to get CSV values to show as just text? :laughing: This seems silly.

If we can’t figure out how to get text only values, then I’ll need to look at a different app to read CSV files with.

#2

About A. Do you try this?

TextFromCSV

About B. What about a macro that will import any file with preset filter options?

4 Likes
#3

Why do you import text into an arithmetic calculator without evaluating numeric text? Seems silly.

2 Likes
#4

Certain jobs require IT to become a clearinghouse for CSVs. I can sympathize with needing to treat numbers as text in these cases. Usually, when you re-export as CSV everything resolves itself. But in between you may want text. Classic example: U.S. grade levels are K,1,2,3,4,… So for grade levels to act the same they may all need to be text to accommodate the K. The obvious, use of 0 for Kindergarten, is basically unheard of.

1 Like
#6

See also: tdf#150652

1 Like
#7

Such a job would justify sprecialized csv software. A spreadsheet makes no sense.

#8

The Column type drop-down is grayed out… why? It’s a .csv file.

image

Even when I uncheck Semicolon, check Trim spaces, check Detect Special numbers, exactly as you have it. The file is definitely CSV, it won’t work unless Comma is checked.

#9

Your answer is marked as Solved, but I’m having a hard time seeing your solution on Filter Options - Apache OpenOffice Wiki

Can you explain just what I’m supposed to do to get all values including headers to show as just text for myself and my users?

I’ve suggested my users use ModernCSV, which just displays CSV files formatted, with text-only values the way they would appear in a flat text editor like TextEdit or Notepad; however, I’d prefer not to have yet another app for yet another dedicated thing on the computer.

I feel like with your link I need to take hours or days out or a course to learn a bunch of things to understand many contexts to just get to an answer of what to click to make this happen.

#10

Usually, when you re-export as CSV everything resolves itself.

This typically doesn’t happen with my users; they open the SV file in whatever editor they use (typically Excel), values are modified and then saved that way. Zeros are stripped from the beginning of numbers (this caused an item to be shipped to the wrong state), long numbers are converted to numbers unreadable by some machines, dates are converted to something parsed incorrectly later.

I’m very glad you understand the issue well, but I wish all cases worked as well.

LibreOffice should have this functionality already (to just display and use text values), and I’m just scratching my head on why it’s so difficult to figure out how to do it here. Maybe OpenOffice is more intuitive in this case? I’d really prefer not to have to install yet another app just for a certain common office file type.

#11

Why are double newlines here stripped to single newlines anyway? It makes things harder to read.

#13

All you need to understand is how spreadsheets applications actually do work since 1980 and what that means when importing plain text.

SelectColumns

  1. Click the grey corner in the circle in order to select all the columns in the preview grid.
  2. Choose “Text” as column type.
2 Likes
#14

I’m sorry, I didn’t realize I had to click all of the columns to get the Column Type field to enable…
— double newline —
So, the problem now is that it takes three extra clicks each file open for users to do this… and I know they’ll be aggravated at more hoops to jump through; so, can they not set something in settings to have this apply to all columns of a .csv upon opening?

Like, have your steps just be auto-selected when the Text Import box shows, for *SV files.

#15

Such a job would justify sprecialized csv software. A spreadsheet makes no sense.

— double newline —

Not necessarily… I’ve worked for maybe a dozen companies that go through dozens, hundreds, thousands, up to millions of *SV files per day, often with one to dozens of people editing them manually. My current job has a handful of users manually editing files at times for minor corrections that they have no UI for yet… and that’s why I’m here. They inevitably get some file that improperly formats a column of values, and something gets messed up after that. Sometimes they can adjust for that, sometimes they can’t.
— double newline —
The best solution I can think of is for the app to have a setting for *SV files to auto-format all values as text, but I’m not seeing how to do that yet. Is a macro required or is there already a setting?

#16

I’ve understood how spreadsheet applications have worked for over 10 years, but that doesn’t mean I understand exactly how LibreOffice handles its settings for formatting them for the user… that’s very specific to LibreOffice. Do all spreadsheet applications work exactly the way you’re showing here? I don’t know, I honestly don’t use Excel often, haven’t had to worry about text formatting in the situations I have had to work with it (rarely ever with CSV fortunately), nor have I used every spreadsheet application since the 1980s. I don’t currently have or need Excel installed, even though our users use it.
— double newline —
I’m sorry that I initially missed the exact steps to making it work in Calc, but really, just a setting to have it default to that would be better than telling our users they have to do three more steps per file opening to avoid installing something that just does it automatically like ModernCSV.

#17

I will not write any such macro. Please do the 2 extra clicks.

1 Like
#19

Do they know how will be aggravated you and all of us who took part in this discussion? Let’s compare efforts - they need to be clicked three times. You need to come up with, write and debug a macro that will open CSV files without text conversion… I bet it will be a lot more than three clicks.
As already written

I’ll add not again - just search here for the word CSV. Do you see how many times this topic has been discussed? Approximately a quarter of these questions received solutions in the form of macro code.

Take, for example, this answer. In the first code snippet, the third line is the same filter parameters that you don’t know what to do with

Notice the two commas in a row on that line - that’s the fifth token. There it is empty, all columns are imported as if they were marked with Standard (Calc will try to determine the data types on its own and “mess up” your text values.)
In order for the fields to be read as text, the line should look like this:

"59/44,34,76,1,1/2/2/2/3/2/4/2/5/2/6/2,1033,true,false,true,false"
This means: 1st column - Text, 2nd column - Text, 3rd column - Text,… 6th column - Text.

Do your files have more than six columns? No problem, list them all and give each one a type of 2. Make enough of them so that no field is left marked as Standard.

1/2/2/2/3/2/4/2/5/2/6/2/7/2/8/2/9/2/10/2/11/2/12/2/13/2/14/2/15/2/16/2/17/2/18/2/19/2/20/2/21/2/22/2/23/2/24/2/25/2/26/2/27/2/28/2/29/2/30/2/31/2/32/2/33/2/34/2/35/2/36/2/37/2/38/2/39/2/40/2/41/2/42/2/43/2/44/2/45/2/46/2/47/2/48/2/49/2/50/2

Add a macro call to the file menu - have users use File-Open CSV as TEXT to run this macro.
Now they want the files they click in Explorer to open without distortion? Well, think about how to change the code and what Office event to hang it on… Oh, they don’t always need text, sometimes the CSV file needs to be opened differently? How else to change the code so that these lazy idiots who can’t master three mouse clicks are finally happy?

1 Like
#22

Note that list is outdated. Up-to-date options are available in the current LibreOffice help CSV Filter parameters .

#23

I think maybe people are missing what I’m saying about this…
– double newline –
When I give the managers the solution recommended here, they say “Well, we can train the users to do that, but that doesn’t mean they won’t forget, and then we get orders messed up. Isn’t there a better solution?”
– double newline –
See what I’m saying? This isn’t all coming from me… there’s a realistic business need for it to be easier, like a setting in the app to have all .csv files opened with all text values (making the repetitive steps unnecessary). So there’s no solutions in settings, but are you saying that solution is also not worth passing to the devs? Or is this not the place for that, or to bring awareness to it? To me it doesn’t seem like a difficult solution to implement.
– double newline –
When it comes to macros, I also have to consider how feasible it would be for the managers to implement… all I can do is suggest it to them. They are trying LibreOffice because some already have OpenOffice (which means the security team already has some tangential trust in related new app install)… maybe it’s hard to imagine why I’ve gotten so nuanced and persistent here unless you’ve been in this situation, which is the case in a large percentage of companies here in the US. Maybe in other countries things are more lax. Note that I don’t expect you to understand nor speak for nor to even about any of my managers, I’m just describing why I’m saying what I am. Please also don’t take anything personal here nor is there any reason to be defensive about anything.
– double newline –
Anyway, I’ve already suggested the steps to them, they complained (as I stated), and the next step is to suggest the macros (which I know they’ll complain more about). I know you have zero control over that, but that should be obvious.
– double newline –
Regardless, a setting to have this all happen automatically for *SV file types is the ultimate solution, though I wonder if I would get complaints or resistance here about that.

#30

FWIW, I’ve created an extension that should help importing CSVs as text only (Import CSV as text extension).
The extension was published to-day, here: Import CSV As Text » Extensions
HTH,

#33

This is what I was talking about:

15 modules, 3.5 thousand lines of code with comments, two dialogs, configuration files… And all this so that someone does not forget to click the mouse three times… At the same time, the problem of opening a CSV file by clicking from Explorer remains open.

@jfn thank you, this is a worthy realization of the strange desire of incomprehensible people.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.