Re: [sqlite] Problem with import

[Dennis Cote]

Daniel de Matos Alves wrote:

PLEASE, read my e-mail, i really need help  ;-)

I am trying to import data from a file using sqlite3 command line, and 
the tcl bind. But I aways get error about the Number of  Columns. 
Sqlite always says that I am trying to put less columns than the 
number of columns defined in the table.


I created the table using the following SQL:


CREATE TABLE Cliente
(
  Chave_Cliente            INTEGER,
  "Data Cadastro"            TEXT,
  Nome            TEXT,
  Sexo            INTEGER,
  Chave_Logradouro            TEXT,
  Endereco            TEXT,
  Numero            TEXT,
  Complemento            TEXT,
  Bairro            TEXT,
  Cidade            TEXT,
  Estado            TEXT,
  Cep            TEXT,
  CPF            TEXT,
  RG            TEXT,
  "Telefone Trabalho"            TEXT,
  "Telefone Particular"            TEXT,
  Email            TEXT,
  "Data Nascimento"            TEXT,
  Chave_Convenio            TEXT,
  Chave_Situacao            TEXT,
  UsuarioQueAutorizou            TEXT,
  "Ultima Locacao"            TEXT,
  "Pre pago"            BOOLEAN,
  Chave_PrePago            TEXT,
  "Validade Pre Pago"            TEXT,
  "Encerrou Pre Pago"            BOOLEAN,
  "Composicao Pre Pago"            TEXT,
  "Quantidade Tiquete"            INTEGER,
  "Consumido Tiquete"            INTEGER,
  "Ultima Compra Tiquete"            TEXT,
  "Limite Debito"            TEXT,
  Referencia1            TEXT,
  Referencia2            TEXT,
  Referencia3             TEXT,
  "Fone Referencia1"            TEXT,
  "Fone Referencia2"            TEXT,
  "Fone Referencia3"            TEXT,
  Chave_Texto            TEXT,
  Observacao            TEXT,
  AbertaLC            BOOLEAN,
  LimiteLC            TEXT,
  DataAberturaLC            TEXT,
  DataEncerramentoLC            TEXT,
  DiaVencimentoLC            INTEGER,
  DataVencimentoLC            TEXT,
  DataUltPgtoLC            TEXT,
  ValorUltPgtoLC            TEXT,
  SaldoProximoPgtoLC            TEXT,
  GrupoAbriuLC            TEXT,
  UsuarioAbriuLC            TEXT,
  DataAbriuLC            TEXT,
  HoraAbriuLC            TEXT,
  GrupoEncerrouLC            TEXT,
  UsuarioEncerrouLC            TEXT,
  DataEncerrouLC            TEXT,
  HoraEncerrouLC            TEXT,
  MotivoEncerramentoLC            TEXT,
  "Data de Movimentacao"            TEXT,
  "Hora de Movimentacao"            TEXT,
  "Grupo usuario"            TEXT,
  "Usuario de grupo"            TEXT,
  TipoManutencao            TEXT

);


I used this tcl code to import


db copy datafile ";" "NULL"


or in sqlite3 command interface


.import datafile.txt Cliente ";" "NULL"


and Here we have one line of the data file:





1;2005-02-13 00:00:00;FRANCISCO EDNAN SABOIA PONTES ;0;R;NEWTON 
PARENTE ;1161;PROXIMO AO COMETA 
;JANGURUSSU;FORTALEZA;CE;00000000;78928958334;94006024023;32741966;32769280 
/34724873 - PUBLICO;;1977-08-17 00:00:00;NAO 
CONVENIADO;NORMAL;SUPERVISOR;2005-12-29 00:00:00;1;PRE - PAGO 
LANCAMENTO 24 HORAS;2005-04-30 
00:00:00;0;;0;0;NULL;.0000;CELULAR-MARIA ROSIMEIRE;TIO- 
CELSO;;8861-5632;3276-1949;;CONTRATO DE 
LOCACAO;;0;.0000;NULL;NULL;0;NULL;NULL;.0000;.0000;;;NULL;NULL;;;NULL;NULL;;2005-12-29 
00:00:00;1899-12-30 17:08:25;ADMINISTRAÇÃO;ANGELA;ALTERACAO


Daniel,

When you are using the ".import" command in the shell, you should supply 
only two arguments; the filename and the table name. You have given four.

I believe you are trying to set the separator character to ";", but that 
needs to be done with the ".separator" command before the ".import". 
Note, the ".separator" command does not need quotes around separator 
character (and if they are used, they become part of the string SQLite 
looks for to delimit values).

It also appears that you are trying to set a string to use for NULL 
values. This is not possible with the ".import" command. Since it 
imports all values as text there are no NULL values. The closest thing 
you get is an empty string (i.e when two separator characters occur back 
to back).

In summary try this sequence commands instead.

create table table_name ....
.separator ;
.import datafile.txt table_name

To verify the values that were imported use:

.mode line
select * from table_name;

HTH
Dennis Cote