All you need to understand is how spreadsheets applications actually do work since 1980 and what that means when importing plain text.
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.
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?
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.
I will not write any such macro. Please do the 2 extra clicks.
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?
Note that list is outdated. Up-to-date options are available in the current LibreOffice help CSV Filter parameters .
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.
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,
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.
I’d say this thread has already a solution given by @JohnSUN
If you set the column-type to text the contents stay text. (This is also the place where you can choose to recognize foreign numbers 3.141 instead of 3,141 )
It is obviously named by the source: text-based files, not by the destination or it would be labeled spreadsheet import.
OK, so if it’s obviously being named by the files being text-based, it seems ironic that there’s no apparent easy/intuitive solution to actually make them display and be used as text when the file’s ultimately opened. This solution should be as front-and-center as the the title of “Text Import”… there should be 1 checkbox named “Display and treat all values as just text”. See what I’m saying?
Yep. The usual “my needs as default” request. I switched at work to OpenOffice as I was annoyed by Excel (2003 ??), wich just imported files assuming the formatting and importing wrong. I am glad Open- and LibreOffice ask me nicely… (Sometimes I find some structures we developed to prepare files for Excel even now in our archives…)
.
But as I am no Developer for Aoo/LO it is not important what I read or think. Requests for “enhancements” go through bugzilla, but you should accept your opinion may not be supported by everybody. There is concern on usability of the import dialogue, and as tl;dr is a mayor issue now manuals will not help…
Virtually nobody wants to import numeric text into an arithmetic calculator. If you want to do so, just do the 2 extra clicks or write your own macro program.
Yes, I see.
…and one more checkbox to recognize numbers as numbers. And one more checkbox to recognize records with a dot as numbers in the American format. And three more checkboxes recognize dates as day/month/year, as month/day/year, as year/month/day. And a small bunch of checkboxes for other options… You say that clicking one checkbox will be convenient for them - it’s only one click instead of three. But you forget that they also have to click the rest of the checkboxes that are checked to reset them… And instead of three clicks, the user clicks a dozen times… Be careful about granting the wishes of lazy users!
Why would people start requesting a checkbox for each data type just because a checkbox shows to display all data as just text? Those are two different levels of context (one broad, one specific). “just text” applies to all datatypes regardless of type (it ignores trying to figure out any type and just sets them to the base readable type as they would show in a flat text editor)… if you consider here that “text” is a datatype, well guess what, it’s the default datatype when there’s no formatting at all! Just the way you’d see in Notepad or TextEdit or whatever Linux flat text editor you prefer. It can’t be confused for any other datatype because it’s unformatted. This especially applies to CSV.
– double newline –
Also, having a bunch of checkboxes to show everything as a certain data type makes no sense when the app by default already processes all values by the scanned datatype… your fear is based on a redundancy, just applied to all, and in the case of non-numeric types in the file, applied improperly! Nobody would want that anyway! In what cases would a numeric value not be processed by LibreOffice as something other than a number? That would sound like a bug. Would it rewrite a number as a date? Would the user want a date rewritten as a number? In which format, Unix time, or something that uses minutes? I don’t see how your fear would lead to anything but chaos for users (an actual reason to avoid implementing a datatype-based checkbox), whereas my suggestion fixes chaos users are already experiencing, because plain text just displays everything as-is with no formatting!
– double newline –
You’re mixing up a checkbox that would prevent formatting (agnostic or no datatypes at all) with checkboxes that would force formatting (opinionated datatyping). Please, I hope you can understand the difference here… my suggestion would not lead to what you’re suggesting being implemented.
– double newline –
I don’t know what else to say here. I think I found something that lets Excel be configured to open .csv files as showing all values as plain text, that isn’t a macro (it’s an import, still more steps than just a configuration), which would mean Excel is still ahead of LibreOffice in this regard.
– double newline –
As a programmer myself, I see no logical reason to just not have a setting to have *.*sv files open in Calc with plain text values. That does not seem hard to implement. Seriously.
They’re not lazy; just sometimes people forget something. Also, you’re mixing up laziness with the desire for efficiency. Should the iPhone never have existed because it made things easier for people? How lazy people are to use smartphones instead of laptops or write letters! We should really just throw away all technology and do everything in the forest.
On a smart phone you would install one app for the csv of Company A, another app for the csv of Company B, a third app for the csv of company C and each app would be updated automatically when its owner changes the csv flavour. Spreadsheet applications are generic software tools originating from (and still compatible with) the the killer apps of the 80ies and 90ies of the past century.
With a minimum of effort Calc can import thousands of different csv flavours correctly. And no, importing text only into an arithmetic calculator makes absolutely no sense, nevertheless it is possible with one additional click and a listbox selection.
– double newline –
Our users don’t view CSV files on phones… and most CSV users I’ve worked with opened CSV files on desktop computers or laptops. My question is within the context of LibreOffice for Mac/Windows. Maybe I should have stated that, but I thought LibreOffice’s main usage was on those operating systems.
– double newline –
– double newline –
That’s nice, but that doesn’t mean the popular modern ones easily show values as plain text when the file is opened. Old apps or their compatibility are irrelevant here. My clients are looking for the easiest way to open CSV files with plain-text-only values, and the easiest way I can think of is to have a setting in the app to have this happen with *.*sv files. I don’t know why there’s so much resistance to that here. It doesn’t seem hard to implement, and I’m saying that as a programmer myself.
– double newline –
– double newline –
So what? I don’t see your point here. Who cares how many flavors it can import? That’s not the point. Also, “correctly” in our case means “without modifying values”… and I’m not seeing how to have the app do that automatically for the user upon double-click of the file, without adding something like 3 extra steps each file open that each worker may or may not remember each time.
– double newline –
– double newline –
This is a strange statement considering the app already allows the user to do that… but using the extra-3-steps-each-file-open method suggested here. I’ve already explained why that can be a problem, I don’t understand why logically that should be the best solution, and the best feasible solution I can think of has been suggested. I don’t know why there’s such resistance to it. Maybe difficulty understanding how it would work? I’m having no difficulty with that. All it takes is someone stepping up and making the change happen. As a programmer this does not look like a difficult problem, and an alternate solution can be a toggle on the Import screen, as I’ve also suggested. I also don’t know why anyone would resist that nor think that would create new out-of-context problems.
– double newline –
I feel like some people here are getting to detail D and forgetting detail A, or getting to detail G and forgetting detail C. Maybe you haven’t been in this situation enough to really understand why so many people are asking for this to be easier, even as easy as I’ve suggested.
– double newline –
All it takes is someone stepping up and doing it.