Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    65

    Angry Unanswered: SQL Server WILL NOT import properly from Excel!!

    ARRGGH, I'm going to kill Excel or SQL Server!! I've spent an hour on this and I can't get it working. I'm importing some address data, and several of the zip codes are importing as NULL, even though in the Excel sheet, they look fine! Excel is complaining, something about the zip codes are numbers entered as text..whatever.. so I edited my SQL import and made it so the ZIP field gets created as varchar 50 instead of float. It still won't import right.

    Someone please tell me what I'm doing wrong.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your zip column is probably a mix of numeric and text cells. DTS doesn't handle that very well. Force all of the cells to be one thing or another, then do an import based on whichever datatype you picked. That should get it working fairly smoothly.

    -PatP

  3. #3
    Join Date
    Jan 2004
    Posts
    65
    Yeah i tried that. I selected all the cells and formatted them as "Text". And my ZIP field is varchar. It still didn't work..

  4. #4
    Join Date
    Jul 2004
    Posts
    5
    I have experienced similar problems with Excel and had to export the file as tab or comma delimited and then DTS had no problem importing the data.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think you are changing the formatting, not the datatype of the cells.

    Insert an empty column to the right of the zipcode column. In the cell in the empty column next to the first zipcode, put the formula
    Code:
    =""&RC[-1]
    Replicate the formula for all of the zipcodes, then Copy and paste Special Value the new column you've created (effectively replacing the formulas with their values). At that point, you can delete the original column.

    -PatP

  6. #6
    Join Date
    Jan 2004
    Posts
    65
    Hmm, it doesn't like that formula. I get a formula error.

    EDIT: OK, excel is on crack. A minute ago I got a formula error. Now, it just puts my text verbatim in the cell. Even if I do =SUM(A1:B1), it just says "=SUM(A1:B1)" in the cell.
    Last edited by MDesigner; 08-03-04 at 19:26.

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You have text formatting specified for the cell, you can't enter a forumula into it until you reset the formatting back to general.

    If you haven't set the RC formatting using Tools | Options | General, then Excel won't like that RC[-1] cell reference. Just put a reference to the cell to the left and you'll be fine.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •