Quick Tip: A flat-file database [Perl]

[sugarkane]

While a serious e-comm site would probably use a big-league database like Oracle, all that power is wasted on a small site. You can use Perl's 'hash' variable type to knock together a simple database.

Say you had a text file containing product information in the format:

product_code,name,colour,size,price

The following code will read the information into memory in an easily retrievable form

open(FILE,"filename.txt");
chomp(@lines=<FP>); 
close (FP);
foreach $i (@lines) {
($prod_code, $name, $colour, $size, $price)=split(/,/,$i);
$name{$prod_code}=$name;
$colour{$prod_code}=$colour;
$size{$prod_code}=$size;
$price{$prod_code}=$price;
}

You can then retrieve the information for a product with code 'PROD101' like this:

$code="PROD101";
print "Product $code is a $name{$code} in colour $colour{$code}, of size $size{$code} costing $price{$code}<Br>";

This isn't very scalable - you wouldn't try and run a huge catalogue like this, but for a few hundred products it works fine.

[Brett_Tabke]

<-- King of flatfiles.

I live and eat flat files (preferrably pipe ¦ delimited flat files ¦ never was a csv fan).

I get what you were doing there with tip (KISS). I tend to do my flat files differently to make for easier sorting.

I push the flat file (@new) it over into a hash - like this example from a update to stickymail's address book:

foreach $item (@new) {
($tser, $type, $ifolder, $adrname, $atimeposted, $adrrealname, $adremail, $adrphone, $adrfax, $adrurl, $adrcontact, $adrnote, $adricon) = split(/\¦/,$item);
$t = $atimeposted . "¦" . $ser; #default to date
$t = $ser if ($sortfield eq "serial");
$t = "$adrname¦$atimeposted¦$ser" if ($sortfield eq "from");
$t = "$adrrealname¦$adrname¦$ser" if ($sortfield eq "realname");
$t = "$adremail¦$adrname¦$adrrealname¦$ser" if ($sortfield eq "email");
$t = "$adrurl¦$adrname¦$adrrealname¦$ser" if ($sortfield eq "url");
$t =lc($t);
$HITS{"$t"} = $item;
}

Now to spit them out in sorted fashion is as easy as:
foreach $key (sort keys %HITS) {
$line=$HITS{$key};
($tser,$type,$ifolder,$adrname,$atimeposted...) = split(/\¦/,$line);
}

Sure makes updating easier.

[physics]

Is there a spreadsheet program (for windows) which will allow you to save files as pipe-delimited or whatever? Excel lets you open files with any delimiter, but I can't get it to let me save files with a pipe or other delimiter.

[rcjordan]

I rarely work in spreadsheets, but when I do, I just "select all" and paste into a text file. Then replace ^t with pipe.

[physics]

Thanks!

[physics]

Well, I'll be a monkey's uncle! In StarOffice I opened a file that was pipe delimited and then played with it and saved it as a .csv file. Instead of commas it still used pipes :) Except I feel I can do without the quotes, but you can't have everything... here was the output:
"field1"¦"field2"¦"field3"
"tee"¦"hee"¦"hee"
Well, that wasn't really the file, but you know what I mean :) However, if you want to do this 'from scratch', i.e. create a new file and then save it as pipe-delimited it appears this isn't possible. When you go to save as .csv, you can choose , ; : tab or space, but not pipe.

[physics]

Weird! Now when I try this it don't work. Thats computer programs for you ;)

[tedster]

> Excel lets you open files with any delimiter, but I can't get it to let me save files with a pipe or other delimiter.

From Excel, I do "Save As" and choose .csv from the list to get a comma delimited file. Then use a text editor to switch out the commas for the delimiter of choice.

[Josk]

don't forget about dbi:csv...its a database driver for csv files. (each csv file is considered a table). If you want to go down the excel route, there's also a dbi driver for that as well... (CPAN for details)

[Brett_Tabke]

Yes, I've used that before too Josk. The problem I have with it, is the same problem with most dbi programs - slow...

[Brett_Tabke]

Also, I just noticed something about SK's original post. Wow - a year ago already.

open(FILE,"filename.txt");
chomp(@lines=<FP>);
close (FP);

If speed is critical, do the chomp after the close. It's pretty memory intensive.

open(FILE,"filename.txt");
@lines=<FP>;
close (FP);
chomp @lines;

[Josk]

> Its slow...

Yes...everything I do now with lists is either in a single pass, or with a proper database and do much of the processing through the sql. This assumes that the database it more optimised than my code...

[physics]

This post sure is getting old now ;) But I had to correct what I said before. It *is* possible to save any spreadsheet file as a pipe delimited text file with StarOffice/OpenOffice. Here is the best way I found:

1) Open your Excel or other spreadsheet file with StarOffice and Go to save as.
2) Select "File Type Text/CSV"
3) Unselect the "Automatic file name extension box" and rename the file foo.pipe or whatever instead of foo.csv... why not?
4) Click Save
5) Delete whatever is in the "Field Delimiter" box and just type ¦ (pipe)
6) Delete the "Text Delimiter" quotes and leave that blank (if you want).
7) Click OK.

Now you should have
foo1¦foo2¦foo3
goo¦2¦choo
roo¦7¦koo
...

Another trick I found was that if you open a text file you should save it in StarOffice or Excel format temporarily because if you don't you're not allowed to do things like 'insert column' and stuff like that. Then just follow the above procedure to re-save it as a .pipe file. I don't think that Micro$oft allows you to do this so CHEERS OpenOffice!

[physics]

BTW, if you have trouble opening a file with the extension .pipe or .txt as a spreadsheet in soffice try the following. Go to open a new file and then either type in the name of the file or select it from the list. After you do this you can select type->text/csv Then you will be propted to specify the delimiters, etc. Otherwise it will open it as document and not a spreadsheet. (Note to openoffice developers... maybe some autodetect function would be nice, or a button like 'open as a spreadsheet' or an option for Text/Spreadsheet)