Re: [sqlite] Stitching together Text Files into a New Database

[Tim Romano]

1. Define a separate file in SQLite for each campaign, with the three
columns you need:

CAMPAIGN1
Time  number  (assuming the values are always numbers; use TEXT if they
contain a mix of letters/numbers)
TagA  number
TagB  number

CAMPAIGN2
Time
TagA
TagB

Etc.

2. Import each of your text files into the appropriate SQLite table.   N.B.
How are columns demarcated in your text files? Tabs? Spaces? Commas? You may
need to clean the text files up before importing so that a single character
is the column delimiter.

3. Create an amalgamation table where all of the data from the separate
campaign tables can later be merged:

CAMPAIGNS
id INTEGER PRIMARY KEY AUTOINCREMENT,
Source text
Time number
TagA number
TagB number


4.  After you have imported the individual campaign text files into their
respective CAMPAIGN# tables, you can copy the data from each of those tables
into your amalgamated CAMPAIGNS table  by executing this query:

insert into CAMPAIGNS
(source, time, TagA, TagB)
select   'C1' as source, time, tagA, TagB from CAMPAIGN1

5. Repeat step #4 for each CAMPAIGN# table, changing the [source] column
value in your select-clause : 'C1', 'C2','C3', etc.

At the end of the process, your CAMPAIGNS table will have five columns and
look like this (hypothetical data):

id source  time  taga   tagb
1  C11231000   199
.
.
.
123478  C7 188  4567885

You can afterwards create indexes on the columns to speed up queries. E.g.
you might want an index on source if you frequently need to ask a question
about the rows from the a particular campaign.

There are a variety of GUI tools available for SQLite. The one I use most
often is a plug-in for Firefox and is found here:
http://code.google.com/p/sqlite-manager/


Regards
Tim Romano
Swarthmore PA

On Wed, Aug 25, 2010 at 8:42 AM, Lorenzo Isella wrote:

> 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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Stitching together Text Files into a New Database

[Oliver Peters]

Lorenzo Isella  writes:

[...]

if you've no idea where a db can help you and if you don't have time to
investigate this and the possibilities a db offers you my suggestion is:

import your 3 or 5 files into a spreadsheet, add a column for your different
campaign and that's it (time: ~ 1min or less per file)

Oliver



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Stitching together Text Files into a New Database

[Lorenzo Isella]

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
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users