• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2056
  • Last Modified:

Import via Excel and SSIS

I'm working on an SSIS package that will digest an excel spreadsheet that our client will have filled out and import that information into a SQL Server database. I'm stuck on a particular problem with columns that contain numbers and text.

Through excel, I see a column that would look like this.

WAG_TYPE                <-- column header
Roof
Basement
1
1
Roof
2

When I pick this data up via Excel Source in my package, it will either display only the Text or only the numbers. The other rows will show up as NULL.

i.e.

WAG_TYPE
Roof
Basement
NULL
NULL
Roof
NULL

This will be reversed if the package guesses that it's an numeric field (depending on how the data is sorted in excel). Numbers will show up and text will be NULL.

I can go into excel and put a ' in front of every number to treat it as text and it comes in fine. But that is much to labor intensive and the data will be coming from a client.

I'm looking for a way to force all contents of a column to be read as text no matter what is in it. Does anybody have a suggestion as to how I can accomplish that?
0
i2mental
Asked:
i2mental
1 Solution
 
MOA81Commented:
I have something that works but I am not sure how efficient is it for you
- open the excel file
- right click the column that you want to be as text
- right click and choose 'Format cell' and choose 'Text' format
- now select all the cells and copy
- open the notepad
- paste the data
- now in notepad press crtl+A to select all
- go back to excel
- choose the first cell A1 right click and paste special
- from the options choose 'Text'

by this you have the column you want as text

now on the SQL side just make sure that the field properties is as varchar resides on this column

this may sound as a long procedure, but thats only in writing, when you apply this its only a matter of seconds

0
 
Patrick MatthewsCommented:
In your SSIS package, you have complete control over how the data gets imported.
You can very easily import into a varchar field.
0
 
vjammyCommented:
Connecting to Excel
The Microsoft Jet provider is used to connect to an Excel workbook. In the following connection string, the Extended Properties keyword sets properties that are specific to Excel. "HDR=Yes;" indicates that the first row contains column names, not data and "IMEX=1;" tells the driver to always read "intermixed" data columns as text. Note that

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\MyExcel.xls;Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1""
Note that the double-quote character required for the Extended Properties must also be enclosed in double quotation marks.

http://msdn2.microsoft.com/en-us/library/ms254500.aspx
0
 
i2mentalAuthor Commented:
Great! Thanks vjammy, I was able to add that extended property to the connection manager. Lets hope it doesn't affect the rest of my package.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now