in reply to Process and combine two CSV files into one

I've pointed you to DBD::CSV's "CREATE TABLE AS SELECT" syntax in several other threads but you don't seem to have seen it, so here goes again: You can create a new CSV file from one or more old CSV files in a single step with this kind of syntax: 
$dbh->do("
   CREATE TABLE new_csv_file
      AS SELECT (columns)
           FROM old_csv_file1 
           JOIN old_csv_file2
");

[download]
The JOIN may be natural, inner, outer, left, right, or full. You can use table and column aliases, supply a WHERE clause, etc. This creates the new CSV file from the data result set you specify in the SELECT clause. You don't need to put the results into an array or hash or any other structure, just go from CSV file to CSV file using SQL.

update : Make sure you have the latest version of SQL::Statement, DBD::CSV's SQL engine, the syntax is supported in SQL::Statement version 1.14 and later.

Replies are listed 'Best First'.
Re^2: Process and combine two CSV files into one
by DrAxeman (Scribe) on Aug 10, 2005 at 00:17 UTC
    You're right. I was just kinda skipping over this because it looked like more work. I'll just go and whimper in the corner.
[reply]
Re^2: Process and combine two CSV files into one
by DrAxeman (Scribe) on Aug 10, 2005 at 00:38 UTC
    I tried it and got a error:
    SQL ERROR: Can't find column definitions!

    [download]

    So I tried adding column headings as shown in the DBD:CSV page. Same error. Google doesn't produce anything for that error either.
    # Associate our csv disk file with the table name 'results'
$dbh->{'csv_tables'}->{'info'} = { 'file' => "psinfooutputfile.csv"};
$dbh->{'csv_tables'}->{'disks'} = { 'file' => "disk_results.csv"};
$dbh->{'csv_tables'}->{'hosts'} = { 'file' => "netbiosoutputfile.csv"}
+;
$dbh->{'csv_tables'}->{'hosts'} = { 'file' => "template.csv"};

######
$dbh->do("DROP TABLE IF EXISTS template");

$dbh->do("CREATE TABLE template (IP CHAR(64), ServerName CHAR(64), Dom
+ain CHAR(64), DaysUptime CHAR(64), OS CHAR(64), RAM CHAR(64), OSSP CH
+AR(64), InstallDate CHAR(64), CPUSpeed CHAR(64), CPUCount CHAR(64), C
+PUType CHAR(64))
        AS SELECT IP, ServerName, Domain, DaysUptime, OS, RAM, OSSP, I
+nstallDate
, CPUSpeed, CPUCount, CPUType
        FROM hosts
        LEFT JOIN info ON hosts.IP = info.IP ");

    [download]

    Please note, the CHAR (64) was just a dummy value used for testing ONLY.
    Do I have a syntax error?
[reply]
[d/l]
[select]
      That's not the syntax supported by SQL::Statement (see SQL::Statement::Syntax). The columns for creating the table are specified in the SELECT part and created in the new table automatically. So change the create statement to: 
      $dbh->do("
    CREATE TABLE template
       AS SELECT IP, ServerName, Domain, DaysUptime, OS,
                 RAM, OSSP, InstallDate, CPUSpeed, CPUCount,
                 CPUType
            FROM hosts
       LEFT JOIN info
              ON hosts.IP = info.IP
");

      [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom