How to Upload a File Into Toad Oracle

Recently we had a customer accomplish out to the states asking about using the import characteristic of Toad Information Point.

They started out request near importing an Excel spreadsheet.  We were able to bear witness them how they could exercise that and fifty-fifty limit the number of rows that could be imported. They had a large spreadsheet and only wanted to import a subset of these records.

One of their concerns was that their spreadsheets were very large, with hundreds of thousands of rows.  For this we suggested that they consider breaking up the spreadsheet into multiple spreadsheets based on a data range.  They had eluded to the fact that at that place were ranges of customers/widgets/parts, whatever they were working with.  This made it easy, in our opinion, to break up the spreadsheet.

And then they asked nearly importing a text file because sometimes their customers would transport a text file instead of a spreadsheet.  They weren't sure TDP could import txt files.  Nosotros explained to them that they could in fact do this.

What yous will come across beneath are the steps involved to import a txt file.

---

To create this txt file I saved a spreadsheet to a text file in Excel.  Excel recognized the data, obviously, and saved it every bit a TAB delimited file, which I named tab-text-file.txt

And then I went in to TDP and opened the Import Export Data Sorcerer from the Tools menu:

Then I chose FILE from the data source menu on the left and decided what I wanted the target to be, in this case a table in a database:

Next I selected the file that I wanted to use for the Import:

When you click on the Open push the Import Wizard will open to the File Preview window.

Below, you can see that the Sorcerer has already determined that this is a TAB delimited file and it displays a sample of the information in the file.  You have some customization options you can cull but I am simply going to utilise the defaults and click NEXT.

This screen gives you the option to select the columns y'all want to import.  I am going to click Next and import all of them:

Before I had chosen to import into a database tabular array.  This window gives y'all the option to import into an existing tabular array or create a new table.  I am going to create a new tabular array named 'textdatatest'.

The side by side screen that opens is a verification/addition screen.  It shows the file I desire to import but you besides take the power to import multiple files.  You could take 2 or more text files or spreadsheets and import them into different tables in one task stream.

After clicking Side by side in the higher up screen the 'Salve Settings as Template' screen opens:

The 'Template' screen that only opened has several options to aid with the import process.  For my import I chose "Import Information Now" merely you may want to "Import Data Afterwards" and schedule this job or you can cull the "Send to Automation" option and automate this process.

Automation is very helpful if you are going to run this process repeatedly.  Maybe you run this process every dark, or even one time a week.  The automation feature allows y'all gear up up the job to run as needed and you tin only bank check the results after it runs.

This screen also allows you lot to capture any records that did not get processed and put them into a 'discard file'.  This gives you the chance to come across the 'bad' information, prepare information technology, and re-import it.

Y'all can also create a logfile for this job.

Clicking on the 'FINISH" push will start the import.  When it finishes, which may take a few minutes depending upon your data load, you will meet the window below.  This 'summary window' will tell y'all how many records were read from the import file and how many were inserted into the table.

Hopefully they match :)

Click "OK" to shut the summary window.  This will have yous dorsum to the "Object Explorer" window and you lot can meet the new table you just created and by clicking on the 'data' tab on the right-side window you can see that the information is there.

Now you have successfully imported text data into a new database tabular array and your business organisation processing tin go along on with the new data.

Y'all could just as easily import this text data into an existing tabular array, that is one of the options on the target window in the Wizard, and continue your concern processing with the new data in an existing table.

Feel free to experiment with this utility and let me know if you have whatever questions, or if you detect any cool tips and tricks you lot would like to share.

For more than information about Toad Data Point and all of its features visit united states of america on theToad Data Bespeak Community.  In that location you lot volition find videos and helpful information nigh the whole Toad family of tools.

-Mark Kurtz

@ToadSC_CHI

mackeyburstiest.blogspot.com

Source: https://www.quest.com/community/blogs/b/database-management/posts/importing-txt-files-with-toad-data-point-we-can-do-that