#!/usr/bin/perl use Mysql; use Text::CSV_XS; use strict; use Data::Dumper; # MySQL CONFIG VARIABLES my $host = "localhost"; my $database = "ksa"; my $user = "root"; my $pw = ""; my $connect = Mysql->connect($host, $database, $user, $pw); $connect->selectdb($database); my @files = glob('*.csv'); my $csv = Text::CSV_XS->new({ binary => 1, escape_char => '\\', allow_loose_quotes => 1, allow_loose_escapes => 1, }); foreach my $file (@files) { open my $fh, "<", $file or die "file.csv: $!"; my $line = <$fh>; $line =~ s/^\xef\xbb\xbf//; $csv->parse($line); my @fields = $csv->fields; foreach my $field (@fields) { $field =~ s/\s/\_/g; $field = lc($field); } $csv->column_names( @fields ); my $table_name = $file; $table_name =~ s/\.csv$//; $connect->query("DROP TABLE IF EXISTS $table_name"); my $create_sql = "CREATE TABLE $table_name (record_id INTEGER AUTO_INCREMENT PRIMARY KEY ". ( join('', (map {( ", $_ varchar(100000)" )} @fields)) ) . ", import_special varchar(100) )"; $connect->query( $create_sql ); my $status; my $line; while ($line = <$fh>) { $status = $csv->parse( $line ); if (! $status) { print "Status: $status\n"; print $csv->error_diag() . "\n"; print $line; die(); } my @values = $csv->fields(); foreach my $value (@values) { $value =~ s/"/\\"/g; } if ( $#values < $#fields ) { $values[ $#fields ] = '0'; } my $insert_query = "INSERT INTO $table_name (". join(', ', @fields) . ") VALUES (". join(', ', map{( '"'.$_.'"' )} @values) . ")"; $connect->query( $insert_query ) or failed( \@fields, \@values, $insert_query); } print "File $file imported\n"; } sub failed { my ($f, $v, $i) = @_; print Dumper($f); print Dumper($v); print $i; die(); }