Importing Excel into SQL 2008

Question

I'm fairly new to this, so bear with me!  I created a new Integrations Services Connection Project in BIDS and I selected an Excel file as my source and a database as my destination.

The excel file looks like this:

NAME  IDNUMBER STATE
DOE, JOHN AF927471 WA
SMITH, JANE D9438A  OR
JOHNSON, JAMES 94189981 CA

However, when I preview it in BIDS, the data shows the IDNUMBER for JOHNSON, JAMES as NULL.

http://i.imgur.com/Z5hIR.jpg

I guess what's happening is BIDS is making that column a text field, and because the ID is all numerical, it's showing as NULL?  The IDNUMBER is always going to have either letters, numbers, or a combination of both.  I tried going into excel and making that column a text field but that didn't seem to help.

What can I do so I can import this data?

Answer 1

check http://plexussql.blogspot.com/2010/04/looping-through-excel-files-and-sheets.html

and for using a combination for both Numeric and non numeric fields you must use IMEX=1

---

Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\SSIS\LoopingThroughExcelFilesAndSheets\Sample\SampleExcelFile.xls;Extended Properties="EXCEL 8.0;HDR=Yes; IMEX=1;"

Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).

Answer 2

However, when I preview it in BIDS, the data shows the IDNUMBER for JOHNSON, JAMES as NULL.

What i do is, i use a LOOK UP object to find the UserID from a mapping table in SSIS and redirect the error records (in your case users with NULL UserID) to a Error Table.

 

---

Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).