This may go on the Tech Nerd thread, but since I'm grumpy about it, here it is:
One of the many meaningless tasks I perform is to upload certain university data to this Clearinghouse outfit so they can Get Meaningful Insights for universities and Provide Important Metrics That Definitely Matter And Amount To Things. This data has to be formatted in a very specific way, and running the reports off of our server doesn't quite cut it. It needs some formatting and things added, changed, etc.
Excel is an ideal tool for this, but once I realized I didn't want to memorize the steps, read instructions every semester, or basically be bothered with it as much as possible, I wrote a Python script that takes my database report and spits out a correctly-formatted .csv file I can then upload. The whole thing should take less than a couple of minutes, boom, done.
Apparently in November, the Clearinghouse updated their platform, and it no longer accepts the standard .csv format where null values between delimiters (commas) don't matter. They switched to a stricter file parsing protocol where it matters how many commas the header has....I think it's called "fixed-header format" or something like that. What this means, in English, is that when you have a spreadsheet with a bunch of columns, but you insert a header row with only a few columns, when Excel (or Python, or just about anything) saves to a .csv file, it treats the first row as if it has as many columns as the rest of the rows, and in a standard text editor, you'd see a bunch of commas with nothing between them, indicating empty values. So for example, if your spreadsheet has 10 columns, but your header row (1st row) only has a couple of things, it looks like this: Column 1, Column 2,,,,,,,, <--- All those extra commas are created by default because the rest of the document utilizes that space, and virtually all parsing systems are cool with that. Now, this dumb thing has to be: Column 1, Column 2 <--- for the first row only, and it fails the upload if there's any more commas than that.
1) This is dumb. There's been standard parsing for .csv files forever, and it's completely unnecessary to make stricter parsing standards for a header. It doesn't hurt anything, and nearly the entire world operates on the same protocol, and so did their system, until just recently.
2) Yes, I can open the files in a text editor and manually edit them. But that defeats the whole point of automating this. And to be clear, the files are quite a bit more complicated than the small example I gave. Sitting here straining over their newly required header format on their website to figure out how many commas I need, and where, is annoying.
3) Off the top of my head, I don't know how to fix my script. Doesn't matter what I do in my coding, once Python (or anything) spits out a .csv, it creates all those commas automatically. I'm sure there's a fix, but I'm not enough of a programmer to know what it is.
Tryna do one little thing that I actually went to school for, and these morons are throwing a wrench in the works.
