Re: Parsing horizontal table in Excel & loading it into a database

Perhaps stating it slightly-differently: first, walk along the names-column-header row, looking to find the first row that is blank, or maybe, the last one that is not. (If you detect at this point that there are no names, or that there are blank columns within the list, die().) Now, you know the range of columns to be processed.

I would also examine the row-header columns, either to locate those rows or to die() if the spreadsheet is not exactly as expected. Now, you know, or have confirmed, which rows you need to look at. The necessary loop naturally follows.

FYI... I always code such programs very suspiciously. “Trust, but verify.™” Spreadsheet users can do anything they wish, and sometimes do, even by mistake. This can cause a subsequent computer program to commit the worst possible sin: to “appear to work correctly,” but to produce the wrong results. No one will know. Therefore, a little extra time spent verifying the expected content of rows and columns, by means of regular expressions, with die() being the response if anything appears not-right, makes for a much more robust and reliable program. If such a program runs to completion (and if its various tests have actually been tested!), then it serves as a positive indicator that its outputs (and, its inputs) are plausible.

Replies are listed 'Best First'.
Re^2: Parsing horizontal table in Excel & loading it into a database by marinersk (Priest) on Jun 10, 2015 at 17:39 UTC
I agree with the Constructive Paranoia. Being of an engineering mindset, I am even cautious when there is "no way" the data could be laid out incorrectly. I've lost count of the number of times in my career my code has caught errors in an error-resistant environment, and handled it with some kind of grace (even yelling for help is more graceful than wandering off the edge of the Abyss in many cases), and I've had other folks asking how on earth I saw that coming. Simple: I didn't. I simply asked that most basic of engineering questions during the design phase: What could go wrong? In an open-ended system such as this problem describes, Constructive Paranoia is absolutely warranted. I would almost say it is mandatory, but most folks have exceptions in their heads -- and, yes, Mike, it would not surprise me in the slightest if you were not amongst them :: grin ::. The code can't react with judgement the way a human does (for better or for worse!). So you have to code all the edge and corner cases, and do your best to deal with the black hole cases as well. Otherwise, it will almost certainly come back to haunt you.	[reply]

Replies are listed 'Best First'.

Re^2: Parsing horizontal table in Excel & loading it into a database
by marinersk (Priest) on Jun 10, 2015 at 17:39 UTC

I agree with the Constructive Paranoia.

Being of an engineering mindset, I am even cautious when there is "no way" the data could be laid out incorrectly.

I've lost count of the number of times in my career my code has caught errors in an error-resistant environment, and handled it with some kind of grace (even yelling for help is more graceful than wandering off the edge of the Abyss in many cases), and I've had other folks asking how on earth I saw that coming. Simple: I didn't. I simply asked that most basic of engineering questions during the design phase: What could go wrong?

In an open-ended system such as this problem describes, Constructive Paranoia is absolutely warranted. I would almost say it is mandatory, but most folks have exceptions in their heads -- and, yes, Mike, it would not surprise me in the slightest if you were not amongst them :: grin ::.

The code can't react with judgement the way a human does (for better or for worse!). So you have to code all the edge and corner cases, and do your best to deal with the black hole cases as well. Otherwise, it will almost certainly come back to haunt you.

[reply]