Audiobus: Use your music apps together.
What is Audiobus? — Audiobus is an award-winning music app for iPhone and iPad which lets you use your other music apps together. Chain effects on your favourite synth, run the output of apps or Audio Units into an app like GarageBand or Loopy, or select a different audio interface output for each app. Route MIDI between apps — drive a synth from a MIDI sequencer, or add an arpeggiator to your MIDI keyboard — or sync with your external MIDI gear. And control your entire setup from a MIDI controller.
Download on the App StoreAudiobus is the app that makes the rest of your setup better.
Google Sheets strips leading zeroes when importing CSVs!
Now that we've seen threads about washing machines and tattoos, I thought I'd just drop this here because I'm still not done with setting up my "General IT and Software Development stories" blog.
DO NOT USE GOOGLE SHEETS FOR CRITICAL DATA!
I've just been through something that could easily have cost thousands or tens of thousands of dollars -- some lucky coincidence and one person's attention to detail have prevented that.
Basically, I've written a database export script that spits out a CSV file. The customer wanted it as XLSX and nicely formatted though, so I imported the CSV into Google Sheets (Google Drive -> "New" -> "File Upload") and then applied the necessary formatting / header row etc., and re-downloaded it as XLSX. Everything looked grand.
Customer came back to me the next day: "Our article IDs seem to be randomly missing digits!"
I thought "impossible"... it was just a straight dump from the database after all!
Turns out that Google Sheets CSV import "helpfully" drops leading zeroes from fields when it apparently "thinks" it's a number and doesn't "need" leading zeroes.
I mean, what the FLYING F*** is going on? How is such behaviour in software going to help ANYONE? This is simply completely unnecessary and outright dangerous.
If Sheets wants to hold my hand, they can if they like include a CUSTOM MENU ENTRY "Strip leading zeroes" that I can KNOWINGLY select when I NEED it.
Really strange decisions in software development nowadays... what's next? A MIDI sequencer randomly shifting the recorded notes because it thinks they sound better? A MIDI Clippy?
What the hell.
Comments
Excel does this too, the trick with excel is to use a .TXT file and not a .CSV, this makes Excel use the full import wizard and not the 'clever' csv one...perhaps sheets is doing the same. Downside is that when importing through the full wizard you have to tell it which data formats you want for columns that you do NOT want as TEXT.
I wouldn't upload any confidential data to google's servers in the first place
That is shocking. How can mainstream critical "business" software be infested with such ridiculous design decisions, i.e. silently modifying the data that goes through it? Especially when its MAIN task is HANDLING DATA!
Yes I know. It's just convenient for the collaboration aspect, so I use it with one of my customers (an agency which has lots of other customers) for most "tabular data". We've been bitten once already when Google was "worried" about the "safety" of one of our customers and permanently locked them out of their Google account (even though they had the perfectly valid login data and password)... since that day, we regularly "Download as XLSX" all relevant Sheets documents just in case...
I don't use Google full stop. Their new policy of locking out users from their accounts just becasue they've logged in from a different device, asking them security questions they didn't set, and then giving the option to send a password reset to a second account they also didn't specify has kinda put me off. Yahoo are doing this too. 'What's the name of your favourite uncle?' Well, I didn't set that question but it's easy as I only have one, so here goes.... 'Incorrect answer, try again'.
Have you never thought about the account being locked because of an attack?
Somebody else might have an uncle family
Not like us music makers, we ASK for apps to change the data for us
Yep, essentially all this Two-Factor Authentication bullshit is what has also made me avoid online / cloud hosting services like the plague except if I absolutely have to.
That is all fine as long as it's OPT-IN and OPTIONAL.
I remember dealing with TEH INTERNETZ since around 1995 and NEVER had a problem with logging into any kind of account (or others logging into my accounts) before this 2FA shirt came along.
But it's a general tendency in society. Everything's now so SAFE that life has become essentially unlivable.
That would be ok if Google provided support when there’s an issue, but they don’t. There’s ‘community’ support, but not with actual staff who could ask you a few questions and unlock the account. And since Gmail is now linked to Blogger and Youtube accounts, you lose access to a whole bunch of stuff.
Automated security that doesn’t always work, and no support available when it messes up.
Wouldn’t touch them with a long sticky pole.
With LibreOffice Calc you can format the cell to keep leading zeroes. If i'm not wrong, excel can too
@SevenSystems can you paste here screenshot with one or two rows of that CSV you imported ? I think i know why it happened and if it islike i think it is not google's fault :-)
Recently i spend 5 days of optimalising CSV exporter in our company app to get output CSV in format 100% compatimble with everything - i succeeded but i have to say far away biggest bitch when it comes to proper importing of CSV is Microsoft Excel
All off them including Google sheets work the same with leading zeroes in front of a number (and with good reason to, but that’s another topic)
@SevenSystems here is your solution, works everywhere:
Yes okay, but the DEFAULT should be NO formatting!
Basically if there is a zero before the numbers what you have are strings (i.e. text).
Changing the cell format to Plain Text via Format>Numbers>Plain Text will keep the leading zeros.
Or if you need it for just one cell, or while typing, just precede your string with an apostrophe “ ‘ “. Like so:
‘07891
Here's a few lines (ignore the line wraps, they're only introduced by the viewer I used for the Screenshot.)
The first column has its leading zeros cut off.
@bato may I know what the "good reason" is for software to cut off parts of my data?
I simply don't get it. Yes I know that when dealing with actual numbers, then leading zeroes do not change their value.
But the software can't know if it is dealing with a number or, for example, an item ID. That's why when in doubt, it should simply leave it untouched.
There's no excuse for "intelligently" destroying it.
ah, ok somyou have values enclosed with " :-) i was suspicious you don't...
what i do in my export CSV generator - if value is number bigger than 65535, i append to end of value tab character ( \t ) ... this was necessary for Excel (otherwise he breaked them in similiar way) and maybe it will help also to google
tab char at end doesn't break anything in value itself, but helps to app, which is importing that CSV, treat value as "string" not number and doesn't apply any formatting on it
You may as well be right, but that whole conversation happened during the early 90s and the powers that be (or were at the time) have standardized to how it behaves now. Changing that today will ruin quite a few legacy systems.
Basically, it is meant to allow scientific notation, equations and formulas, to work properly when crawling, discriminating, and working with large sets of data.
Sorry, but a longer answer is too much for me at weeks’ end :-).
The post above shows a simple way to achieve what you need
@dendy @bato thanks for your help and the details on how this stuff works. You can probably tell I fortunately don't have to deal with spreadsheet software a lot. The only time when I myself intended to do something mission critical with it is when I started writing invoices. Again I found the way ALL spreadsheet software works so broken that I instead quickly cobbled together my own accounting software.
I still feel I'm right and by default, spreadsheet software should simply treat all imported data as raw strings and not do anything with it.
If you want to do calculations, then those can be done with leading zeroes without problems, or at least I would expect that to be the case. They don't have to be stripped.
And if that automatic formatting is absolutely necessary -- well the fact that the data it's about to import DOES contain leading zeroes to me is a very clear indication that it is probably NOT meant as a "normal" number and then, IF it wants to be "smart", it should be smart enough that if it encounters a single thing that doesn't look like a number in a column, it should "format" the entire column as "raw".
Well anyway, I guess I'm tiring everyone. Just wanted to give my outside perspective on what I thought was extremely unexpected behavior.
Which is what Excel does, if you use a .TXT extension...the file can still be CSV content with delimiters etc but needs a .TXT file extension, this tells Excel that the content is strings
It does make sense, as long as you know that is what it will do.
It's an interesting fact. But strange nonetheless. It's as if Photoshop, when opening a PNG, it just loads it, whereas if you open a JPEG, it automatically and irreversibly converts it to grayscale.
Well, maybe that comparison is slightly off. I should enjoy my coffee and continue work on Xequence 😂
+1000.000.000
Believe me, we here in our offices have had this same frustration and discussion many times
And yes, Xequence work sounds like a good plan
The approach @bato mentioned is how you do that. When confronted by a "text" file, the Excel will just import the raw data and ask how you want to format it.
That said, Bato's approach is what works for Excel. For Google Sheets, it's different - you don't need to change anything with your .CSV.
Instead of importing into Google Drive (which will link the file to Sheets and when opening make the assumptions about data and screw things up) Import into Sheets. When you do this you'll have a dialog that will ask if you want to "Convert text to numbers, dates, formulas" Set this to "No" and you're all set.
Obviously, if you wanted to preserve some data as dates or formulas, etc. this won't work, since it's global to the file and not column-by-column like the TXT import into Excel.
I would agree with you there - or ask the user for confirmation.
I always wanted to make a WarGames-style 80s hacker movie where the precocious teen ‘hacks’ an important email account by pure luck...it turns out he has the same first dog name as the prominent user who set that as his security question.
Of course, the film was DOA once I realized this was just a 30-second gag and I couldn’t base a fleshed-out plot around that at all
Hehe, @SevenSystems
I am quite similar to you in stance and reaction to such topics
And yes, SANE DEFAULTS and no unneeded "intelligence" are massively important!
Yep I noticed that already. I don't know what that kind of personality is called. Pedantic? Smart?
Google seem to be experts at this kind of stuff. Also when I search for something, then read carefully through the search results and start to notice that they have nothing to do with what I searched for, only to find a tiny line of text at the top of the results: "Showing results for "Wet Web View". Search instead for "WKWebView"?
W T F ?
(yes yes, I know this is all well-established and accepted practice nowadays, i.e. software is made to think that it's smarter than its users. It might actually even be the case sometimes. But still I think any kind of unexpected behaviour is bad unless it is prominently communicated to the user. So if Google thinks that it has found better search terms than me, it should display a DIALOG with an actual OK button that contains a warning).
Yes, I already found that out... thanks though. I'm now in "philosophy" and "world improvement" mode anyway. Just like in school. When I entered the classroom, people shouted "Alexander, saving the world!". And they were right!
Could you try an experiment? Follow the instructions precisely as given in this document: http://students.washington.edu/ayandm/tutfiles/CSVTtut.pdf
Try it with google sheets (import), and anything else like that you’ve access to, and see if it has any impact at all. I’d be quite interested to see if this is the way to do this sort of thing.