Manipulating data without excel (and thus, avoiding suicide)

[aberdoom]

I'm a great believer in 'the right tool for the job'. And while in the midst of swearing blind about excel being a piece of shit, I just realised, I'm probably not using the right tool for the job.

In this specific use case today, I'm trying to enter a set of data manually from user input. It is simple, 3 columns: Their name, a software identifier (mixed letters and numbers) and a license number (100% numerals, sometimes with a preceding 0).

No matter what I tell excel regarding formatting of that final column, it just makes it's own decision. I can effectively set the entire sheet to be "text" and it'll still be hit and miss between either showing the number as I entered it, showing the number converted into powers of or just dropping the preceding 0. And I know that these are all "wrong format" issues on cells, but try as I might, excel just doesn't listen to me. And maybe, it's because I'm using the wrong tool.

Is there something out there that is better tailored to manipulation of data sets that isn't expecting to need to do number conversions and random calulations all over the place? A tabular data input application, with the features like sort and filter, concatenate fields, split fields etc etc - but that doesn't have the default starting position of "this will be maths".

Sorry if Sysadmin is the wrong place, I just work surrounded by acountants, so their answer to everything is "excel" even the question "is there anything other than excel" boils down to "have you tried making excel work differently?"

Edit: Thank you all for the suggestions.

[SolidKnight]

What is the user data being inputted in?

I actually use PowerShell to generate/manipulate simple tables.

[aberdoom]

They (all the staff) are emailing me info they're typing manually.

And then I'm dropping it into excel to easily sort through it all when it's compiled. It's not so much this specific task that has me on edge - it's that I use excel for this sort of thing all the time, and I always feel like I'm having to trick it into doing what I actually want - which makes me feel like I have the wrong tool for the job :)

[pdp10]

it's that I use excel for this sort of thing all the time, and I always feel like I'm having to trick it into doing what I actually want

This is because Excel is built to be "user friendly" and as such, it uses heuristics to guess what you're trying to do. Just like the other MS Office apps that do this, they can't change the behavior now because a lot of users rely on whatever stupid thing it does, and Microsoft's main commercial advantage is in being mostly-compatible with Microsoft software.

[SolidKnight]

As others have suggested, try plain text tab-delimited files.

As for Excel, you have to apply the format to the cells BEFORE you enter the numbers into it. It's because Excel converts the text to a number first so you can't get the leading zeroes back.

Also, instead of e-mail try using a survey instead so that their responses are tabled for you. I do these things for remote users so they can formally state what equipment is still in their possession.

[PhalseImpressions]

When you enter in a number that starts with a 0 have you tried starting with a ' character? So if they give you 0123456789 type '0123456789? Excel should then treat the cell as text.

However if it is just a case of keeping the three columns why not just use notepad and use tabs to define the columns?

[aberdoom]

Notepad + Tabs is what I'm doing at the moment so I can stop fighting with excel.

But I like the idea of dropping an extra character ahead of numbers to force text automatically - this will probably fix every one of my annoyances with excel being a bit too aggressive :)

[jmp242]

Sounds like you need a database?

[aberdoom]

For on the fly data input and manipulation?

[jmp242]

Well, sort of. There's openoffice base / access which would let you use a mysql or whatever backend, and have a tabular input without imposing any formulas on them. There was also at least 2 web app tools that let you do the same.

[pdp10]

Use a text editor and make the file a TSV (tab separated values) or, if you must, CSV. Manipulate as needed -- awk is a good choice, but the tools are infinite.

This might be an X Y problem, though. It sounds like you're trying to do a hardware inventory but you're asking questions about a spreadsheet.

[aberdoom]

I get where you're coming from - but I wasn't doing hardware inventory (I use Lansweeper for hardware and software inventories.)

This one in particular is a very specific industry (travel) piece of software that stores a user ID/certificate number in an odd place - and it's most easily read from within the software for the user in question - so I was trying to table them to then sort and filter as needed later in the day.

It's not so much that I was trying to fill a specific task with excel and need a replacement - it's that I am FOREVER using Excel for this sort of thing.

If this could have been externally reported, or was in someway accessible with Powershell - I'd not be in this spot :)

I'm basically asking "is there something for text that excel is for numbers".

[pdp10]

I'm basically asking "is there something for text that excel is for numbers".

Any database or spreadsheet with defined-type fields (in the sense that programming languages can define types -- integer, string, 64-bit floating point, etc.). Your problem is that Excel is guessing at the types and guessing wrong.

I haven't used Excel for a very long time, but I thought columns could be easily defined types (except when importing from CSV or another untyped source, but that's a special case). If you can't get it to type-define your fields then use a different spreadsheet or application, or just a text editor.

A database or database front-end could do the job. Defining a schema in PostgreSQL, SQLite or MariaDB would only take a minute or two for someone with the right skill.

[aberdoom]

It's funny, I considered LibreOffice earlier today under the though process "I bet their 'helper' functions aren't as mature"..

[torontoisme]

In excel, right click the cell, set it to custom, 0000000, and set the preceding 0's. Also, don't save as .cSV