# Jeff Walters, hiker_jjw@yahoo.com

Everyone who works with CGI/Perl and the Internet knows that database 
interaction is essential, from Web Store programs to a database of 
friendly quotes.  The problem I have always had is not the complexity 
of the database, but finding a simple and cost effective way to let a 
Website owner manage his/her own database; usually from a MS Windows 
system.

The most simple database is the '|' pipe delimited database, but 
owners soon find out that editing a large database in a text editor 
becomes a rather tedious task. Copying and pasting text is the only 
way to speed up the process of building the database, but it is often 
too easy to get columns of data mixed up. At the other end is the SQL 
database and MS Access. Some owners find SQL databases overwhelming 
and find MS Access too restrictive, hard to use and expensive.

My solution is to use the .CSV file format - Comma Separated Values. 
Most spreadsheet programs such as MS Excel will enable you to save 
files in the .CSV format. Most Website owners already have MS Excel 
on their computers and are also very comfortable using it. Copying 
and pasting fields is a very easy process and keeping columns aligned 
is no longer a problem.

Despite the name .CSV, the .CSV file format does allow you to use both 
commas and quotes in your database fields. I've noticed from MS Excel 
that there are two rules that MS Excel follows when it exports a 
spreadsheet to the .CSV format. First, if the cell contains a double 
quote (") then change all double quotes (") to two double quotes (""). 
Second, if the cell contains a comma or a double quote, then surround 
the data with double quotes ("___data___"). These two rules define the 
logic needed to split the .CSV file data line.

The logic in the SplitData subroutine below isn't exactly pretty, but 
it will handle just about every case I could come up with. To 
summarize it, the subroutine splits the CSV file data line by commas 
using the split() function. Since some of the fields may have 
contained commas, we then proceed to recombine the data based on the 
logic of the above two rules. The data fields are then returned in 
the array @output.

There is one small limitation to using MS Excel that could be annoying 
(hey, it's a MS product remember?). It seems that a text cell is 
limited to a maximum character length of 255. In most cases this is 
not a problem. On the other hand, Web Store database descriptions might 
exceed 255 characters, especially if you include HTML in the cell. The 
solution to this is to just add a description2 field to your database.

If anyone is interested in the code changes required to implement 
this subroutine into the Web Store program (web_store.cgi) just follow 
these simple changes.

1.) Open web_store_db_lib.pl

2.) Copy and Add the SplitData subroutine shown below to the
    web_store_db_lib.pl file just before 1;

3.) Find subroutine: check_db_with_product_id

Replace (or comment out #):
    @db_row = split(/\|/,$line);
with
    @db_row = &SplitData($line);

4.) Find subroutine: submit_query

Replace (or comment out #):
    @db_row = split(/\|/,$line);
with
    @db_row = &SplitData($line);

5.) Save and Updata via FTP.

A working version of the subroutine with the Web Store program can 
be found at www.outdoorstoreusa.com


############################################################
#
# subroutine: SplitData
#
#   Usage:
#     @output = &SplitData($data_line);
#
#   Input:
#     $data_line = the CSV file line to be separated.
#
#   Function:
#     This subroutine is used to split a (.CSV) comma separated
#     values database line ($data_line). This subroutine was
#     written to be used with a .CSV file exported from MS Excel
#     or another Spreadsheet Program.
#
#   Output:
#     Returns line data fields in a list array format (@output).
#
#
#   Written By: Jeff Walters at hiker_jjw@yahoo.com
#   Last Edit: 7/16/99
#   Please report any problems to hiker_jjw@yahoo.com
#
############################################################

sub SplitData
{
    local($data_line) = @_;
    local($combine,$combine_trigger,$data,$temp_data,$data_length, @split_data, @output);

    $combine_trigger = "off";

#   Remove the new line or carriage return characters.

    $data_line =~ s/\n+$//;
    $data_line =~ s/\r+$//;

#  First, we split the data by commas into the @split_data array.

    @split_data = split (/\,/, $data_line);

#  For each data element we will check to see if the elements need to be combined.

    foreach $data (@split_data)
	{

	if ($data =~ /\"/ || $combine_trigger eq "on")
		{

		$temp_data = $data;
		$temp_data =~ s/\"{2}//g;
		# Check to see if the data begins with an even or odd number of quotes
		if ( $temp_data =~ /^\"/ ) { $beginning_quotes = "odd" } else { $beginning_quotes = "even" }
		# Check to see if the data ends with an even or odd number of quotes
		if ( $temp_data =~ /\"$/ ) { $ending_quotes = "odd" } else { $ending_quotes = "even" }

		$temp_data = $data;
		$temp_data =~ s/\"//g;
		if ($temp_data eq "") { $all_quotes = "true" } else { $all_quotes = "false" }

		$data_length = length($data);
		# print "Combine  \n";
		# print "$beginning_quotes  $ending_quotes  $all_quotes<BR>\n";

		# $data contains a comma, but we do not need to combine.
		if ( $beginning_quotes eq "odd" && $ending_quotes eq "odd" && $all_quotes eq "false" )
			{
			$combine_trigger = "off";
			$data = substr($data,1,$length-1);
			$data =~ s/\"{2}/\"/g;
			push (@output,$data);
			}
		# $data is the beginning of a split field
		elsif ( $beginning_quotes eq "odd" && $ending_quotes eq "even" && $all_quotes eq "false" )
			{
			$combine_trigger = "on";
			$data = substr($data,1);
			$data =~ s/\"{2}/\"/g;
			$combine_data = $data;
			}
		# $data is a middle section of the split field
		elsif ( $beginning_quotes eq "even" && $ending_quotes eq "even" && $all_quotes eq "false" )
			{
			$combine_trigger = "on";
			$data =~ s/\"{2}/\"/g;
			$combine_data .= ",$data";
			}
		# $data is the ending of the split field
		elsif ( $beginning_quotes eq "even" && $ending_quotes eq "odd" && $all_quotes eq "false" )
			{
			$combine_trigger = "off";
			$data = substr($data,0,$length-1);
			$data =~ s/\"{2}/\"/g;
			$combine_data .= ",$data";
			push (@output,$combine_data);
			$combine_data = "";
			}
		elsif ( $beginning_quotes eq "odd" && $ending_quotes eq "odd" && $all_quotes eq "true" )
			{
			$data =~ s/\"//;  # remove one double quote
			$data =~ s/\"{2}/\"/g;
			if ( $combine_trigger eq "off" )
				{
				$combine_trigger = "on";
				$combine_data = "$data";
				}
			else
				{
				$combine_trigger = "off";
				$combine_data .= ",$data";
				push (@output, $combine_data);
				}
			}
		elsif ( $beginning_quotes eq "even" && $ending_quotes eq "even" && $all_quotes eq "true" )
			{
			$data =~ s/\"{2}/\"/g;
			$combine_data .= ",$data";
			}
		else
			{
			$combine_trigger = "off";
			$combine_data .= "ERROR";
			}

		}
	else
		{
		# print "Regular Push<BR>\n";
		push (@output, $data);
		}

	}

@output;

} # end of SplitData