Post reply

Problem with some field types when importing from Excel 2003 SP2 to SQL Server 2000 SP3

  • September 13, 2007 at 9:12 pm

    #70244

    These are 2 problems that I have encountered:

    1. Importing from a column formated as text in Excel but containing just numbers (in fact phone numbers, but without any extra sign in90% of cases). Nothing has been imported into the varchar(20) SQL table field except (and here it comes the funny part) the phone numbers with extensions or just containing extra-symbols (as literals).

    I made a test - just added an 'a' at the end at the phone number initially stored as '4165551234' and imported as NULL. When it became '4165551234a' it was imported perfectly. (varchar because it has to handle extensions too - it seems it's the only kind of phone number that can be handled)

    2. Another column formatted again as text, this time containing dates has to go into another varchar field. And again a funny (not so funny in fact) fact:

    - the dates formatted dd/mm/yyyy (15/09/2007) have been imported properly into the databas evarchar field. But when it comes a field like yy/mm/dd (06/02/18) a NULL is generated into the target field. (I choose to use a varchar field because I have dates in at least 20-30 different ways introduced by end-users, it's my job after to format them properly ...)

    Please Help me!!!

    I am using DTS. The versions of the software are in the title. I have tried too to export the XLS file to CSV, tab separated text file, XML but I had all ind of problems. Is there any way I could make it?

    Thank yo for any idea!

  • September 18, 2007 at 1:06 pm

    #734387

    Try the following:

    1. Save your excel file YourFile.xls with csv extension: YourFile.csv
    2. Fields of the table where you load the data has to be of varchar or char data type.  

     

    1. In the DTS package select Text File (source) for your YourFile.csv.
    2. On the file Properties select:

    -         Delimited. Columns are separated by character(s)

    -         Row Delimiter:  {CR}{LF}

    -         Text Qualifier: Double Quote {"}

    1. Click button “NEXT”
    2. Specify Column Delimiter: Comma
    3. On the   Transformation TAB map your    fields
    4. Load the data    

    Isabella   

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply