• Jeff Moden - Thursday, January 19, 2017 2:34 PM

    Phil Parkin - Thursday, January 19, 2017 12:31 PM

    Jeff Moden - Thursday, January 19, 2017 12:12 PM

    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 "PFM2". 😉

    I use the 'read the column names to force everything to text' trick too, but I shouldn't have to. I'm looking forward to the day I get a file with no column names ...

    Another thing I remembered. If my spreadsheet was 60k rows long, but has subsequently been edited to contain only 50 rows, the damned ACE drivers return 59,950 empty rows which I have to direct into the trash.

    And only last week we found that someone had reformatted a column of rates (which can have up to 4 decimal places) to display as 2 decimal places. From then on, the ACE drivers return the data with decimal places 3 and 4 truncated. Unless you have a ton of validation in place, it's really easy to miss stuff like this.

    Thanks for the heads up on all this, Phil.  You sound like the perfect candidate to do a technical review on the article I'm writing for all this.  Any interest there?

    You bet. Bring it on!

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.