Post reply

Split data

  • July 11, 2008 at 7:06 am

    #190369

    Hi,

    Front end guy's are sending the column names in one string and record at a stretch in a another string by using some delimeter to differentiat the column and record. For column differentiation they used ~ and for record #.

    Ex:

    column_string:

    'customer_id~customer_name~Address1~Address2~City~State~Pincode~Age'

    Data_String:

    '1~Name1~Address1~Address2~Chennai~Tamil Nadu~5744856~85#

    2~Name2~Address2~Address3~Madurai~Tamil Nadu~57448562~80#

    3~Name3~Address3~Address4~Mysore~Karnataka~570007~79#

    4~Name4~Address4~Address5~Bangalore~Karnataka~570007~52#

    5~Name5~Address5~Address6~Belgum~Karnataka~570006~74#

    6~Name6~Address6~Address7~Delhi~Delhi~5700085~64#

    7~Name7~Address7~Address8~Ramnagar~Karnataka~570042~41'

    How do I split and store this data into one single table? I need an output as follows:

    customer_id customer_name Address1 Address2 City State Pincode Age

    1 Name1 Address1 Address2 Chennai Tamil Nadu 5744856 85

    Can anyone help me out to resolve this issue

  • July 11, 2008 at 7:12 am

    #841458

    There's an article here: http://www.sqlservercentral.com/articles/TSQL/62867/

    It has data on efficient string splitting.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • July 11, 2008 at 8:16 am

    #841525

    I would put that method of sending data to your database into the "bad idea" bucket. You should try to talk your front-end guys into doing this in a better way.

  • July 11, 2008 at 7:35 pm

    #841916

    For a 2 dimensional split, see the following...

    http://www.sqlservercentral.com/articles/T-SQL/63003/

    The real problem is going to be how to make a table from that... you will need to use the parameters in dynamic SQL...

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

  • July 11, 2008 at 9:46 pm

    #841930

    I would get the fire and sword out and demand another tier do this work. My tier CPU is allocated to set based operations. not gross string sex. I have no flexiability on this subject with my developemnt group. (Untill my boss says shut it and do it.)

  • July 11, 2008 at 10:14 pm

    #841932

    I don't see any data type definitions. Should we just assume that all columns are varchars?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • July 12, 2008 at 9:22 am

    #841992

    Hi,

    This problem arise because we didn't able to read the excel / csv file from ftp location.

    From local server I can read excel / csv file using openrowset function.

    So, Now front end guy's are reading excel file from ftp location and sending these two string to store this data.

    Can anyone help me to resolve this issue... either by giving solution to read the excel / csv file from ftp location or by splitting this data and store it in the table as shown above format.

    Thanks for all your support..

  • July 12, 2008 at 9:30 am

    #841995

    Eric Inman (7/11/2008)

    I would get the fire and sword out and demand another tier do this work. My tier CPU is allocated to set based operations. not gross string sex. I have no flexiability on this subject with my developemnt group. (Untill my boss says shut it and do it.)

    T-SQL and the cpu handles that type of gross string sex just fine and in a relatively set based fashion. 🙂 But, I agree... why would anyone in their right mind try to import the contents of a spreadsheet like this? There are much better ways.

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

  • July 12, 2008 at 9:43 am

    #841996

    keerthy.vishweshwarachar (7/12/2008)

    Hi,

    This problem arise because we didn't able to read the excel / csv file from ftp location.

    From local server I can read excel / csv file using openrowset function.

    So, Now front end guy's are reading excel file from ftp location and sending these two string to store this data.

    Can anyone help me to resolve this issue... either by giving solution to read the excel / csv file from ftp location or by splitting this data and store it in the table as shown above format.

    Thanks for all your support..

    First, who's designing this? Who ever it is, you need to talk some sense into them... have them download the Excel file you're trying to read and then use OpenRowSet to read the spreadsheet. Or, have someone who knows how to do it use SSIS do the FTP download and the necessary converion to a table. I can't give you the details on how to do that because I always separate church and state like Eric suggested... I have the front end download the FTP file and then call a proc that will read it using OpenRowSet.

    Like Eric said, having the front end pass the data from the spreadsheet to the proc in the form of a CSV parameter is just insane even though T-SQL can handle it.

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

  • July 12, 2008 at 5:02 pm

    #842030

    I''d be happy to help if you would answer my question: How should I determine the data-types? Should I just set everything to varchar?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • July 13, 2008 at 8:26 am

    #842093

    Hi,

    Consider all the fields as varchar. As we doesn't have an access to SSIS we will be unable to do this.

    Do you guy's think that copying the csv/excel file to local server through front end application and reading the same using openrowset function. Is it the best way to resolve this issue?

    Thanks again...

  • July 13, 2008 at 10:27 am

    #842099

    That was the recommendation I made above... so, Yes.

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

  • July 13, 2008 at 10:28 am

    #842100

    Hi,

    All the column need to be considered varchar. As we doesn't have an access to ssis we couldn't able to access the FTP file directly from backend.

    So, Do you guy's think that copying csv/xls file from front end application to local server and access the same using openrowset function is the appropriate solution for this?

    Thanks Again...

  • July 13, 2008 at 10:32 am

    #842101

    You'll find that double posting within a thread will piss people off worse than normal double posting. 😉

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

  • July 13, 2008 at 10:48 am

    #842105

    Hi,

    Yes, all the fields considered to be varchar data type

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

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