Please, I need help with importing from excel using DTS

  • 13 years ago
    I am new to SQL Server, and I am picking up a database in SQL server 2000.
    The developer who built that database, created a DTS package to import data
    from excel. In the package, there is an Excel Connection and a SQL Server
    connection and a blank line between the two going from Excel to SQL Server. the
    problem is that there is a column in excel that has some cells formatted as
    text and some formatted as number. The cells that are formatted as Text are
    imported OK, the numeric cells are imported as nulls. is there a way to edit
    the package to import everything from excel. The data type of the field that
    this data is going to in the SQL Server side, is varchar (255).













    Here are the details:
    in Excel (the source) the first column, which is the problem, the data looks
    like below:

    FY0325
    FY2586
    4235
    8567

    The top 2 cells, of course are text but I found that the bottom 2 cells
    (4235,8567) are being treated by the destination (sql server side) as
    numbers. generally, the 4 cells in excel are formatted as "general".

    On the SQL Server 2000 Side (The destination) here is what's imported:

    FY0325
    FY2586
    Null
    Null

    The data type in the sql table is (varchar (255)). My understanding is that this
    type should accept Alphanumeric, numeric, text so long as the size is less
    than 10 characters. I do not understand why it is not accepting the values
    that has numbers only. I tried to change the format on Excel side to "Text"
    but got the same results. any idea?
    thanks
    Al

























Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Programming today is a race between software engineers striving to build bigger and better idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning.” - Rich Cook