 Using Text Importer via SQL*Plus [message #254751] |
Fri, 27 July 2007 16:54  |
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 #254763 is a reply to message #254755] |
Sat, 28 July 2007 00:51  |
 |
Littlefoot
Messages: 21823
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
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 #256267 is a reply to message #254763] |
Fri, 03 August 2007 10:59 |
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 #256312 is a reply to message #256310] |
Fri, 03 August 2007 15:01 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
