Post reply

Data Import from CSV file

  • November 25, 2012 at 4:17 am

    #393663

    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

  • November 25, 2012 at 4:21 am

    #1562029

    So what is your question?

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • November 25, 2012 at 4:25 am

    #1562030

    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.

  • November 25, 2012 at 5:09 am

    #1562033

    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.

  • November 25, 2012 at 5:15 am

    #1562034

    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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • November 25, 2012 at 5:20 am

    #1562035

    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

  • November 25, 2012 at 5:23 am

    #1562036

    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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • November 25, 2012 at 5:27 am

    #1562037

    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.

  • November 25, 2012 at 8:40 am

    #1562050

    Any suggestion on this.......

  • November 26, 2012 at 10:48 am

    #1562316

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

  • November 26, 2012 at 11:47 am

    #1562342

    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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • November 26, 2012 at 5:31 pm

    #1562468

    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.

  • November 26, 2012 at 8:24 pm

    #1562475

    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)

  • November 26, 2012 at 10:27 pm

    #1562499

    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.

  • November 27, 2012 at 7:23 am

    #1562671

    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

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

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

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