justin423 sent me a private message. I think it has to do with this thread, so I will answer it here publicly.
justin423 says: Is there a module, or use REGEXP to use the Perl DBD to import CSV files into SQLite? see this one https://www.irs.gov/pub/irs-utl/FFIListExample.csv
We are talking past each other. Strictly speaking, NO. The Perl DBI is for Perl to talk to a SQLite DB. There is no command line I/F included with the standard Perl distribution. Almost certainly at the beginning of your Perl program, use DBI; is all you need to start writing Perl code to talk to an SQLite DB. That is because almost certainly the SQLite drivers were included with your Perl distribution. I have provided one example at: Example Perl import code. You did not specify the format of your file very well, but my example should get you started. To import a CSV file, I would recommend Text::CSV to parse the input file.
If you want a command utility to import a CSV file. That task has nothing whatsoever to do with Perl - a completely separate subject. Go to the SQLite FAQ Page. Go to the Download Link and download the appropriate tools for your platform. I assume Windows of some flavor. You want the command line program sqlite3. Read the online documentation to find out about the CSV import utility. I haven't used it myself, but there is such a thing. Again, this is independent of Perl and its DBI. However, Perl will be able to read the SQlite DB created with the command line tools.
Writing your own import program will give you more flexibility and is not hard to do. This is the way that I do it so that nothing besides Perl needs to be installed. I would encourage you to attempt this yourself based on my example code. If you have trouble, then start a new thread with your new question.
Update:
Ok, Oh well, I went ahead and wrote a simple importer for your IRS example file. It makes one table with as many columns as the CSV data has. Assumes all data is textual. In SQLite, it is not necessary to spec say: varchar(24), you can just say: TEXT as the data type. I think you are better off with a special-purpose importer that doesn't have to build confusing SQL command strings as I did. But feel free to use this if you want. Of course, there are issues if some of these values are INTEGER or other types. Note that this CSV file does contain embedded commas, so Text::CSV is mandatory.
Updated code with a different way of generating the SQL statements.
use strict;
use warnings;
use Text::CSV;
use DBI;
my $dbName = "YourDBname.sqlite";
my %attr = ( RaiseError => 1); # auto die with error printout
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbName","","",\%attr)
or die "Couldn't connect to database $dbName: " . DBI->errstr;
my $csv = Text::CSV->new ({ binary => 1 });
my $columnDefLine = <DATA>;
chomp $columnDefLine;
$dbh->do("DROP TABLE IF EXISTS Irs");
my $ColDataTypes = $columnDefLine;
$ColDataTypes =~ s/(\w+)/$1 TEXT/g; # all cols are TEXT
my $createStatement = "CREATE TABLE Irs ($ColDataTypes)";
print "Create SQL Stmt=>$createStatement\n";
$dbh->do("$createStatement");
my $qMarks = $columnDefLine; # placeholders for each column's data
$qMarks =~ s/(\w+)/?/g;
my $insertSQL = "INSERT INTO Irs ($columnDefLine) VALUES ($qMarks)";
print "insertSQL STMT=>$insertSQL\n";
my $insert_row = $dbh->prepare("$insertSQL");
$dbh->begin_work; # Single Transaction for all of the Table's data
# million rows is just fine
while (my $row_ref = $csv->getline (*DATA))
{
$insert_row->execute(@$row_ref);
}
$dbh->commit;
=Prints:
Shows the CREATE TABLE and INSERT SQL statements that were generated
Create SQL Stmt=>CREATE TABLE Irs (GIIN TEXT,FINm TEXT,CountryNm TEXT)
insertSQL STMT=>INSERT INTO Irs (GIIN,FINm,CountryNm) VALUES (?,?,?)
=cut
__DATA__
GIIN,FINm,CountryNm
"98Q96B.00000.LE.250","Test Bank One, LLP","France"
"98Q96B.00000.BR.826","Branch","United Kingdom"
"98Q96B.00000.BR.036","Branch","Australia"
"98Q96B.00000.BR.076","Branch","Brazil"
"98Q96B.00000.BR.818","Branch","Egypt"
"98Q96B.00001.ME.276","Test Bank Two","Germany"
"98Q96B.00001.BR.826","Branch","United Kingdom"
"8124H8.00000.SP.208","Test Bank Three","Denmark"
"C54S47.99999.SL.276","Test Bank Four","Germany"
"C54S47.99999.BR.208","Branch","Denmark"
"126BM7.00000.LE.826","Test Bank Five","United Kingdom"
"126BM7.00000.BR.250","Branch","France"
"126BM7.00001.ME.208","Test Bank Nine","Denmark"
"126BM7.00002.ME.276","Test Bank Ten, LLP","Germany"
"126BM7.00002.BR.344","Branch","Hong Kong"
"SE19K4.99999.SL.250","Test Bank Eleven","France"
"76GHU9.00000.BR.036","Branch","Australia"
"AAAAAA.00000.SP.001","Test Bank, 150-character name1 23456-40 123456-
+50x123456-60x123456-70x123456-80x123456-90x12345-100 12345-110 12345-
+120 12345-130 12345-140 123456-150","Afghanistan"
"AAAAAA.00000.SP.002","Test Bank, 150-character name2 (parenthesis) [s
+quare brackets] {curly brackets}-60x123456-70x123456-80x123456-90x123
+45-100 12-130 12345-140 123456-150","France"
"AAAAAA.00000.SP.003","Test Bank, 150-character name3 (3456-4) [23456-
+50x123456-60x123456-70x123456-80x123456-90x12345-10] {2345-11} 12345-
+120 12345-130 12345-140 123456-150","Egypt"
Old code...
use strict;
use warnings;
use Text::CSV;
use DBI;
my $dbName = "YourDBname.sqlite";
my %attr = ( RaiseError => 1); # auto die with error printout
my $dbh = DBI->connect("dbi:SQLite:dbname=$dbName","","",\%attr)
or die "Couldn't connect to database $dbName: " . DBI->errstr;
my $csv = Text::CSV->new ({ binary => 1 });
my $columnHeaders = <DATA>;
chomp $columnHeaders;
my @colNames = split ",",$columnHeaders;
$dbh->do("DROP TABLE IF EXISTS Irs");
my $createStatement = "CREATE TABLE Irs (".join (" TEXT,",@colNames)."
+ TEXT)";
print "Create SQL Stmt=>$createStatement\n";
$dbh->do("$createStatement");
my $qMarks = ("?," x @colNames); # placeholders for each column's dat
+a
$qMarks =~ s/,$//; # no comma after the last one
my $insertSQL = "INSERT INTO Irs (".join(",",@colNames).") VALUES ($qM
+arks)";
print "insertSQL STMT=>$insertSQL\n";
my $insert_row = $dbh->prepare("$insertSQL");
$dbh->begin_work; # Single Transaction for all of the Table's data
# million rows is just fine
while (my $row_ref = $csv->getline (*DATA))
{
$insert_row->execute(@$row_ref);
}
$dbh->commit;
=Prints:
Shows the CREATE TABLE and INSERT SQL statements that were generated
Create SQL Stmt=>CREATE TABLE Irs (GIIN TEXT,FINm TEXT,CountryNm TEXT)
insertSQL STMT=>INSERT INTO Irs (GIIN,FINm,CountryNm) VALUES (?,?,?)
=cut
__DATA__
GIIN,FINm,CountryNm
"98Q96B.00000.LE.250","Test Bank One, LLP","France"
"98Q96B.00000.BR.826","Branch","United Kingdom"
"98Q96B.00000.BR.036","Branch","Australia"
"98Q96B.00000.BR.076","Branch","Brazil"
"98Q96B.00000.BR.818","Branch","Egypt"
"98Q96B.00001.ME.276","Test Bank Two","Germany"
"98Q96B.00001.BR.826","Branch","United Kingdom"
"8124H8.00000.SP.208","Test Bank Three","Denmark"
"C54S47.99999.SL.276","Test Bank Four","Germany"
"C54S47.99999.BR.208","Branch","Denmark"
"126BM7.00000.LE.826","Test Bank Five","United Kingdom"
"126BM7.00000.BR.250","Branch","France"
"126BM7.00001.ME.208","Test Bank Nine","Denmark"
"126BM7.00002.ME.276","Test Bank Ten, LLP","Germany"
"126BM7.00002.BR.344","Branch","Hong Kong"
"SE19K4.99999.SL.250","Test Bank Eleven","France"
"76GHU9.00000.BR.036","Branch","Australia"
"AAAAAA.00000.SP.001","Test Bank, 150-character name1 23456-40 123456-
+50x123456-60x123456-70x123456-80x123456-90x12345-100 12345-110 12345-
+120 12345-130 12345-140 123456-150","Afghanistan"
"AAAAAA.00000.SP.002","Test Bank, 150-character name2 (parenthesis) [s
+quare brackets] {curly brackets}-60x123456-70x123456-80x123456-90x123
+45-100 12-130 12345-140 123456-150","France"
"AAAAAA.00000.SP.003","Test Bank, 150-character name3 (3456-4) [23456-
+50x123456-60x123456-70x123456-80x123456-90x12345-10] {2345-11} 12345-
+120 12345-130 12345-140 123456-150","Egypt"
