Data Import from CSV file

  • I have a CSV file which is having record like mentioned below.

    UserNameUser Id Type

    RamKumarram MSAD

    RamaCrama MSAD

    RamesKrames MSAD

    Rameshvramesh MSAD

    RamKumarramp PCG

    Ramig rami PCG

    Ramemrame PCG

    RampDramp PCG

    RampDrampX MSAD

    Here I need to import this in a DB table for which DDL is like mentioned below

    CREATE TABLE #USERTABLE([USERNAME] [varchar](100),[MSAD] [varchar](20),[PCG] [varchar](20))

    I need to import the data into the table.The logic is If Type is MSAD then it should update MSAD column of table with userid and if type is PCG then it should update PCG column also there should not be duplicate entry for a uername i.e. for RamKumar ther should be only one entry. with both MSAD and PCG column values.

    Expected output is mentioned below

    UserNameMSAD PCG

    RamKumarram ramp

    RamaCrama

    RamesKrames

    Rameshvramesh

    Ramig rami

    Ramem rame

    RampDrampX ramp

  • So what is your question?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (11/25/2012)


    So what is your question?

    I need to import the data into the table.The logic is If Type is MSAD then it should update MSAD column of table with userid and if type is PCG then it should update PCG column also there should not be duplicate entry for a uername i.e. for RamKumar ther should be only one entry. with both MSAD and PCG column values.

  • rhythm.varshney (11/25/2012)


    Phil Parkin (11/25/2012)


    So what is your question?

    I need to import the data into the table.The logic is If Type is MSAD then it should update MSAD column of table with userid and if type is PCG then it should update PCG column also there should not be duplicate entry for a uername i.e. for RamKumar ther should be only one entry. with both MSAD and PCG column values.

    I tried upinsert but it is not working for duplicate username.

    Please provide your input.

  • rhythm.varshney (11/25/2012)


    Phil Parkin (11/25/2012)


    So what is your question?

    I need to import the data into the table.The logic is If Type is MSAD then it should update MSAD column of table with userid and if type is PCG then it should update PCG column also there should not be duplicate entry for a uername i.e. for RamKumar ther should be only one entry. with both MSAD and PCG column values.

    I am not seeing a UserId column.

    Also, all you have done is stated your requirements. From these, you are assuming that I can guess what your question is, but I can't.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (11/25/2012)


    rhythm.varshney (11/25/2012)


    Phil Parkin (11/25/2012)


    So what is your question?

    I need to import the data into the table.The logic is If Type is MSAD then it should update MSAD column of table with userid and if type is PCG then it should update PCG column also there should not be duplicate entry for a uername i.e. for RamKumar ther should be only one entry. with both MSAD and PCG column values.

    I am not seeing a UserId column.

    Also, all you have done is stated your requirements. From these, you are assuming that I can guess what your question is, but I can't.

    UserId column is in CSV file not in SQL table.

    So if you see the first record(username is RamKumar) from CSV should insert userid value in MSAD column of table while 5th record with same username should update the same record with userid from CSV in PCG column of table

  • rhythm.varshney (11/25/2012)


    Phil Parkin (11/25/2012)


    rhythm.varshney (11/25/2012)


    Phil Parkin (11/25/2012)


    So what is your question?

    I need to import the data into the table.The logic is If Type is MSAD then it should update MSAD column of table with userid and if type is PCG then it should update PCG column also there should not be duplicate entry for a uername i.e. for RamKumar ther should be only one entry. with both MSAD and PCG column values.

    I am not seeing a UserId column.

    Also, all you have done is stated your requirements. From these, you are assuming that I can guess what your question is, but I can't.

    UserId column is in CSV file not in SQL table.

    Ahh, I see - I saw 'User' and 'Id' separated by a space and assumed they were separate columns.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (11/25/2012)


    rhythm.varshney (11/25/2012)


    Phil Parkin (11/25/2012)


    rhythm.varshney (11/25/2012)


    Phil Parkin (11/25/2012)


    So what is your question?

    I need to import the data into the table.The logic is If Type is MSAD then it should update MSAD column of table with userid and if type is PCG then it should update PCG column also there should not be duplicate entry for a uername i.e. for RamKumar ther should be only one entry. with both MSAD and PCG column values.

    I am not seeing a UserId column.

    Also, all you have done is stated your requirements. From these, you are assuming that I can guess what your question is, but I can't.

    UserId column is in CSV file not in SQL table.

    Ahh, I see - I saw 'User' and 'Id' separated by a space and assumed they were separate columns.

    Yes so I need first row of CSV should insert into table and the value for MSAD column will get the value of UserId of CSV and when again the 5th row will have same username it should update the same record of table by updating PCG column with userid of CSV.

  • Any suggestion on this.......

  • waiting for your valuable input guys........

  • rhythm.varshney (11/26/2012)


    waiting for your valuable input guys........

    You still have not asked a single question - there is nothing to respond to.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin (11/26/2012)


    rhythm.varshney (11/26/2012)


    waiting for your valuable input guys........

    You still have not asked a single question - there is nothing to respond to.

    please suggest the way to get the data into my DB table based on above mentioned logic i.e how can I import data from my CSV to DB table considering above logic.

  • rhythm.varshney (11/26/2012)


    Phil Parkin (11/26/2012)


    rhythm.varshney (11/26/2012)


    waiting for your valuable input guys........

    You still have not asked a single question - there is nothing to respond to.

    please suggest the way to get the data into my DB table based on above mentioned logic i.e how can I import data from my CSV to DB table considering above logic.

    Personally, I wouldn't bother with SSIS with such a task. I'd use BULK INSERT into a staging table and write some good old fashioned "upsert" code to get the job done.

    "Upsert" in this case (you're using 2005) means a simple conditional INSERT if the key data doesn't already exist and a simple conditional UPDATE if it does.

    Using such a staging table would also allow you to do a lot of pre-validation and error checking before you ever allow the external unknown data anywhere near your existing data.

    --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)

  • Jeff Moden (11/26/2012)


    rhythm.varshney (11/26/2012)


    Phil Parkin (11/26/2012)


    rhythm.varshney (11/26/2012)


    waiting for your valuable input guys........

    You still have not asked a single question - there is nothing to respond to.

    please suggest the way to get the data into my DB table based on above mentioned logic i.e how can I import data from my CSV to DB table considering above logic.

    Personally, I wouldn't bother with SSIS with such a task. I'd use BULK INSERT into a staging table and write some good old fashioned "upsert" code to get the job done.

    "Upsert" in this case (you're using 2005) means a simple conditional INSERT if the key data doesn't already exist and a simple conditional UPDATE if it does.

    Using such a staging table would also allow you to do a lot of pre-validation and error checking before you ever allow the external unknown data anywhere near your existing data.

    Thanks Jeff.

    Actually I did the same and suggested the same even wrote all UPSERT logic but my lead wants it to be implemented using ETL. Don't know why though I asked him that handling it in DB would be more easy to maintaing considering future changes and performance(as I read somewhere tha SSIS is not so good in update) also.

    So could you please suggest the approach to get it done via SSIS.

  • There is no good way of doing this in SSIS - I'm with Jeff on this one.

    If you want to put an SSIS 'wrapper' round it, something like this would work:

    1) Exec SQL: Truncate Staging table

    2) Dataflow: Insert to Staging table

    3) Exec SQL: UPSERT logic to move from Staging to main data table

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Viewing 15 posts - 1 through 15 (of 20 total)

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