Insert local .csv data in remote SQL Server via already working connection string

Question

I am using ODBC and FreeTDS to connect from a linux server to an MS SQL Server 2008 (connection string). I need to get the contents of a local .CSV file on the RHEL-server in a table on the SQL server. However my SQL is a little shabby to say the least. How should I proceed? I am a stranger to DBI as well.

#!/usr/bin/perl

# PERL MODULES WE WILL BE USING
use DBI;
use DBD::ODBC;

my $data_source = q/dbi:ODBC:MSSQLServer/; # DSN string from /etc/odbc.ini
my $user = q/Username/;
my $password = q/Password/;
my $dbh = DBI->connect($data_source, $user, $password, {RaiseError => 0, PrintError => 1}) or die "Can't connect to $data_source: $DBI::errstr";

The .csv file contains 2 values separated by commas on each line. Every line must be inserted in the table. The table on the database has 2 columns (attribute1 and attribute2).

.csv content example:

server1, id1

server2, id2

server3, id1

server4, id9

Primary key is number value already set so I don't think duplicate values are an issue.

So you want us to parse the CSV file for you, iterate over its contents, and insert each line as a new record in the database? — Matt Jacob, Dec 03 '15 at 16:35
Here's the thing---it doesn't look like you've made a decent effort to solve this problem yourself. You posted 10 lines of code, only 1 of which actually does something. I won't do your work for you, but I will, however, point you in the right direction. Read all of these completely: [DBI](https://metacpan.org/pod/DBI), [Text::CSV](https://metacpan.org/pod/Text::CSV)/[split()](http://perldoc.perl.org/functions/split.html), and [INSERT (Transact-SQL)](https://msdn.microsoft.com/en-us/library/ms174335(v=sql.100).aspx). Then come back and edit your question, and we'll see what we can do. — Matt Jacob, Dec 03 '15 at 17:03

score 0 · Answer 1 · edited May 23 '17 at 12:07

At this point in your code you have only connected to your database. Please look at an example here in stackoverflow that may help you to see what your next steps could be (it refers to MySQL but it is still instructive):

Perl inserting into MySQL DB

Also, I would recommend what user Matt Jacob mentions in his comments and also the book "Programming the Perl DBI: Database programming with Perl" by Tim Bunce and Alligator Descartes.

HTH

score 0 · Answer 2 · answered Dec 04 '15 at 12:52

Your test.csv contains:

server1, id1

server2, id2

server3, id1

server4, id9

==============================

Use Below code for Bulk insert.

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

open(my $file, '<' , "test.csv");
my @all_data;
while(my $data = <$file>){
    chomp($data);
    if($data){
        $data =~ s/^\s+//;
        $data =~ s/\s+$//;
        my $string = qq{($data)};
        push(@all_data,$string)
    }
}
close $file;
my $sql_string = join(',',@all_data);

my $dbname   = "DBName";
my $dsn      = "dbi:SQLite:dbname=$dbname";
my $username = "user";
my $password = "pass";
my $dbh = DBI->connect($dsn, $username , $password, {
   PrintError       => 0,
   RaiseError       => 1,
   AutoCommit       => 1,
   FetchHashKeyName => 'NAME_lc',
});
my $sql = qq{INSERT INTO ServerDetails (server, id) VALUES $sql_string};
$dbh->do($sql);
$dbh->disconnect;

Use Below code for Single insert:

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $dbname   = "DBName";
my $dsn      = "dbi:SQLite:dbname=$dbname";
my $username = "user";
my $password = "pass";
my $dbh = DBI->connect($dsn, $username, $password, {
   PrintError       => 0,
   RaiseError       => 1,
   AutoCommit       => 1,
   FetchHashKeyName => 'NAME_lc',
});
open(my $file, '<' , "test.csv");
while(my $data = <$file>){
    chomp($data);
    if($data){
        $data =~ s/^\s+//;
        $data =~ s/\s+$//;
        my $sql = qq{INSERT INTO ServerDetails (server, id) VALUES ($data)};
        $dbh->do($sql);
    }
}
close $file;
$dbh->disconnect;

score 0 · Accepted Answer · answered Dec 04 '15 at 13:26

Sorry I was not realising that I aksed a bit too much. I actually figured it out on my own. So i guess I leave the code here in case anyone needs it. Have a nice day!

#!/usr/bin/perl

# PERL MODULES
use strict;
use DBI;
use DBD::ODBC;
use Text::CSV;
use List::MoreUtils qw(each_array);

# SOME VARIABLES
my $file = '/tmp/Stevo/clientlist.csv';
my $csv = ();
my $fh = ();
my $data_source = q/dbi:ODBC:MSSQLServer/;
my $user = q/username/;
my $password = q/password/;

# WRITE HOSTNAMES FROM CSV TO ARRAY
my @hostnames;
open (my $csv, '<', $file) || die "cant open";
foreach (<$csv>) {
   chomp;
   my @fields = split(/\,/);
   push @hostnames, $fields[0];
}
# WRITE CLIENTVERSIONS FROM CVS ARRAY
my @clientversions;
open (my $csv, '<', $file) || die "cant open";
foreach (<$csv>) {
   chomp;
   my @fields = split(/\,/);
   push @clientversions, $fields[1];
}
# CONNECTION STRING
my $dbh = DBI->connect($data_source, $user, $password, {RaiseError => 0, PrintError => 1}) or die "Can't connect to $data_source: $DBI::errstr";

# INSERTION CODE
my $sth_insert = $dbh->prepare('INSERT INTO dbo.LegatoClients (ClientName, ClientVersion) VALUES (?, ?)')
  or die $dbh->errstr;
   # EVERYTIME $EA IS CALLED THE NEXT LINE OF THE ARRAYS WILL BE RETURNED 
my $ea = each_array(@hostnames, @clientversions);
   # SET VALUES USING THE EVERYTIME ARRAY
while ( my ($val_hostnames, $val_clientversions) = $ea->() ) {
  $sth_insert->execute($val_hostnames, $val_clientversions) or die $dbh->errstr;
}

Insert local .csv data in remote SQL Server via already working connection string

3 Answers3