Mapping info question
Mapping info question
(OP)
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:
Code1 Code2 Breed DogID Description
B1 C2 Collie 43 brown with spots
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.
Code Breed DogID Description
198 Collie 43 brown with spots
Please let me know if you have any thoughts or ideas where I could find information.
Code1 Code2 Breed DogID Description
B1 C2 Collie 43 brown with spots
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.
Code Breed DogID Description
198 Collie 43 brown with spots
Please let me know if you have any thoughts or ideas where I could find information.
RE: Mapping info question
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.
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.
3. Try 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.
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!
RE: Mapping info question