Perl Script to Create MYSQL INSERT Statements

At my job I was required to import 500+ user records into a MYSQL database. I was given a CSV file that included user information such as last name, first name, password, etc. My access to the database is limited to the MYSQL Query application that allows me to run queries on tables and make modifications through SQL statements. So, I essentially needed to write out 500+ INSERT statements to add the users to the database. Me being the lazy efficient guy I am, decided that I should probably automate the process by creating a script to take in that CSV file with the user information and create a text file with all 500+ INSERT statements. I could then copy/paste those statement into MYSQL Query and win at IT for the day. I’ve never used Perl before, so I thought this might be a good opportunity to try it out.

I’ve modified the code to present the script as an example. The one I have used in production includes other fields that are specific to the application it was intended for. The example script for blogging purposes takes in an insert.csv file with ID, Lastname, Firstname, and password fields and creates a insert.txt file with individual INSERT statements for each record in the CSV file.

Example insert.csv:

1,Last,First,Password
2,Smith,John,mypass
3,Doe,Jane,testpass

Example insert.pl:

#!/usr/bin/perl

# This script takes in a given .csv file $file with the format:
# LastName,FirstName,Password

# The script then parses the .csv file and creates a .txt file with individual INSERT statements given the parameters listed below.
# Replace $file and $out with appropriate filenames

use File::Slurp; #JL command "cpan File::Slurp" to install module if not present

my $file = "insert.csv"; #JL input csv file
open(my $fh, "<", "$file") or die "cannot open < $file: $!"; 

my $out = "insert.txt"; #JL output file for INSERT statements
open(my $f, ">", "$out") or die "cannot open < $out: $!";

my $data = read_file($fh); #JL entire file into single string
my @records = split /\n/, $data; #JL array values separated by \n in string $data
my $arrysize = @records; #JL total number of individual records

####print $f "DELETE FROM test;\n";####JL UNCOMMENT TO CLEAR USERS TABLE

for(my $i=0;$i<$arrysize;$i++){ #JL loop through each record
    my $data = @records[$i]; #JL store record as string
	my @words = split /,/, $data; #JL array values separated by commas in string $data
	print $f "INSERT INTO test (ID, LastName, FirstName, Password)
	VALUES('@words[0]','@words[1]','@words[2]','@words[3]');\n";
}

Example insert.txt:

INSERT INTO test (ID, LastName, FirstName, Password)
    VALUES('1','Last','First','Password');
INSERT INTO test (ID, LastName, FirstName, Password)
	VALUES('2','Smith','John','mypass');
INSERT INTO test (ID, LastName, FirstName, Password)
	VALUES('3','Doe','Jane','testpass');