I try to upload a csv file to access database using perl DBI. But keep failing.
The* number* and *type* of the field in csv file are not fixed. Might be integer or character. ( I was able to make it if they are fixed) -------------------------------------------------------start of code #connect to access database * $dbh = DBI->connect("dbi:ODBC:$DSN", { PrintError => 0, RaiseError => 1 * * }) or die "Can't connect to the database: $DBI::errstr\\n";* #Create a table * $sql = qq{ CREATE TABLE $table_name (id INTEGER NOT NULL, name VARCHAR(64))}; **<=* What if I don't know the *type* and* name* and * number* before I upload? * $sth = $dbh ->prepare ($sql ); $sth -> execute;* #Go through csv file and insert each row to table * open( CSV, "<", $csv_file ) or die $!;* * * * foreach my $line (<CSV>) { chomp $line ; my ( $field1, $field2) = split( /,/, $line );* * $sql = qq{ INSERT INTO $table_name values("$ field1","$field2") };* <= What if I don't know the *type* and* name* and * number* before I upload? Also, this code doesn't work because $field1,$field2 are variable. If I don't use variable, it works. * $sth = $dbh ->prepare ($sql ); $sth -> execute;* * }* #disconnect databse * $dbh->disconnect();* -----------------------------------------------------------end of code ** Could anyone show me how to modify my code or show me a template that can upload an unknown format csv file into Access database using Perl DBI? Thank you! -Tiffany