hi DAniel
i had similar probme and at the end i had to build my own
tool to migrate from csv to sql ...
maybe it will solve your problem too?
regards
e.
Цитат на писмо от Daniel de Matos Alves
<[EMAIL PROTECTED]>:
> 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
>
>
>
-----------------------------
Спортни залагания!
bg.sportingbet.com
// ivandimitrov
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <sys\stat.h>
#include <time.h>
char separator = ',';
char quotes = '\"';
int scanCSV(const char *fn)
{
FILE *stream;
int line, pos, maxpos;
int flg = 0;
char buff[5*1024];
int i, n = sizeof(buff);
stream = fopen(fn, "rt");
if (!stream)
return -1;
maxpos=0;
for (line=0;!feof(stream);++line)
{
fgets(buff, n, stream);
for (pos=0, i=0;i<n && buff[i] != 10 && buff[i] != 13 && buff[i] != 0;++i)
{
if (buff[i] == quotes)
{
if (flg)
{
flg = 0;
}
else
{
flg = 1;
}
}
if (flg == 0 && buff[i] == separator)
++pos;
}
if (flg == 0 )
++pos;
if (pos > maxpos)
{
maxpos = pos;
}
//printf("pos=%d\n", pos);
}
fclose(stream);
return maxpos;
}
static char *
RemoveTraillingSpaces(char *s)
{
int n, i;
n = strlen(s);
for (i=n-1;i>=0;++i)
if (s[i] == ' ')
{
s[i] = 0;
} else
{
break;
}
return s;
}
char *removespaces(char *s)
{
RemoveTraillingSpaces(s);
strrev(s);
RemoveTraillingSpaces(s);
strrev(s);
return s;
}
int isBlank(char *s)
{
for (;*s;++s)
{
if (*s != ' ')
{
return 0;
}
}
return 1;
}
//#define display() {ix = i+1;++pos;for (j=old;j<i;++j) {word[j-old] = buff[j];} word[j-old] = 0;printf(" +++ %d %s\n", pos, word);old = ix;}
#define display() \
{ \
ix = i+1; \
++pos; \
for (j=old;j<i;++j) \
{ \
char *p = &word[j-old];\
*p = buff[j]; \
} \
word[j-old] = 0;\
if (pos > 0) \
strcat(cout, " ,"); \
removespaces(word); \
if (i==old || isBlank(word)) \
strcat(cout, "NULL"); \
else \
{ \
strcat(cout, "\'");\
strcat(cout, word); \
strcat(cout, "\'");\
} \
old = ix; \
}
int processCSV(const char *fn, const char *tbablename, int maxpos)
{
FILE *stream;
int line, pos, ix;
int flg = 0;
char buff[5*1024];
char word[1024];
int i, j, n = sizeof(buff), old;
char cout[10*1024];
stream = fopen(fn, "rt");
if (!stream)
return -1;
old = 0;
for (line=1;!feof(stream);++line)
{
fgets(buff, n, stream);
old = 0;
*cout = 0;
sprintf(cout, "%u", line);
for (pos=0, ix = i=0;i<n && buff[i] != 10 && buff[i] != 13 && buff[i] != 0;++i)
{
if (buff[i] == quotes)
{
if (flg)
{
flg = 0;
}
else
{
flg = 1;
}
}
if (flg == 0 && buff[i] == separator)
{
display();
}
}
if (flg == 0)
{
display();
for (;pos < maxpos;++pos)
{
strcat(cout, ", NULL");
}
}
printf("INSERT INTO %s VALUES(%s);\n", tbablename, cout);
//printf("pos=%d\n", pos);
}
fclose(stream);
return maxpos;
}
int preambule(const char *tbablename, int fields)
{
int i;
printf("CREATE TABLE %s(", tbablename);
printf("excelid");
for (i=1;i<=fields; ++i)
{
if (i>0)
printf(", ");
printf("field%u", i);
}
printf(");\n\n");
return i;
}
/*
strtok
*/
int cvstosql(const char *filename, const char *tablename)
{
int mp;
struct stat statbuf;
time_t timer;
//struct tm *tblock;
/* gets time of day */
timer = time(NULL);
/* converts date/time to a structure */
//tblock = localtime(&timer);
// get information about the file
stat(filename, &statbuf);
printf("-- CVS to SQL [adimiti]: %s\n", __FILE__ " " __DATE__ " " __TIME__);
printf("-- file: %s\n", filename);
printf("-- from %s", ctime(&statbuf.st_ctime));
mp = scanCSV(filename);
if (mp<0)
{
fprintf(stderr, "Error opening %s\n", filename);
return mp;
}
printf("-- quotes: %c\n", quotes);
printf("-- separator: %c\n", separator);
printf("-- table: %s\n", tablename);
printf("-- created: %s", ctime(&timer) );
//printf("-- created: %s", asctime(tblock));
printf("-- felds: %d\n", mp);
printf("BEGIN TRANSACTION;\n");
preambule( tablename, mp);
processCSV( filename, tablename, mp);
printf("COMMIT;\n");
return 0;
}
//
void test(void)
{
cvstosql("sample.csv","tbl");
}
int main(int argc, char *argv[])
{
// test();
if (argc > 4)
quotes = *argv[4];;
if (argc > 3)
separator = *argv[3];
if (argc > 2)
{
cvstosql(argv[1], argv[2]);
} else
{
fprintf(stderr, "This program converts CSV-file to SQL(stdout)\nParams: csv-file table-name [separator [quotes]]\n");
return -1;
}
return 0;
}
