Re^5: Splitting the records into multiple worksheets

Do you have any specific example of pulling the raw data into DB?

I've not had cause to use Sqlite as I usually have another DB available. But this is the sort of thing you need. Untested and without checking for errors creating connections and opening files etc. Hopefully it will give you a start.

use DBI;
use DBD::Sqlite;

# Connect to your database
my $dbh = DBI->connect("dbi:sqlite:$db_name:localhost:$port", $db_user
+, $db_password);

# Create a temporary table
$dbh->do("CREATE TEMPORARY TABLE IF NOT EXISTS ExcelData (name VARCHAR
+(80), data INT)");

# Populate temporary table from your datasource
foreach my $row(@responsetext) {
    my ($name, $data) = split / +/, $row
    $dbh->do("INSERT INTO ExcelData SET name = '$name', data = $data")
+;
}

# Create a query to sort the data how you want it and output to CSV fi
+le
open $fh, '>', 'myfile.csv';
my $query = $dbh->prepare("SELECT name, data FROM ExcelData WHERE data
+ > 20 ORDER BY data");
$query->exceute;
my ($n, $d);
while (($n, $d) = $query->fetchrow_array) {
    print $fh qq["$n",$d\n];
}
close $fh;
[download]

Comment on Re^5: Splitting the records into multiple worksheets Download Code

Replies are listed 'Best First'.
Re^6: Splitting the records into multiple worksheets by hippo (Archbishop) on Feb 20, 2021 at 23:38 UTC
`$dbh->do("INSERT INTO ExcelData SET name = '$name', data = $data");` I would not advocate that approach. Far safer to use placeholders. 🦛	[reply] [d/l]
Re^7: Splitting the records into multiple worksheets by Bod (Parson) on Feb 21, 2021 at 19:33 UTC
I do limit the privileges of the script's DB account to SELECT, INSERT, UPDATE, DELETE and CREATE TEMPORARY TABLE	[reply]
Re^8: Splitting the records into multiple worksheets by marto (Cardinal) on Feb 21, 2021 at 21:22 UTC
Which is enough to cause serious problems.... hence the tried and tested safe methods of working.	[reply]
Re^9: Splitting the records into multiple worksheets by Bod (Parson) on Feb 21, 2021 at 23:54 UTC
Re^10: Splitting the records into multiple worksheets by marto (Cardinal) on Feb 22, 2021 at 07:41 UTC
Re^6: Splitting the records into multiple worksheets by chandantul (Scribe) on Feb 21, 2021 at 12:49 UTC
That's very good suggestions but i will have to calculate the .csv file with around 2000000 rows of data in order to compare against another excel . I will not have any server to install database. Can i create a temporary .db file in my system and save the data and then fetched the data as .csv. How i can handle the .xlsx file's row limitations in case i will have 2000000 rows of records to handle? Can i create dynamic worksheets by checking the records in array?	[reply]
Re^7: Splitting the records into multiple worksheets by marto (Cardinal) on Feb 21, 2021 at 13:07 UTC
"I will not have any server to install database." Several people have suggested you use DBD::SQLite, you could have at least looked at the one line description: "DBD::SQLite is a Perl DBI driver for SQLite, that includes the entire thing in the distribution. So in order to get a fast transaction capable RDBMS working for your perl project you simply have to install this module, and nothing else." "How i can handle the .xlsx file's row limitations in case i will have 2000000 rows of records to handle? Can i create dynamic worksheets by checking the records in array?" What do you expect XLSX to do if it has more than the max rows per worksheet? Given the limits are well documented, find a way to work around them or use a tool/format that better suits your source data.	[reply]
Re^8: Splitting the records into multiple worksheets by chandantul (Scribe) on Feb 25, 2021 at 04:57 UTC
I have used an alternate solution for this and i splitted the 1 @array into mutpltiple @array. I have used below modules for splittings the arrays and that improves the script performances. Thanks for all the suggestions. `use List::MoreUtils qw( part ); use List::AssignRef;` [download]	[reply] [d/l]