Post reply

Normalizing and loading data from csv data file

  • June 15, 2009 at 2:05 pm

    #222616

    I have a csv file with data which is represented in the table as follows

    Create table test1 (

    Sid int

    ,Name varchar(20)

    ,Gender Char(1)

    ,siteid varchar(20)

    ,sitename varchar(20)

    ,readingtestdate smalldatetime

    ,readingscore int

    ,mathtestdate smalldatetime

    ,mathscore int)

    Insert into test1 values(1,'ABC','M','M01','XX','5/08/2009',90,'',0)

    Insert into test1 values(1,'ABC','M','M01','XX','',0,'06/08/2009',95)

    Insert into test1 values(2,'DEF','F','VV1','VV-01','04/06/2009',88,'',0)

    Insert into test1 values(2,'DEF','F','VV1','VV-01','',0,'05/06/2009',77)

    Now I need to load the data into staging table along with row_number and then after loading the staging I have to use the staging table and I need to load the data into normalized tables like site table (siteid,sitename) and student table(sid,name,gender,siteid),exam table(sid,readingtestdate,readingscore,mathtestdate,mathscore).

    Please help me how to load into normalized tables using SSIS.

    If at all I am not clear please let me know

    Thanks,

    sai

  • June 21, 2009 at 1:02 am

    #1012974

    Hi

    I think what you have to do is to create a staging exactly are test1 table. Extract all records from csv and dump in to staging table. Now for loading site table, Use a oledb source, write a normal sql query like "select siteid, sitename from test1" and direct it to another transform oledb destination which points against site table.. Rest will be taken care by SSIS. Hope this should help you...

  • June 21, 2009 at 1:29 pm

    #1013069

    Does it HAVE to be done in SSIS?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • June 21, 2009 at 2:20 pm

    #1013076

    If you desire a clicky solution 😛

    1.) Create a flat file source for your CSV file

    2.) Add a "Sort" task to sort by SID

    3.) Add a "Conditional Split" to split by "ISNULL(readingtestdate) || YEAR(readingtestdate) == 1900" and "ISNULL(mathtestdate) || YEAR(mathtestdate) == 1900"

    4.) Add a "Merge Join" and add both output streams of previous split task. Chose the needed columns of both sides

    5.) Destination

    Done

    Flo

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • June 22, 2009 at 4:12 am

    #1013190

    saidwarak01 (6/15/2009)

    Now I need to load the data into staging table along with row_number and then after loading the staging I have to use the staging table and I need to load the data into normalized tables like site table (siteid,sitename) and student table(sid,name,gender,siteid),exam table(sid,readingtestdate,readingscore,mathtestdate,mathscore).

    Those tables aren't normalised. Specifically the exam table isn't. Tell me, without changing the table design, how you would store another subject's marks?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 23, 2009 at 9:01 am

    #1014015

    Just curious, is this a homework assignment or do you work for a school?

    Steve

Viewing 6 posts - 1 through 5 (of 5 total)

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