RE: use excel to update backend SQL Table

SSC Guru

Points: 1003863

January 19, 2017 at 12:12 pm

#1923822

Phil Parkin - Thursday, January 19, 2017 10:55 AM
Jeff Moden - Thursday, January 19, 2017 10:46 AM
You don't need SSIS. The ACE drivers work just fine. I install them as a matter of rote on every system.
IMO, the ACE drivers are a pile of horse**** and not fit for purpose.
Not only that, what they return depends on how data was formatted. How many people have been tripped up by getting a '-' returned after a column has been formatted as 'Accounting'? (Rhetorical)
How about a column that contains numeric data until row 1000, when there's an 'x' in there? Can you tell the ACE driver to treat that column as text? Good luck with that, unless you're into registry hacks.

Haven't run into the "formatted as Accounting" problem before. I'll have to give that a try.

Agreed on the second part about mixed columns but I've beat that particular problem without a registry hack by reading in the column names as data along with the rest of the rows. It's necessary to figure out how to auto-magically unpivot like-data across, say, multiple months and to also auto-magically capture the column names without duplication for the final result. If they add another month or even a set of totals for a quarter/year, the code auto-magically figures all that out with no prior knowledge on the part of humans. It's totally "PFM²". 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)