Phil Parkin (10/21/2016)

---

Jeff Moden (10/21/2016)

---

Phil Parkin (10/20/2016)

---

Jeff Moden (10/20/2016)

---

Phil Parkin (10/20/2016)

---

Jeff Moden (10/20/2016)

---

Phil Parkin (10/20/2016)

---

First point to note is that a CSV file is a far far better thing to be importing from than an Excel file.

"It Depends". If someone exported a spreadsheet as a CSV file, you have all of the same problems plus the addition problem of dealing with only occasional quoted identifiers on text-based items that have a comma in them. Huge PITA compared to reading from the Excel file directly using ACE Drivers.

Actually, even then, you don't have all the same problems.

Knowing your SSIS proficiency level, you'll have to trust me on this:-) An important difference is that, with flat files in SSIS, you can set the datatype of all the underlying columns manually, rather than have the Excel driver sometimes insist that you are importing a numeric() when you know it should be a varchar(), then throw an error when it hits the (valid) string on row 5,000. That Excel driver is a steaming purulent carbuncle.

The IMEX parameter is the solution for that particular problem.

IMEX = 1? Still doesn't work if your string data is way down the file, unless you hack the registry to increase Excel's 'number of rows to check' sample size. Or maybe you know something I don't.

I'll have to try longer spreadsheets because I've not run into that problem using IMEX=1, which is claimed to force everything in the spreadsheet to character-based values.

Jeff, please read this thread, particularly Todd McDermid's response, in which he explains the problem with IMEX=1.

The only near-bulletproof way I have found of doing this is to ensure that

a) IMEX=1 appears in the connection string, and

b) I read the column headers as data

Doing this forces Excel to treat all of the columns as text (assuming your column headers are not numeric!).

This brings its own overheads, of course (have to dispose of column headers, have to name columns manually based on ordinal, have to specifically convert to numeric/date where appropriate). But at least all of the data actually gets pulled in this way.