RE: Parsing CSV Data |
||
  Lambda ViceCinC Enstyne Hacked together a quick script. first off, name your CSV file input.csv make convert.pl with the following perl code: #!/usr/bin/perl
$tbl = "goat";
open(IN, "input.csv") || die("Can't open input.csv");
chomp($names = <IN>);
$sqlsetup = join(" VARCHAR(255),", split(',', $names)) . " VARCHAR(255)";
$outputschema = "CREATE TABLE $tbl ($sqlsetup);\n\n";
while (<IN>) { chomp; $outputschema .= "INSERT INTO $tbl VALUES ('" . (join("', '", split(',', $_))) . "');\n" unless !$_ }
print $outputschema . "\n\nCREATE VIEW UsersPerServer as (SELECT mailserver,count(*) FROM $tbl GROUP BY mailserver);\n\n";
Next you need to supply a special first line in the CSV file you are running it on. The first line is CSV formatted names of the columns you want to call them in the database. I assumed that mailserver is going to be called 'mailserver' for the VIEW at the end, but you can rename it to whatever you like. So input.csv should look something like this.
username,email,idnum,mailserver john doe,john@perlmonks.org,1337,mail.perlmonks.org larry wall,larry@perl.org,31337,mail.perl.org goat rider,goat@rider.org,42,mail.cyberarmy.net ens tyne,perl@rules.org,1,mail.cyberarmy.net int 16h,justifiably@anti-php.perl.org,123456,mail.perl.orgso the first line will just represent the column names. Each column is assumed to be VARCHAR(255) in SQL. Hack it up if you need that to be different. The amount of columns you can have is limited by how many MySQL will allow, but make sure to remember to put a column name for each one. Then run the script. The output should be like: CREATE TABLE goat (username VARCHAR(255),email VARCHAR(255),idnum VARCHAR(255),mailserver VARCHAR(255));
INSERT INTO goat VALUES ('john doe', 'john@perlmonks.org', '1337', 'mail.perlmonks.org');
INSERT INTO goat VALUES ('larry wall', 'larry@perl.org', '31337', 'mail.perl.org');
INSERT INTO goat VALUES ('goat rider', 'goat@rider.org', '42', 'mail.cyberarmy.net');
INSERT INTO goat VALUES ('ens tyne', 'perl@rules.org', '1', 'mail.cyberarmy.net');
INSERT INTO goat VALUES ('int 16h', 'justifiably@anti-php.perl.org', '123456', 'mail.perl.org');
CREATE VIEW UsersPerServer as (SELECT mailserver,count(*) FROM goat GROUP BY mailserver);
Then all you need to do is log into your mysql database, connect to some database and paste those lines. From your PHP script all you need to do is: SELECT (mailserver, num) FROM UsersPerServer;I left the source on volund in /home/enstyne/goatrider if you want a pre-setup copy. On 2008-08-04 19:57:43, Goatrider wrote >Hey all, > >Here's a good one for you... > >Objective: >Ease the process of auditing the number of email addresses per server, across multiple servers. > >Data Access: >Data is stored in a single, centralized CSV file; containing, amongst other things: the email address, organization ID number, and mail server name. > >Real-Life Application: >A proprietary spam filter interface allows customers to add users to the spam filter proxy server for their mail server. There is no restriction on how many users the customer can set up, but they have all signed contracts stating they are only allowed to have a certain number of users, and overage charges will apply if they go over their quota. Keeping that in mind, a sales person wants to check how many users each customer has set up for spam filtering, to ensure they're not over their contracted amount, but to do so, they will need to go through each individual customer's page and write it all down. > >Luckily, the admin console allows for a system-wide dump of user information, including (amongst other things) the email address, organization ID number, and mail server name. This data dump comes in the form of a CSV file, with each row representing a single user. > >The Question: >How does one allow the salesperson, with no technical knowledge, to take that CSV file, upload it to a script, and have that script parse the file for the number of users per server? This should be done using PHP, and if would be GREAT if it could be done using SQL statements to look through the CSV file, though I'm not sure any such mechanism exists. > >More info can be provided, just let me know what you want. > >--goat > VCinC. Enstyne - /sered Challenge Coder Replies:
|
||
| CyberArmy::Forum v0.6 Generated In 0.00746 seconds |
2008-08-04 19:57:43
2008-08-04 20:04:55
2008-08-05 13:36:37