Professional Web Applications Themes

Cleaning Data for Load - Microsoft SQL / MS SQL Server

Hello everyone, I need to load a comma-delimited text file to a SQL Server 2000 table every day. I have no problem loading the data, provided the data is 'clean.' However, the load fails on little things, like a quotation mark embedded within a string, or a non-date value in a date field. I am assuming I need to process the data before loading, removing or fixing the bad records before loading. The file comes to me from another company. I can get them to fix problems, but before going that way, I want to have a systematic way to ...

  1. #1

    Default Cleaning Data for Load


    Hello everyone,

    I need to load a comma-delimited text file to a SQL Server
    2000 table every day. I have no problem loading the data,
    provided the data is 'clean.' However, the load fails on
    little things, like a quotation mark embedded within a
    string, or a non-date value in a date field.

    I am assuming I need to process the data before loading,
    removing or fixing the bad records before loading. The
    file comes to me from another company. I can get them to
    fix problems, but before going that way, I want to have a
    systematic way to scan through all the records they send
    to determine which records are ok and which are bad, and
    why.

    Number of records being handled is 50,000 records per day,
    so this has to be a very efficient process.

    There must be a good utility for this. I cannot believe I
    would have to write something by hand in VB or something
    like that.

    Any information anyone can provide to help me figure out
    how to handle this situation best will be immensely
    appreciated.

    Best regards,
    -Dan Allen
    Dlen.Com
    [url]www.dlen.com[/url]
    Dan Allen Guest

  2. #2

    Default Re: Cleaning Data for Load

    There are many ETL tools that will help you validate, cleanse and transform
    your data.

    DataJunction ([url]www.datajunction.com[/url]) is reasonably priced and ideal for
    flat-file conversions into SQLServer and other databases. You can download a
    free eval from their website.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --

    "Dan Allen" <dlendlen.com> wrote in message
    news:009501c347da$7089af60$a601280aphx.gbl...
    >
    > Hello everyone,
    >
    > I need to load a comma-delimited text file to a SQL Server
    > 2000 table every day. I have no problem loading the data,
    > provided the data is 'clean.' However, the load fails on
    > little things, like a quotation mark embedded within a
    > string, or a non-date value in a date field.
    >
    > I am assuming I need to process the data before loading,
    > removing or fixing the bad records before loading. The
    > file comes to me from another company. I can get them to
    > fix problems, but before going that way, I want to have a
    > systematic way to scan through all the records they send
    > to determine which records are ok and which are bad, and
    > why.
    >
    > Number of records being handled is 50,000 records per day,
    > so this has to be a very efficient process.
    >
    > There must be a good utility for this. I cannot believe I
    > would have to write something by hand in VB or something
    > like that.
    >
    > Any information anyone can provide to help me figure out
    > how to handle this situation best will be immensely
    > appreciated.
    >
    > Best regards,
    > -Dan Allen
    > Dlen.Com
    > [url]www.dlen.com[/url]

    David Portas Guest

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139