Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    7

    Angry Unanswered: Problems importing TXT files using DTS

    Hello,

    I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file with over 100,000 records. The fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY is 32 characters, etc.)

    I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002, Col003, etc. My problem is that I don't want everything as Characters or Col001 etc. I want different column names and columns of data to be INT, NUMERIC(x,x), etc. instead of characters every time. If I change these values to anything than the default in DTS it won't import the data correctly.

    Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc. the way I want it to look, FWIW. This seems to be going nowhere fast.

    What am I doing wrong? Should I be using something else than DTS?

    Any suggestions are greatly appreciated.

    Thanks,
    JB

  2. #2
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    you should create a transform data task and connect the text file to the destination though the task.
    create the destination table in the xform task and map the data types to the new destinations.

    ps while you are there change the column mappings from x individual column copies to 1 single thread it will increase your performance.

  3. #3
    Join Date
    May 2004
    Posts
    7

    Thanks

    Thanks for the response. I'm going to look into this today. If anyone has any other suggestions, I'd appreciate them too.

  4. #4
    Join Date
    May 2004
    Posts
    7

    It works BUT.....

    Ruprect,

    Come to find out that yesterday I was trying the exact thing you mentioned in your first post. I examined it a little more thorough this morning. The only thing different that I wasn't using 1 thread.

    I have 94 columns worth of data. If I select the first 30 columns it will import the text and insert it into the table with appropriate column headings to perfection. The first 30 columns are all character fields.

    At Column 31 the first integer field appears. It will stop and give me an error message. It says this, "... conversion error: Conversion invalid for datatypes on column pair 1 (source column 'Col031' (DBTYPE_STR), destination column 'MARKETVALUE' (DBTYPE_I4). There are all numbers as well, no letters or symbols in these columns.

    I'm fairly certain I could convert the column to character and it would work fine. The problem is that I need the column to be an integer (as well as my other numeric columns) to perform various mathematical queries.

    Thanks again,
    JB

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah, you've got bad data...

    I would load the data to a table of a varchars...like you have now


    Think of it as a staging table

    Then audit the data...

    For example

    Col030 should be a date

    Find out all the rows with bad dates

    SELECT * FROM myTable99 WHERE ISDATE(Col030)=0

    Will show you rows sql doesn't consider a date...

    Same with numerics

    ISNUMERIC(col1)=0

    You won't be able to load those rows to your final destination table

    either fix the input file, or ignore the rows...

    in any case, go to who ever gave you the file and say...see here...this is garbage...fix it....

    and wait a week while they try to figure out what to do...

    Golf, Tennis, Long lunches....whatever you want....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    May 2004
    Posts
    7

    Yes!!!

    Brett,

    You are correct it was bad data. That should have been the first place I looked.

    I went into Query Analyzer and ran the query you posted on the columns of data that I wanted to turn into numeric data. 5 out of 32 potential columns had issues. It seems that some of the fields that I wanted to turn into numeric values had blank values. I guess you can't convert a blank character <NULL?> into a blank number.

    Anyways, the problem is solved.

    Thanks,
    JB

Posting Permissions

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