Load table with txt data

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
rafaelchagas.sp
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 17
Joined: Tue, 13 Oct 2009 4:52 pm
Location: Sorocaba - SP

I have a TXT file with the fields separated by Virgula and I need to load a table with these fields. Can anyone give me a tip?
User avatar
dr_gori
Moderador
Moderador
Posts: 5026
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

I suppose this file is inside the Oracle server.
Then:
* You can create a package and load the text via UTL_FILE (see here in the forum has several examples)
* You can use SQL * loader -> utility that serves Exactly to load text!
* You can use external-tables to read the text [url=http://glufke.net/2009/01/13/melhorias- ... es-do-10g/]link aqui

and must have other modes tb :-)

You decide what is the best, how often the file will be read, etc. It gives a researched here in the forum about these topics, has enough thing about it! If you have doubts, send us!
: -O
rafaelchagas.sp
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 17
Joined: Tue, 13 Oct 2009 4:52 pm
Location: Sorocaba - SP

Then
in fact my problem is the following,
I have a file in CSV format on my server in a format, I need to read this file and generate a txt in another format but with the same information.

Do you have any easier?
User avatar
dr_gori
Moderador
Moderador
Posts: 5026
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

Well, if you do not need to load this text into the database, you do not need to use Oracle to do this.

I think in your case, the ideal was to do this in C, Pascal, Python, Shell or any other language that will be much faster.
rafaelchagas.sp
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 17
Joined: Tue, 13 Oct 2009 4:52 pm
Location: Sorocaba - SP

I need to use PL / SQL.
RodrigoValentim
Moderador
Moderador
Posts: 367
Joined: Tue, 25 Mar 2008 3:41 pm
Location: Salvador - BA
Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

1. Do a procedure that reads the file in CSV format
2. Each line handles, you can enter into a Temporary table
3. Consult this table and unload in the desired format.

with Thomas passed alternatives, you can do this.
rafaelchagas.sp
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 17
Joined: Tue, 13 Oct 2009 4:52 pm
Location: Sorocaba - SP

Personal then I made an external table to load the fields from my CSV file.
The table was created, now how do I file data to be recorded in the table?

Used code:

Select all

 
 
create table testecsv ( 
  operacao   varchar2(1), 
  tppessoa   Varchar2(1), 
  cnpjcpf   number(14), 
  nome varchar2(55),   
) 
organization external ( 
  TYPE ORACLE_LOADER  
  DEFAULT DIRECTORY ARQFTP 
  ACCESS PARAMETERS ( 
        RECORDS DELIMITED BY NEWLINE 
        FIELDS TERMINATED BY ';' 
        MISSING FIELD VALUES ARE NULL  
	( 
        operacao   char(1), 
	tppessoa   char(1), 
	cnpjcpf   char(14), 
	nome char(55), 
	)  
  ) 
  location ('file.csv') 
) 
PARALLEL 5  
reject limit unlimited; 
 
RodrigoValentim
Moderador
Moderador
Posts: 367
Joined: Tue, 25 Mar 2008 3:41 pm
Location: Salvador - BA
Rodrigo Valentim
Analista de Sistemas
Oracle Developer

Campanha: Faça uma pesquisa antes de perguntar!!!

Just give a Select on your external table and ready, all the data will be there ... With this, you do what you want with the data!
rafaelchagas.sp
Rank: Programador Júnior
Rank: Programador Júnior
Posts: 17
Joined: Tue, 13 Oct 2009 4:52 pm
Location: Sorocaba - SP

But I did the Select and it does not return anything ...
The table was created but it is empty ...: Cry::?:
User avatar
NightSpy
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 49
Joined: Tue, 09 Sep 2008 4:18 pm
Location: SP

So, you have to read the file (open / read / close) and in the middle of it going to insert in your table.

via SQL PLUS, use UTL_FILE .. if you do a form for this, text_io solves.
DiegoTavares
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Posts: 10
Joined: Wed, 28 Apr 2010 9:10 am
Location: Jundiai - SP
Diego Tavares

Or you use the Tools> Muquest Importer Text. You can read TXT and CSV files
diegopedrao
Rank: Analista Sênior
Rank: Analista Sênior
Posts: 164
Joined: Fri, 22 Aug 2008 12:28 pm
Location: SP

Follow a script for you to read the text file.

Select all

 
 
create directory NOME_DO_DIRETORIO as 'C:NOME_DO_DIRETORIO\' ; 
 
drop table NOME_DA_TABELA; 
 
create table  NOME_DA_TABELA( text varchar2(4000) ) 
organization external ( 
  type oracle_loader 
  default directory NOME_DO_DIRETORIO  
      access parameters ( 
          records delimited by newline nologfile nobadfile 
          fields terminated by "|" ltrim 
      ) 
  location('NOME_DO_ARQUIVO.TXT') 
) 
reject limit unlimited; 
 
ATT,

Diego Monteiro
Post Reply
  • Information
  • Who is online

    Users browsing this forum: Google [Bot] and 1 guest