Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Mapping info question

Mapping info question

Mapping info question

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.

RE: Mapping info question

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:

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close