Read Excel spreadsheet

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Post Reply
sydfilho
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Mon, 14 Mar 2005 3:36 pm
Location: porto alegre

I would like to know how to read an Excel spreadsheet in PL / SQL
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

It depends on several things:

Do you have to do this load just once?
a) You can use the SLQ * loader
b) can make a routine that reads the CSV file (delimited) - Text_io of Forms or UTL_File (in the bank)
c) You can create the inserts by hand in Excel (type, ride a formula with

Select all

 =CONCATENAR("INSERT INTO tabela VALUES (" ; A1 ; A2 ; ");" )
d) You can use some PL / SQL Developer type program, which does it very well!
and) if it is bank 9i onwards, can use external tables, etc.

The load must always be done (within a program)
a) Basically uses the SQL * loader
b) A UTL_FILE charge routine is used, etc.

What is your situation there ???
are many lines? Many fields? A lot of frequency?

That's it ...
Last edited by dr_gori on Thu, 18 Aug 2005 9:04 pm, edited 1 time in total.
sydfilho
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Mon, 14 Mar 2005 3:36 pm
Location: porto alegre

Thanks, I'll use the UTL_File. Thanks for the help
Dr_Gori wrote: depends on several things:

Do you have to do this load only once?
a) You can use the SLQ * loader
b) can make a routine that reads the CSV file (delimited) - Text_io of Forms or UTL_File (in the bank)
c) You can create the inserts by hand in Excel (type, ride a formula with

Select all

=CONCATENAR("INSERT INTO tabela VALUES (" ; A1 ; A2 ; ");" )
d) You can use some PL / SQL Developer type program, which does it very well!


The load must always be done (within a program)
a) Basically used SQL * loader
b) USA - If a UTL_File load routine, etc ...

What is your situation there ???
are many lines? Many fields? A lot of frequency?

That's it ...
LC_JK
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 46
Joined: Tue, 24 Aug 2004 2:02 pm
Location: São Paulo
Contact:

I would not like to use the sql * loader .......

would it have how I develop some procedure that does it?!

I have to pass line the line inside Excel (where each column of each line would be a table column) and go by inserts inside an Oracle table.
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

It's like I said ... it depends !!!

How many times will you have to do this load. Only 1 time? Or does it have to be an automatic routine that will always be executed?

If it is an automatic bank routine, it has 2 basic forms:
1. If it is in forms, you can use text_io
2. If the text file is inside the Oracle server, then it is with UTL_FILE

explain better what you want to make someone a and give help!
LC_JK
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 46
Joined: Tue, 24 Aug 2004 2:02 pm
Location: São Paulo
Contact:

It is a file that will run manually .... 1 or 2 times only.

The file will be read on my machine itself .... not on the server.

If you have an example, could you pass me?!
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

In this case, I think the easiest way is to set up an insert.

Type, let's say column 1 is the code, and column 2 is the call name.

Then you go in column 3 and poe the following formula in Excel:

Select all

= concatenar ("INSERT INTO MINHA_TABELA VALUES(" ; A1 ; ", '" ; A2 ; "');" )
This formula there of Excel, will generate a line like this In column 3

Select all

INSERT INTO MINHA_TABELA VALUES( 34, 'BLABLABLA'); 
that is, copy the formula for everyone and you have the inserts you need ...
LC_JK
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 46
Joined: Tue, 24 Aug 2004 2:02 pm
Location: São Paulo
Contact:

ok ... I understood ...

Now I use the UTL_File ... but as I would do to create a loop to read all the lines and running all the commands of the inserts of my file Xls?!
LC_JK
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 46
Joined: Tue, 24 Aug 2004 2:02 pm
Location: São Paulo
Contact:

Do you have any example there?!
User avatar
TBou
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 136
Joined: Thu, 05 Aug 2004 9:33 am
Location: Campo Grande - MS
Thiago Bourscheidt
thiago.info@apoiorural.com.br
Analista de Sistemas

You can add a / at the end of all lines ...
Copy all cells from Excel and play in the worksheet it will run rows line.
User avatar
TBou
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 136
Joined: Thu, 05 Aug 2004 9:33 am
Location: Campo Grande - MS
Thiago Bourscheidt
thiago.info@apoiorural.com.br
Analista de Sistemas

Ex.

Select all

 
INSERT INTO TESTE (1,'TESTE 1')/ 
INSERT INTO TESTE (2,'TESTE 2')/ 
LC_JK
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 46
Joined: Tue, 24 Aug 2004 2:02 pm
Location: São Paulo
Contact:

Thank you for the tip friend, but I would like to do this via pl / sql even !!
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

Search Here in the Forum by UTL_File
I know you have an example of reading. What changes, basically, is that you have to put get_line instead of put_line ...

and when it opens the file is with 'r' and not 'w'.
: -O
LC_JK
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 46
Joined: Tue, 24 Aug 2004 2:02 pm
Location: São Paulo
Contact:

Okay ... I found something ......

But whatever, thanks.
deniscezar
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 9
Joined: Fri, 27 Aug 2010 12:16 pm
Location: são paulo - sp

Does anyone know how you could read an XLS file through PL SQL? thanks
User avatar
fsitja
Rank: OraSauro
Rank: OraSauro
Posts: 611
Joined: Mon, 19 Jan 2009 4:29 pm
Location: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

In what part of what the staff posted above you found problems? What have you tried already? Any error message?
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 9 guests