You didn't say Windows or Unix....
Here's Unix
#!/bin/sh
FILES="data1 data2 data3"
rm -f data.db
echo "create table t (file varchar,i1 int, i2 int, i3 int);" |sqlite3 data.db
for f in $FILES
do
exec<$f
while read line
do
v=`echo $line |awk '{ print $1 "," $2 "," $3 }'`;
sql="insert into t values('$f',$v);";
echo $sql|sqlite3 data.db;
done
done
==> data1 <==
123 1000 199
124 1001 200
==> data2 <==
123 1100 188
124 1101 189
==> data3 <==
125 800 805
126 801 806
Result:
CREATE TABLE t (file varchar,i1 int, i2 int, i3 int);
INSERT INTO "t" VALUES('data1',123,1000,199);
INSERT INTO "t" VALUES('data1',124,1001,200);
INSERT INTO "t" VALUES('data2',123,1100,188);
INSERT INTO "t" VALUES('data2',124,1101,189);
INSERT INTO "t" VALUES('data3',125,800,805);
INSERT INTO "t" VALUES('data3',126,801,806);
If you need windows a small C program will generate the SQL for you which you
can pipe into sqlite.
Just run this like this:
data data1 data2 data3 | sqlite3 data.db
#include <stdio.h>
#include <stdlib.h>
int main(int argc,char *argv[])
{
int i;
FILE *fp;
char buf[256],sql[256];
printf("CREATE TABLE t (file varchar,i1 int, i2 int, i3 int);\n");
for(i=1;i<argc;i++) {
fp = fopen(argv[i],"r");
if (fp == NULL) {
perror(argv[i]);
exit(1);
}
while(fgets(buf,sizeof(buf),fp)) {
int n,i1,i2,i3;
n=sscanf(buf,"%d %d %d",&i1,&i2,&i3);
if (n != 3) fprintf(stderr,"Error?? Not 3 items:
'%s'\n",buf);
sprintf(sql,"INSERT INTO t
VALUES('%s',%d,%d,%d);\n",argv[i],i1,i2,i3);
printf("%s",sql);
}
fclose(fp);
}
return 0;
}
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
________________________________
From: [email protected] on behalf of Lorenzo Isella
Sent: Wed 8/25/2010 7:42 AM
To: [email protected]
Subject: EXTERNAL:[sqlite] Stitching together Text Files into a New Database
Dear All,
I am quite new to databases in general and sqlite in particular.
I have a number of data files which are nothing else than text files with a
pretty simple simple structure: there are only 3 columns of integer numbers,
something along these lines
123 1000 199
123 1100 188
125 800 805
and so on.
The first column contains only non-decreasing times.
Each of these text files corresponds to a different data collection campaign
(let us call them A,B,C etc...).
I would like (with a minimal effort) to merge them into an sqlite database
where each column now has a name (time, ID tag A, ID tag B) and each record is
also marked according to its original dataset (i.e. looking at any entry, I
must be able to tell the the original data file it belongs to).
Any suggestion is really appreciated
Lorenzo
P.S.: of course in the future I may get some new datafiles to merge, hence it
is important that new data can be added effortlessly.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
