My apologies — again — for taking so long between posts. I got swamped at work. Well, to be accurate, I chose to swamp myself. We have an issue with trying to gather data from spreadsheets that are sent via email. If the data can’t be accurately ingested into the company databases through some kind of software intelligence, people end up having to type it all. That’s one big @#$%load of tedious typing.
Although this wasn’t technically my problem to solve, I had a flash of inspiration on how to solve it. So I wrote code until the wee hours several evenings in a row (including weekends) and proved I could indeed solve it — for one major supplier of the spreadsheet info. Now I’m attempting to take what I learned and apply it to multiple suppliers, all of whom seem to have their own opinions about where the data should go on a spreadsheet and how it should be formatted. It’s a ginormous task.
But I’m close. Really, really close.
If you enjoy my posts, please consider a small donation to the Fat Head Kids GoFundMe campaign.
Just so long as you and yours are OK, we can be patient. For a while, anyway. After all, you do have a wife, two daughters, a cat, 2 Rottweilers, and quite a few chickens to support. Am I missing anybody? Goats? Pigs? Pony? Anyway, a project such as you describe just natcherly has to take priority for a working man. Thanks for letting us know you’re still with us.
No goats or pigs at the time. I’m happy to say I left work at 2PM yesterday and spent last night having a relaxing dinner out with Chareva and the girls. Today (Saturday) is the first day in nearly three weeks that I didn’t spend coding.
Just wondering… Are you writing VBA? 🙂
If you’re already not doing it, you may want to consider using a meta file/schema file that’ll list all the rows, columns, and any formatting info for each supplier and make your code generic for all the suppliers. Cheers!
It’s a ton of C# code. The company has been begging suppliers for years to adopt a common format, but the suppliers aren’t interested. They figure if they send data, they’ve fulfilled their legal obligation (which is true), so they don’t feel the need to make our lives convenient. So my program figures out the location of the relevant rows and columns on the fly. We’ll end up storing that as information on what the supplier sends … although many of them aren’t even consistent with their own files.
Just so long as you and yours are OK, we can be patient. For a while, anyway. After all, you do have a wife, two daughters, a cat, 2 Rottweilers, and quite a few chickens to support. Am I missing anybody? Goats? Pigs? Pony? Anyway, a project such as you describe just natcherly has to take priority for a working man. Thanks for letting us know you’re still with us.
No goats or pigs at the time. I’m happy to say I left work at 2PM yesterday and spent last night having a relaxing dinner out with Chareva and the girls. Today (Saturday) is the first day in nearly three weeks that I didn’t spend coding.
Thanks for this Tom. Can’t tell you how many times I’ve been checking your blog for a recent post. Really miss you when you’re busy like this, but it’s totally understood, and I appreciate the time you took to let us all know why!!!
I’m happy to say the big project is done, and I’ll be posting again.
Just wondering… Are you writing VBA? 🙂
If you’re already not doing it, you may want to consider using a meta file/schema file that’ll list all the rows, columns, and any formatting info for each supplier and make your code generic for all the suppliers. Cheers!
It’s a ton of C# code. The company has been begging suppliers for years to adopt a common format, but the suppliers aren’t interested. They figure if they send data, they’ve fulfilled their legal obligation (which is true), so they don’t feel the need to make our lives convenient. So my program figures out the location of the relevant rows and columns on the fly. We’ll end up storing that as information on what the supplier sends … although many of them aren’t even consistent with their own files.
Thanks for this Tom. Can’t tell you how many times I’ve been checking your blog for a recent post. Really miss you when you’re busy like this, but it’s totally understood, and I appreciate the time you took to let us all know why!!!
I’m happy to say the big project is done, and I’ll be posting again.
Glad to hear all is okay, and as I am a software/electrical engineer, I love solving those kinds of problems, as in, no one else seems to know how to solve it so give it to Tom. It is just the work that fills the gap from one tough problem to the next that is unfortunate. I solved an interesting one yesterday, and I am rewarded with some general documentation 🙁
cheers,
Tom
I enjoy the challenge as well. It’s the part of my brain that enjoyed physics class in college, even though I had no intention of going into physics.
Tom, I have solved this exact problem several times in the past.
For this instance, I would create a table in SQL Server with all of the fields being varchar(max) and an ID column that is an Identity.
Import the data into the the generic fields with SSIS or similar, I would use a C# program myself.
Then in SQL evaluate a series of rules on the columns to check if the data fit the constraints of what that the headers say they are, I.E. Quantity is always numeric int, First Name is always alphanumeric, etc.
Then based on that, either push the data into a real table based on the header names or generate messages based on low number exceptions (there is a 1o in the Quantity ) and send it back to the source with the exception messages referencing row and column.
John the DBA.
I ended up going with a similar idea, only with a @#$%load of C# code.
To borrow from Oscar Wilde – “Work is the curse of the low-carb class”.
I assume you’re paraphrasing.
Glad to hear all is okay, and as I am a software/electrical engineer, I love solving those kinds of problems, as in, no one else seems to know how to solve it so give it to Tom. It is just the work that fills the gap from one tough problem to the next that is unfortunate. I solved an interesting one yesterday, and I am rewarded with some general documentation 🙁
cheers,
Tom
I enjoy the challenge as well. It’s the part of my brain that enjoyed physics class in college, even though I had no intention of going into physics.
Welcome to my life! In my line of work (Identity and Access Management) I get to deal with the incredible diversity of data formatting entirely too often.
Tom, I have solved this exact problem several times in the past.
For this instance, I would create a table in SQL Server with all of the fields being varchar(max) and an ID column that is an Identity.
Import the data into the the generic fields with SSIS or similar, I would use a C# program myself.
Then in SQL evaluate a series of rules on the columns to check if the data fit the constraints of what that the headers say they are, I.E. Quantity is always numeric int, First Name is always alphanumeric, etc.
Then based on that, either push the data into a real table based on the header names or generate messages based on low number exceptions (there is a 1o in the Quantity ) and send it back to the source with the exception messages referencing row and column.
John the DBA.
I ended up going with a similar idea, only with a @#$%load of C# code.
To borrow from Oscar Wilde – “Work is the curse of the low-carb class”.
I assume you’re paraphrasing.
indeed it is.
I am forever being amazed by how many ways there are in which people contrive to use spreadsheets to store useful data in ways that make it practically impossible to extract it again.
I now set up a separate parameter worksheet to store locations of columns and rows so the macros don’t have to be edited every time a new sheet configuration arrives.
It is good mental chewing gum though.
What finally struck me is that I could write code to figure out the data locations on the fly, then parse. Complicated as all getout, but I got it working.
Welcome to my life! In my line of work (Identity and Access Management) I get to deal with the incredible diversity of data formatting entirely too often.
I feel for you, now more than ever.
I know exactly what you mean. Inspiration strikes and you gotta take advantage of it!
So nice to hear your “voice” again! I was almost going into withdrawal. One good result of the long time between posts is that I resorted to Facebook and came across your speech on the Low Carb Cruise. It made me more determined than ever to buy your book when it comes out! It may be written for children, but I know a few adults that might benefit from it, since plain adult language has not gotten through to them. God knows I’ve tried! Will try to be patient waiting for the next post. Don’t work too hard!
Well, truth is, we hope both kids and adults enjoy it. Kids don’t buy books, so we have to appeal to the parents.
indeed it is.
I am forever being amazed by how many ways there are in which people contrive to use spreadsheets to store useful data in ways that make it practically impossible to extract it again.
I now set up a separate parameter worksheet to store locations of columns and rows so the macros don’t have to be edited every time a new sheet configuration arrives.
It is good mental chewing gum though.
What finally struck me is that I could write code to figure out the data locations on the fly, then parse. Complicated as all getout, but I got it working.
I know exactly what you mean. Inspiration strikes and you gotta take advantage of it!
Exactly. If an idea strikes me and I don’t deal with it, it just keeps nagging me.
So nice to hear your “voice” again! I was almost going into withdrawal. One good result of the long time between posts is that I resorted to Facebook and came across your speech on the Low Carb Cruise. It made me more determined than ever to buy your book when it comes out! It may be written for children, but I know a few adults that might benefit from it, since plain adult language has not gotten through to them. God knows I’ve tried! Will try to be patient waiting for the next post. Don’t work too hard!
Well, truth is, we hope both kids and adults enjoy it. Kids don’t buy books, so we have to appeal to the parents.
I’m really impressed by your initiative. I’d hire you in a flash, (if I had a call for your work)
You’re a problem solver.
Once the idea got ahold of me, I couldn’t let it go. Took several days of half-crazed programming, but I did, in fact, come up with a solution.
I’m really impressed by your initiative. I’d hire you in a flash, (if I had a call for your work)
You’re a problem solver.
Once the idea got ahold of me, I couldn’t let it go. Took several days of half-crazed programming, but I did, in fact, come up with a solution.
re: Although this wasn’t technically my problem to solve…
I got a stock option for doing that once (hardware). Accept no less☺
re: all of whom seem to have their own opinions about where the data should go on a spreadsheet and how it should be formatted.
And if you’re lucky, it will all “just” be in .xlsx. If not, you might need to learn to parse various undocumented binary formats going back to VisiCalc. I had to deal with .wgz files just a couple of years ago, and some people at that company are still generating new content in Wingz, on PCs, inside a window from a SPARC server. Never underestimate how long people will keep theoretically deceased apps running.
It’s been all .xls and .xlsx. If suppliers send information that pre-dates Excel 97 … well, too bad.
re: Although this wasn’t technically my problem to solve…
I got a stock option for doing that once (hardware). Accept no less☺
re: all of whom seem to have their own opinions about where the data should go on a spreadsheet and how it should be formatted.
And if you’re lucky, it will all “just” be in .xlsx. If not, you might need to learn to parse various undocumented binary formats going back to VisiCalc. I had to deal with .wgz files just a couple of years ago, and some people at that company are still generating new content in Wingz, on PCs, inside a window from a SPARC server. Never underestimate how long people will keep theoretically deceased apps running.
It’s been all .xls and .xlsx. If suppliers send information that pre-dates Excel 97 … well, too bad.
Thanks for checking in Tom, it keeps me coming back. Of course the info, laughs and photos are a big part of that too.
I’m finally out of the programming weeds and will return to the Fat Head chair after a day of rest.
That an August statement.
Thanks for checking in Tom, it keeps me coming back. Of course the info, laughs and photos are a big part of that too.
I’m finally out of the programming weeds and will return to the Fat Head chair after a day of rest.
That an August statement.
Two pieces of software that ended up perverted in the corporate world
Excel, great piece of software for doing calculations. But when it ends up being bastardised into a database of sorts,(i call that the spreadmart in opposition to the datamart) or worse still, as a project management tool, things start to go horribly wrong. (the dreaded “oh, can you email me the latest version of the project status…”)
Powerpoint, wonderful tool. Until it starts to be used as a word processor for the literally challenged. And I have seen some big five consultants spend a week on a single slide tweaking graphics and animations….
Just finished my last gig in Paris and also enjoying some time off and relaxation.
Enjoy the summer!
E.
Two pieces of software that ended up perverted in the corporate world
Excel, great piece of software for doing calculations. But when it ends up being bastardised into a database of sorts,(i call that the spreadmart in opposition to the datamart) or worse still, as a project management tool, things start to go horribly wrong. (the dreaded “oh, can you email me the latest version of the project status…”)
Powerpoint, wonderful tool. Until it starts to be used as a word processor for the literally challenged. And I have seen some big five consultants spend a week on a single slide tweaking graphics and animations….
Just finished my last gig in Paris and also enjoying some time off and relaxation.
Enjoy the summer!
E.
You enjoy yours too. I’m finally back to a normal schedule and feeling relaxed.
Totally agree about Excel. Unfortunately, as one of our managers put it, our providers could grab a pen, scrawl the information on a piece of paper and mail it in, then claim (correctly) they fulfilled their legal obligation. So we deal with what they give us.