Delimiter of string data

Question

Delimiter of string data

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

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

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

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)

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 3

Commas are column delimiters and are table delimiters.

Below are 4 records

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

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)

David McFarland SSChampion Points: 11815 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

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

David Burrows SSC Guru Points: 65142 More actions · Answer 7

That's nothing

It's all the same to a blind man

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

am-244616 SSCarpal Tunnel Points: 4936 More actions · Answer 8

Sorry guys my bad

here you go

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

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

Sorry again

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

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)

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 10

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 11

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)