#!/usr/bin/perl
# creates sql script from tab file exported from excel (or wherever)

use Getopt::constant(
	':prefix'	=> 'C_',
	'infile'	=> '',
	'outfile'	=> 'output.sql',
	'db'		=> '',
	'table'		=> '',
	'type'		=> "dos",
	'debug'		=> 0
);

# assume dos CRLF line separators
$/ = "\r\n" if C_type eq 'dos';

if ( -f ( C_infile ) && -r ( C_infile ) ) {
	open ( INFILE, C_infile ) or die ( "Cound not open " . '"' . C_infile . '"' . " for read:\n$!" );
}
else {
	die ( '"' . C_infile . '"' . " is not a readable file.\n" );
}

chomp ( $firstrow = <INFILE> );
@firstrow = split ( /\t/, $firstrow );

if ( C_debug > 0) {
	print "Columns:\n";
	print "\t$_\n" for @firstrow;
}

open ( OUTFILE, ">" . C_outfile ) or die ( "Cound not open " . '"' . C_outfile . '"' . " for write:\n$!" );

print OUTFILE "use " . C_db . "\ngo\n\n";

print "Data" if C_debug > 1;

$column_line = "\t( " . join ( ", ", @firstrow ) . " )\n";

while ( <INFILE> ) {
	chomp();
	@line = split (/\t/);
	if ( C_debug > 1 ) { print "$_\n" for @line }

	for ( @line ) {
		if	( $_ + 0 eq $_ or  $_ =~ /\(\)$/ or $_ =~ /null/i ) {
			next;
		}
		else	{
			$_ = '"' . $_ . '"';
		}
	}

	print OUTFILE
		"insert " . C_table . "\n" .
			$column_line .
		"values\n" .
			"\t( " . join ( ", ", @line ) . " )\n\n";
}

close INFILE;
close OUTFILE;