Mapping info question

[Shockerman]

Does anyone have any ideas on how I would go about setting up a sql program or any other type of program that could take a tab delimited ASCII file and be able to add the information to a table based on mapping constraints. For example, the ASCII file would look like this:<br>
Code1 Code2 Breed DogID Description<br>
B1 C2 Collie 43 brown with spots<br>
<br>
The program would need to understand that a dog from (B1, C2) would belong in my category# 198 so my table would look like this.<br>
<br>
Code Breed DogID Description<br>
198 Collie 43 brown with spots<br>
<br>
Please let me know if you have any thoughts or ideas where I could find information.

 

[Trailblazer]

Oooh, Shockerman, dealing with text files in sql is ugly. There are a few approaches you could try, but none are very elegant and you still have a lot of coding to do:<br>
<br>
1. I would write something (outside of Oracle and sql) to convert the data to fixed length fields. Believe me, doing that will make your life easier. Then create a temporary table to hold the raw data and use sqlloader to put the data into the table. Write a pl/sql program to read the rows from the temporary table, do whatever checks and data manipulations needed, and update your good table from there.<br>
<br>
2. If you don't want to convert your input data into fixed length fields, you could try loading the data into a temporary table that has one huge VARCHAR2 or LONG field. Then write a pl/sql program, using functions such as translate, rtrim, substr, etc, to delineate the data and update the good table.<br>
<br>
3. Try

http://www.knightwriters.com/kwshelp.htm

for another way to access text files in sql. It does involve messing with the initORA files and I'm always reluctant to do that. But it may be helpful.<br>
<br>
If I think of any other suggestions I'll let you know, but it's my humble opinion that you'll be better off using method #1. It just involves a little front end work of massaging the data into fixed length fields. Once that's done, the rest is easy. Good luck!<br>
<br>
<br>

 

[dba]

You might want to give the folks at DataJunction a call. They have a (pretty cheap) product that does a lot of transformations. -- dba<br>