Post reply

import .txt file with 00/00/0000 dates causing error

  • July 16, 2004 at 8:19 am

    #65674

    I have created a dts package to import a .txt file every night. The package drops the table then creates a new one before it imports the data. I used the datetime datatype for the date field (not sure if it should be smalldatetime).

    I have a couple of issues with the dates which are formatted like this "mm/dd/yyyy". the default value is 00/00/0000.

    I changed the transformation of the date field from "copy column" to "date time string". I think this should work but I'm not sure because the 00/00/0000 dates are causing an error, when I execute the package.

    I would appreciate any info that might help me. Thanks

    Paul 

  • July 16, 2004 at 8:30 am

    #514868

    Not sure that's a valid date. I typically use 1/1/1900 or some other trigger date to mark invalid dates. Can you search and replace in the text file?

     

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 16, 2004 at 8:42 am

    #514875

    Thanks for your suggestion. I think that would work but I have a system that is going to create a new .txt file with 00/00/0000 dates every night. I'm trying to schedule the import package every night after the new .txt file is created.

    Is there some way I can have the transformation skip over the 00/00/0000 or enter a null value in it's place?

    Thanks for you help

    Paul

  • July 16, 2004 at 11:51 am

    #514919

    Hi

    Import the text file into a staging table ( with the datecolumn char(10) ).

    Then use sql to scrub the data.

    1. truncate stageTable

    2. Source -> stageTable

    3. insert realTable(,,,datecolumn,,,)

    select ,,,case isdate(datecolumn) when 1 then datecolumn else null end,,, from stageTable

    /rockmoose


    You must unlearn what You have learnt

  • July 16, 2004 at 12:13 pm

    #514926

    In the past I have used an active x transformation (ie vbs)to modify the date format ie change the / into a -

     

    Steven

  • July 16, 2004 at 12:28 pm

    #514930

    Yeah,

    But

    select convert(datetime,'00/00/0000')

    is just as invalid as

    select convert(datetime,'00-00-0000')

    You could use vbs to convert '00/00/0000' to '01/01/1753' ( or what you prefer )

    to make it a valid date.

    or vbs to convert '00/00/0000' to NULL value.

    vbs:ers how do you do this ?

    /rockmoose


    You must unlearn what You have learnt

  • July 16, 2004 at 12:49 pm

    #514933

    I tried to do it as an activeX script as part of the transformation but I couldn't get it to work.

    I did get it to work with a staging table, and I'm very happy about that.

    My vbs code that did not work looked like this (any idea why?)

    Function Main()

    if isDate(DTSSource("ReservationDate")) then

     DTSDestination("ReservationDate") = DTSSource("ReservationDate")

    end if 

    Main = DTSTransformStat_OK

    End Function

    Thanks

    Paul

  • July 16, 2004 at 12:52 pm

    #514934

    If I remember correctly there is a test button, which runs your code against the first 200 lines of data. Using that is a good way of testing your code

    Also you will need to add an else into your code, ie what to do when its not a date

    Steven

  • July 16, 2004 at 1:29 pm

    #514936

    Glad it worked out for You.

    For data manipulation / cleaning I always use SQL.

    In DTS, if I use it, I put as little business logic as possible, if any.

    The staging table approach is very efficient and fault proof.

    *sql/bulkinsert/dts/bcp **SQL

    DirtySource ->* StageTable --cleanvalidatelogerrors-->** ProductionTable

    /rockmoose


    You must unlearn what You have learnt

  • July 16, 2004 at 1:45 pm

    #514941

    I'm getting an error with one of the fields that that I am bringing over from the stage table to the destination table.

    the stage table has a data type of varchar and the destination table has a datatype of money for this field. The error indicates that I need to convert so I put the following in the select part of my sql statement that moves the data from stage to destination. 

    convert(money ,[LoanAmt] ) ,

    Thanks for any input about why this is not working

    Thanks

    Paul 

  • July 16, 2004 at 2:01 pm

    #514944

    The first thing that springs to mind is that the varchar field contains data that cannot be converted to a numeric value ( or money ).

    select * from stageTable where isnumeric([LoanAmt]) = 0

    /rockmoose


    You must unlearn what You have learnt

  • July 16, 2004 at 2:38 pm

    #514953

    Right again.

    What started out to be a very frustating day is turning into a bright weekend.

    I didn't realize there was so much to SQL. Did you take a class or is there a particularly good book that you used to get up to speed.

    Thanks for your help

    Have a great weekend.

  • July 16, 2004 at 4:11 pm

    #514968

    Hi,

    Actually the only book I have read (front to back) is

    C.J. Date "An Introduction to Database Systems"

    This will give a good theoretical understanding of databases and the relational model.

    Apart from that I recommend BOL ( A very good documentation of Microsofts implementation of a database system ).

    The web has some good sites ( this one, http://www.sqlteam.ccom, http://www.sqlsecurity.com and more )

    ALso I am sure there are many good books and courses on T-SQL out there.

    Unfortunately I cannot recommend one off hand, ask this forum and You will surely get replies 🙂

    You to have a great weekend.

    /rockmoose


    You must unlearn what You have learnt

Viewing 13 posts - 1 through 12 (of 12 total)

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