I would like to know how to read an Excel spreadsheet in PL / SQL
Read Excel spreadsheet
- dr_gori
- 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
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
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 ...
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
=CONCATENAR("INSERT INTO tabela VALUES (" ; A1 ; A2 ; ");" )
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.
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 withd) You can use some PL / SQL Developer type program, which does it very well!=CONCATENAR("INSERT INTO tabela VALUES (" ; A1 ; A2 ; ");" )
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 ...
-
- 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.
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.
- dr_gori
- 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
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!
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!
- dr_gori
- 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
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:
This formula there of Excel, will generate a line like this In column 3
that is, copy the formula for everyone and you have the inserts you need ...
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:
= concatenar ("INSERT INTO MINHA_TABELA VALUES(" ; A1 ; ", '" ; A2 ; "');" )
INSERT INTO MINHA_TABELA VALUES( 34, 'BLABLABLA');
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.
Copy all cells from Excel and play in the worksheet it will run rows line.
Ex.
INSERT INTO TESTE (1,'TESTE 1')/
INSERT INTO TESTE (2,'TESTE 2')/
- dr_gori
- 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
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
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
-
- 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
- fsitja
- 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
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?
-
- Information
-
Who is online
Users browsing this forum: No registered users and 9 guests