SHARE
TWEET

Untitled

a guest Jun 14th, 2017 50 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. #!/usr/bin/perl -w
  2. use strict;
  3. use DBI;
  4. use Text::CSV_XS;
  5. use Getopt::Long;
  6.  
  7. use constant USAGE => "usage: $0 [ -h hostname ] [ -o port ] --db dbname -u user -p passwd -t table file.csv [ file.csv ... ]";
  8.  
  9. GetOptions(
  10.     'h|host=s'  => \( my $opt_host = "stncss00.tnc" ),
  11.     'o|port=i'  => \( my $opt_port = "3306" ),
  12.     'db=s'      => \( my $opt_dbname ),
  13.     'u|user=s'  => \( my $opt_user ),
  14.     'p|pass=s'  => \( my $opt_pass ),
  15.     't|table=s' => \( my $opt_table ),
  16. ) or die USAGE;
  17.  
  18. #die USAGE
  19. #    if not( $opt_dbname and $opt_user and $opt_pass and $opt_table );
  20. $opt_table='test';
  21.  
  22. my $csv = Text::CSV_XS->new(); #auto_diag =>1
  23. my $firstline=<>;
  24. print "$firstline\n";
  25. $csv->parse( $firstline  ); # first line
  26. my @field = $csv->fields();
  27. print join(', ',@field),"\n";
  28.  
  29.     my $sql =
  30.         "INSERT INTO $opt_table ("
  31.         . join( ',', @field )
  32.         . ") VALUES ("
  33.         . join( ',', ( '?' ) x @field )
  34.         . ")";
  35.     print STDERR "Using query: $sql\n";
  36. exit;
  37.  
  38. my $dbh = DBI->connect(
  39.     "dbi:mysql:dbname=$opt_dbname;host=$opt_host;port=$opt_port",
  40.     $opt_user,
  41.     $opt_pass,
  42.     { RaiseError => 1, PrintError => 0 }
  43. ) or die "Connection error: $DBI::errstr";
  44.     my $sth =$dbh->prepare( $sql );
  45.  
  46. my ( $buff, $lnr );
  47.  
  48. while( <> ) {
  49.    $buff .= $_;
  50.    if( $csv->parse( $buff ) ) {
  51.        $sth->execute( $csv->fields );
  52.        $buff = '';
  53.        $lnr = $. + 1;
  54.    }
  55. }
  56.  
  57. perl a.pl <a.csv
  58. Print objekt,Press edition,Product typ,Issue reference,Line,Run ID,Title,Edition,Planned start,Plann
  59. ed end,Start time,End time,Gross utr,Stack utr,Wound utr,Overflow utr,Unwound hutr,MSD ep,Stack hutr
  60. ,Wound hutr,Overflow hutr,fat,skew
  61.  
  62. Print objekt, Press edition, Product typ, Issue reference, Line, Run ID, Title, Edition, Planned sta
  63. rt, Planned end, Start time, End time, Gross utr, Stack utr, Wound utr, Overflow utr, Unwound hutr,
  64. MSD ep, Stack hutr, Wound hutr, Overflow hutr, fat, skew
  65. Using query: INSERT INTO test (Print objekt,Press edition,Product typ,Issue reference,Line,Run ID,Ti
  66. tle,Edition,Planned start,Planned end,Start time,End time,Gross utr,Stack utr,Wound utr,Overflow utr
  67. ,Unwound hutr,MSD ep,Stack hutr,Wound hutr,Overflow hutr,fat,skew) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?
  68. ,?,?,?,?,?,?,?,?,?,?)
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top