Post reply

Delimiter of string data

  • August 22, 2007 at 7:27 am

    #728551

    Sorry Jeff, Here you go

    We receive data in txt CSV format. Once I import the file to a table I do string manipulation to load it into my schema.

    I know where you are getting at, you want the logic to be implemented at DTS import stage. But the problem is there are exceptions from the source data that will carried over and hence I am doing string manipulation after importing data to weed out exceptions which do not conform to the source data documentation.

    Do you see any other effective way of doing this.

    Thanks

  • August 22, 2007 at 7:35 am

    #728556

    Heh... DTS?  I don't ever use it .

    There might be a more effective way but I'd need to see what you have in the CSV file... can you post, say, the first 20 lines of data from it?  If it doesn't have a header line, please identify the fields contained in the text file so I can whittle at it for you a bit...

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

  • August 22, 2007 at 9:43 am

    #728613

    Commas are column delimiters and are table delimiters.

    Below are 4 records

  • August 23, 2007 at 8:23 am

    #728953

    I don't see any records in your post...

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

  • August 23, 2007 at 9:13 am

    #729001

    The file doesn't contain headers and the first four rows are filled with Null values. You really can't see the rows? I bet you're one of those people who can't see a white cow in a snowstorm.

  • August 23, 2007 at 10:59 pm

    #729175

    ... or nulls on a blue background, either

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

  • August 24, 2007 at 1:44 am

    #729195

    That's nothing  

    It's all the same to a blind man

    Far away is close at hand in the images of elsewhere.
    Anon.

  • August 24, 2007 at 6:58 am

    #729250

    Sorry guys my bad

    here you go

    D,CITI CORP,,I,NEW YORK,,

    D,SCIENCE & TECH II,,I,JAMES MADISON,,

    Sorry again

  • August 24, 2007 at 8:26 am

    #729289

    Thanks AM... I should have come right out and said it... can you provide the 10 or 15 rows of information, as well as a handfull of rows where the data seems to "double up", from the CSV?  Thanks.

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

  • August 24, 2007 at 9:48 am

    #729328

    As much as this may be sacrilege around here, there are other tools available besides SQL. Going back to the first post, it looks like there are two items on each line, each headed by a 'D' or 'I'. This would be a great place to run a little regex script to massage the file into an easier-to-import format (even gracefully handling the exceptions, though we still don't know what they are). Then DTS the stuff into a nice table with each component in its own field. Aamof, all of it can be done in DTS -- the regex, the import, the looping (sorry, don't see any way around it) and processing of each item.

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • August 24, 2007 at 4:41 pm

    #729391

    Yep...  lots'o different tools available, but I'm confused... first post shows 2 items each line, follow on shows 1 item each line... I'm just trying to get AM to post about 10 or 20 lines of text directly from the file 'cause I think the import will be easy once we know exactly what the data looks like.  Right now, I'm not sure because of the conflicting posts...

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

Viewing 11 posts - 16 through 25 (of 25 total)

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