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?
Load table with txt data
-
- Rank: Programador Júnior
- Posts: 17
- Joined: Tue, 13 Oct 2009 4:52 pm
- Location: Sorocaba - SP
- dr_gori
- 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
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
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
-
- 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?
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?
- dr_gori
- 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
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.
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.
-
- Rank: Programador Júnior
- Posts: 17
- Joined: Tue, 13 Oct 2009 4:52 pm
- Location: Sorocaba - SP
I need to use PL / SQL.
-
- 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!!!
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.
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.
-
- 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:
The table was created, now how do I file data to be recorded in the table?
Used code:
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;
-
- 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!!!
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!
-
- 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::?:
The table was created but it is empty ...: Cry::?:
-
- 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
-
- 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.
ATT,
Diego Monteiro
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;
Diego Monteiro
-
- Information
-
Who is online
Users browsing this forum: Google [Bot] and 1 guest