Home » Other » Client Tools » Using Text Importer via SQL*Plus
icon1.gif  Using Text Importer via SQL*Plus [message #254751] Fri, 27 July 2007 16:54 Go to next message
bryanw
Messages: 3
Registered: July 2007
Location: Chicago
Junior Member
Hi all I hope I can find some help here and any help will be greatly appreciated. I have a text file that is produced from an excel spreadsheet. I can manually go into PL/SQL and use text importer to import the text file and use my saved definition file to then import that data into the database. However, I am looking to automate this process. So I guess I am asking is it possible to use the PL/SQL text importer functionality via the SQL*PLUS command line?

Thanks,
Bryan
Re: Using Text Importer via SQL*Plus [message #254755 is a reply to message #254751] Fri, 27 July 2007 22:00 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Your search - "PL/SQL text importer " - did not match any documents.

OK, I've been doing Oracle since V7 & I can't say I've ever heard this term before today.

I suspect that this is a locally produced utility.
If I'm correct, then I doubt anyone can authoritatively answer your question.

Use CUT & PASTE to show all the steps to successfully load into the DB a text file from a spreadsheet.
Re: Using Text Importer via SQL*Plus [message #254763 is a reply to message #254755] Sat, 28 July 2007 00:51 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'd say that Bryan didn't provide complete information. I believe his words were about the PL/SQL Developer's Importer utility.

How to automate this process? Well, there are software products which can "remember" what you do on your computer - you know, click "Tools", click "Importer", click "Open file", click ..., press <Return>, click "OK".

When you run what this kind of software remembered, you'd get exactly the same result unless something changed. For example, window wasn't on the exactly same place on the screen. Or you used different screen resolution. Or ...

So, this *might* work if you have an old PC whose only task would be running scheduled scripts made by this kind of software.

Another approach would be learning what "Importer" does. I don't know, but suspect it uses SQL*Loader (somewhere in the background) and inserts records from some kind of CSV file (which is created using ODBC, also somewhere in the background).

Now, automating SQL*Loader process isn't difficult - you'd write a batch script which would look like this:
SQLLDR scott/tiger control=import_data.ctl
You'd have to write a control file ("import_data.ctl") - see how it is done in the SQL*Loader Reference Manual.

Finally, you'd have to find a way "how to create a CSV file"? How do you get it now? If it is created/obtained automatically on some remote computer, could you map this directory so that it would act as your local drive and use the file? Something different? Whatever it is, just get the file!

MS Windows offers task scheduler (I believe you use this OS; if not, there must be a scheduler in OS you use) - it is a matter of several clicks to schedule a job which would take care about the rest of the process.
Re: Using Text Importer via SQL*Plus [message #254766 is a reply to message #254751] Sat, 28 July 2007 01:01 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you have an Oracle version >= 9 then you can use external tables and Oracle job/scheduler.

Regards
Michel
Re: Using Text Importer via SQL*Plus [message #256267 is a reply to message #254763] Fri, 03 August 2007 10:59 Go to previous messageGo to next message
bryanw
Messages: 3
Registered: July 2007
Location: Chicago
Junior Member
Thank you all. I figured out a solution using your tips. I had to use a combination of sqlplus to clear out the old data and sqlldr to insert the new data from the .csv file. It ended up being a little more complex than I wanted and I had to use a control file and a sql script but it gets the job done. Again Thanks for your help.
Re: Using Text Importer via SQL*Plus [message #256310 is a reply to message #256267] Fri, 03 August 2007 14:55 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
I had to use sqlplus to clear out the old data

Perhaps not! Did you check available loading methods? Would, perhaps REPLACE or TRUNCATE do the job for you (instead of SQL script you use)?
Re: Using Text Importer via SQL*Plus [message #256312 is a reply to message #256310] Fri, 03 August 2007 15:01 Go to previous message
bryanw
Messages: 3
Registered: July 2007
Location: Chicago
Junior Member
I tried replace but it gave me an error saying something like table is not empty. I'll try again. Personally I thought the error made no sense because I'm trying to replace the information. It may also have something to do with the rights on the particular login I'm using. Also I have to remove the data from the Db entirely because it is possible that some of the original entry data might change and thus not be replaced. I'm trying to limit duplicates as much as possible. Thanks for the help.

[Updated on: Fri, 03 August 2007 15:04]

Report message to a moderator

Previous Topic: query hang on toad
Next Topic: Drop User Command Help
Goto Forum:
  


Current Time: Thu Mar 28 09:55:12 CDT 2024