Import data into xls to bank using PLSQL Developer

[Deniskywalker]

Good afternoon everyone!

I have some data in a worksheet and need to import this data into a table in the database.
Are more than 1,000 records in the column

Is there a way to get this data from the column of this worksheet and insert it on my table? Is it possible to do using PLSQL Developer?

Thanks! :-O

[DanielNN]

Hello Deniskywalker,

I have some doubt what you really want to do:

1- You already have a table and have a worksheet (which represents the columns From the table) and want to insert the data in it?
2-or, do you want to update the table data?
3-or, do you want to update just any columns in the table?
4-or, do you want to create a table from the worksheet?

If it is only to enter (situation 1), you can query the table with an "for update" at the end, unlock the table to be able to enter the records, copy the data from your worksheet Necklace on the table. Then only the Commit.

[Deniskywalker]

Hi Daniel, good morning!

That's right, it's Case 1!

I created a table with only one column of the varchar2 type. I have a spreadsheet with a column, with the records that must be entered in the table. As many are, it is unfeasible to make the insert of one by one.
I needed to know a simpler and more effective method if any, of course.

Thanks for the tip.

[nelson.anchite]

Dude,

sincerely I think it's not the best way I'll tell you, but it works.

1. Give a Select for Update on your table;
2. DPOs in PL / SQL Click on that padlock on top of the results;
3. Copy the XLS data.
4. Click the column where you want to insert the data and paste.

It will work I have done a few times.

Embrace.

[Deniskywalker]

Dear Nelson,

I did something very similar.

I used Rowid and it solve my problem.

Select all

SELECT ROWID, A.* FROM MINHA_TABELA A;

So, click the padlock, inserted the information and was.

Thanks!

[nelson.anchite]

That's it ... In case it works the same rowid or update form.

[adrianoturbo]

Brothers, there is a practical and quick way to do what they need: simple to make a data load on the bank using SQL Loader.
Next Let's go from How to Here:
Step 1
- In your worksheet adds a column between each column and in these columns insert a point and virgulum (;) to all lines;
- Select all columns and rows from which you want to import and copy to Notepad and then save Notepad with the name of the spreadsheet with the TXT extension;
- Open a new notepad Create a file with any name with the SQL, SQL.SQL extension, for example, and inside this file insert the following parameters below and save

Select all

 
load data 
infile 'suaplanilha.txt' 
append into table tabela 
fields terminated by ';' optionally enclosed by '"' 
(coluna1,coluna2,coluna3,coluna4) 

OBS1: In line: Infile, 'Suaplanilha.txt' is the name of the file of the worksheet that copied and saved in TXT
Obs2: Online: Append Into Table Table, should be the name of the table you want to do The load
Obs3: On line: (column1, column2, column3, column4), these names should be replaced by the names of the columns of your table.

Step 2
- Create a folder with any name in C: Windows and copy that two files with .sql and .txt extension to that folder.
- Open the command prompt with cmd or the older rsrs and give a CD to folder created and give a dir to list the files.

Step 3
Let's make it happen inside the folder with the files
Enter the following command line:
SQLLDR UserID = System @ Adriano / password control = sql.sql

Just give an enter and on a few seconds the data will be imported into your table.

Well, this is my brothers, I hope I have helped.

More information Access this nice and English explanation of Oracle below: http://docs.oracle.com/cd/B19306_01/ser ... ncepts.htm

[nelson.anchite]

Very nice poxa ... thanks !!

[alexbarbosa]

Take a look at this link that will help you a lot.
http://www.alexbarbosa.info/2016/06/29/ ... no-oracle/