Flat File Database

[meghatronic]

I have a 28mb archaic flat file database (made up of combined individual files) as such:

#BEGIN_FILE 1_pp.txt
# Field1
Blah

# Field2
blum
bop
wop
#Field 3
tada

#END_FILE

How do I go from this into something I actual understand? I.e. tab delim. or SQL.... even access! or excel...

Ideally I'd like it as:

1_pp*Blah*blum,bop,wop*tada
2_asdad*asds*adsasd*adsasd
3_asdads*asd*asda*da*asca

(where * = tab)

Any clues?

 

[Buttons]

What is the relevance of the 3 values for field 2? Is that one file a single record or depicting three tables?

I'd normally just end up doing some text manipulation with textpad macros and do global replaces to turn it all into valid SQL insert statements but as it stands I don't get the proposed structure.

 

[meghatronic]

Well, looking at the data file, some 'fields' contain multi line values.

EDIT - The 'file' depicts one record, containing three fields.. (in practice there are thousands of records and tens of fields).

 

[skiba]

some fancy sed action?

 

[meghatronic]

some fancy sed action?

Hmmm... possibly... but it'd have to be mighty fancy!

 

[DAVEM]

Could write a Java / Perl app with some sort of string tokenizer I guess

 

[Buttons]

Well, looking at the data file, some 'fields' contain multi line values.

EDIT - The 'file' depicts one record, containing three fields.. (in practice there are thousands of records and tens of fields).

Oh right, think I see now, the SQL Server import utility BCP should be able to handle this, if not the GUI version will but is a bit over complex. It's a long time since I used it.

First global replace "#BEGIN_FILE " with ~ (as an example)
Then (if your editor can handle it), replace carriage returns with commas
Then global replace "# Field1" with |

Then you should be able to import into most databases using ~ as your record delimeter and | as your field delimeter.

If you can't replace carriage returns then it gets a bit tricky but there are definitely free editors that can so ho hum.

That is how I'd do it.

 

[meghatronic]

There are different fields, though.. so I can't replace field 1 with ¬ etc...

Unless replace all the field x with ¬ but there are oodles of fields..!

 

[ByteJuggler]

Hmm, I don't think this should be output in the way you suggest. I would probably suggest either a single table with 3 fields: File, Field, Value. Then have File and Field repeated for each value in the table. Primary key would then be (File,Field).

I might knock up something in awk or ruby or something to do that conversion if I can be arsed... (can you post up a more meaningful test file with more than one "file" in it?)