Post reply

data conversion

  • December 6, 2005 at 3:08 am

    #364412

    Hi all,

    I have to create 2 tables for receiving datas sent by an other corportate application on my SQL server 2000. The files sent will be fixed lenght text format, and I have the description of the original files.

    My problem is that some of the fields are in a zoned format : I have several zoned lenght, like 3,0 or 15,6, and a date in a 7,0 zoned format which correspond to a CYYMMDD date

    what should I use for my SQL tables?

  • December 6, 2005 at 2:52 pm

    #608066

    For the initial step of importing data from files, I normally want to get everything in the database so I generally create some work tables with varchar datatypes.  Once the data is loaded into the work tables it needs to be cleaned before inserting into the destination tables.  This is where you would do your converts and string parsing etc...  This is also where you should write invalid rows off to a separate table or file for logging and troubleshooting.

    I'm not sure what you mean by zoned length.  Are you talking about the precision and scale?  If so then use decimal(3,0), decimal(15,6) respectively in your destination table.  I would also strongly recommend using the datetime data type for the date and doing whatever conversion is required to get the supplied date format into a format that is recognizable to SQL Server.

    /*****************

    If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

    *****************/

  • December 7, 2005 at 3:20 am

    #608127

    Thanks for the advices

  • December 7, 2005 at 10:53 am

    #608229

    Fixed length is a pain to work with in DTS. Easier is to import the file into an import buffer table having a single column. Then use a user defined function, with SUBSTRING() inside, to parse each column while selecting it into the second cleanup buffer table.

    INSERT INTO cleanup_table( colA, colB, colC )

    SELECT

    dbo.UDF_PARSE(colZ, 1, 5)

    ,dbo.UDF_PARSE(colZ, 6, 15)

    ,dbo.UDF_PARSE(colZ, 16, 20)

    FROM

    import_table

     

    Now that your data are in proper columns you can inspect and clean before inserting into your production table.

Viewing 4 posts - 1 through 3 (of 3 total)

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