Import via Excel and SSIS

Posted on 2007-07-20
Last Modified: 2013-11-30
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

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.



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?
Question by:i2mental
    LVL 8

    Expert Comment

    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

    LVL 92

    Expert Comment

    by:Patrick Matthews
    In your SSIS package, you have complete control over how the data gets imported.
    You can very easily import into a varchar field.
    LVL 1

    Accepted Solution

    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.
    LVL 8

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now