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
  

Poston Tue, 19 Apr 2005 5:30 pm

I would like to know how to read an excel spreadsheet in PL/SQL
sydfilho
Location: porto alegre

Poston Wed, 20 Apr 2005 9:20 am

Depends on several things: you have to make this load only once?
the) can use the SQL * Loader b) can do a routine that reads the CSV file (delimited)--at TEXT_IO Forms or the UTL_FILE (on the bench) c) can create INSERTS his hand into Excel (type, assembles a formula with the = CONCATENATE (\"INSERT INTO table VALUES (\"; A1; A2; \");\" )
d) can use some program type PL/SQL Developer, which makes it very well!
and) If Bank 9i onwards, can use external tables, etc.

The load should be always done (within a program)) Uses basically the SQL * Loader b) Uses a load routine UTL_FILE, etc. ..

What is your situation there???
Are many lines? many fields? Very often?

that's right ...
Last edited by dr_gori on Thu, 18 Aug 2005 9:04 pm, edited 1 time in total.
dr_gori
Location: Portland, OR USA

Thomas F. G

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

Poston Wed, 20 Apr 2005 1:11 pm

Thanks, I will use utl_file. Thanks for your help [quote = " dr_gori "] depends on several things: you have to make this load only once?
the) can use the SQL * Loader b) can do a routine that reads the CSV file (delimited)--at TEXT_IO Forms or the UTL_FILE (on the bench) c) can create INSERTS his hand into Excel (type, assembles a formula with the = CONCATENATE (\"INSERT INTO table VALUES (\"; A1; A2; \");\" )
d) can use some program type PL/SQL Developer, which makes it very well!


The load should be always done (within a program)) Uses basically the SQL * Loader b) Uses a load routine UTL_FILE, etc. ..

What is your situation there???
Are many lines? many fields? Very often?

that's right ...[/quote]
sydfilho
Location: porto alegre

Poston Fri, 19 Aug 2005 9:39 am

I wouldn't want to use the sql * Loader ... .... would I develop some procedure to do that?!

I have to go line by line within excel (where each column of each row is a column of the table) and go doing inserts within an Oracle table.
LC_JK
Location: São Paulo

Poston Fri, 19 Aug 2005 11:24 am

Like I said ... DEPENDS ON!!!

How many times do you have to make this load. Only 1 time? Or do you have to be a routine that will always be executed?

If it is a automatic routine Bank, has 2 basic ways: 1. If it is in the FORMS, you can use 2 TEXT_IO. If the text file is within the ORACLE Server, hence is with UTL_FILE Explain better what you want to do someone help aí!
dr_gori
Location: Portland, OR USA

Thomas F. G

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

Poston Fri, 19 Aug 2005 2:13 pm

is a file that will be executed manually. 1 or 2 times only.

the file will be read on my machine even ... not on the server.

If you tenhaalgum example, could you pass me?!
LC_JK
Location: São Paulo

Poston Fri, 19 Aug 2005 2:22 pm

in this case, I think the easiest way is to set up an INSERT.

like, let's say that the column 1 is the code, and column 2 is the Clientname.

Hence you'll in column 3 and poe the following formula in excel:
Code: Select all
= concatenar ("INSERT INTO MINHA_TABELA VALUES(" ; A1 ; ", '" ; A2 ; "');" )
This formula in excel, will generate a line like this in 3 column
Code: Select all
INSERT INTO MINHA_TABELA VALUES( 34, 'BLABLABLA');
IE, copy the formula to all and u have the INSERTS that needs ...
dr_gori
Location: Portland, OR USA

Thomas F. G

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

Poston Fri, 19 Aug 2005 2:25 pm

ok ... get it ... now I use utl_file ... but how am I supposed to do to create a loop to go reading all rows and performing all dos command inserts of my xls file?!
LC_JK
Location: São Paulo

Poston Fri, 19 Aug 2005 2:37 pm

do you have any examples?!
LC_JK
Location: São Paulo

Poston Fri, 19 Aug 2005 2:40 pm

You can add a / at the end of every line ... copy every cell in Excel Worksheet and play on it will run the lines.
TBou
Location: Campo Grande - MS

Thiago Bourscheidt
thiago.info@apoiorural.com.br
Analista de Sistemas

Poston Fri, 19 Aug 2005 2:42 pm

Ex.
Code: Select all
INSERT INTO TESTE (1,'TESTE 1')/
INSERT INTO TESTE (2,'TESTE 2')/
TBou
Location: Campo Grande - MS

Thiago Bourscheidt
thiago.info@apoiorural.com.br
Analista de Sistemas

Poston Fri, 19 Aug 2005 3:41 pm

Thanks for the tip mate, but I'd like to do this via pl/sql!!
LC_JK
Location: São Paulo

Poston Fri, 19 Aug 2005 4:08 pm

Search here on the forum because I know there's a UTL_FILE example of reading. What changes, basically, is that u have to put GET_LINE rather than PUT_LINE ...

And when you open the file with ' R ' and ' W '.
:-o
dr_gori
Location: Portland, OR USA

Thomas F. G

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

Poston Fri, 19 Aug 2005 5:47 pm

ok ... I thought already something ... but qualquer way, thanks.
LC_JK
Location: São Paulo

Poston Thu, 09 Sep 2010 1:59 pm

Anyone know how I could read an xls file through the pl sql? Thank you
deniscezar
Location: sao paulo - sp

Poston Thu, 09 Sep 2010 4:17 pm

In which part of that posted above you encountered problems? What have you tried already? Any error messages?
fsitja
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


  • See also
    Replies
    Views
    Last Post


      Return to PL/SQL

      Who is online

      Users browsing this forum: No registered users and 4 guests