<?php
/* Usage:
/*
/* $report = new Reports_Core; (In Kohana just new Reports;)
/* print $report->csv($result_set_or_array, 'a title', ';');
/*
/*--------------------------------------------------------------*/
class Reports_Core
{
function csv($lines = FALSE, $title = '', $delimiter = ';')
{
$output = '';
if($title) $output .= $title."
";
if($lines)
{
$report_line = array();
foreach($lines as $line)
{
$line = implode($delimiter, (array) $line);
$report_line[] = $line."
";
}
$output .= implode('', $report_line);
return $output;
}
else
{
trigger_error('There is no data to generate a CSV report.', E_USER_ERROR);
}
}
}
?>
Tag Archive for csv
CSV Reports
Importing data from (Excel) CSV into MySQL
LOAD DATA INFILE 'datafile.csv' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '' LINES TERMINATED BY ' ' IGNORE 1 LINES;
Rails CSV Export
# require 'rubygems' if using this outside of Rails
require 'fastercsv'
def dump_csv
@users = User.find(:all, <img src='http://www.snippetsmania.com/wp-includes/images/smilies/icon_surprised.gif' alt=':o' class='wp-smiley' /> rder => "lastname ASC")
@outfile = "members_" + Time.now.strftime("%m-%d-%Y") + ".csv"
csv_data = FasterCSV.generate do |csv|
csv << [
"Last Name",
"First Name",
"Username",
"Email",
"Company",
"Phone",
"Fax",
"Address",
"City",
"State",
"Zip Code"
]
@users.each do |user|
csv << [
user.lastname,
user.firstname,
user.username,
user.email,
user.company,
user.phone,
user.fax,
user.address + " " + user.cb_addresstwo,
user.city,
user.state,
user.zip
]
end
end
send_data csv_data,
:type => 'text/csv; charset=iso-8859-1; header=present',
:disposition => "attachment; filename=#{@outfile}"
flash[:notice] = "Export complete!"
end
Postgres output CSV
f ',' a o /tmp/moocow.csv SELECT foo,bar FROM whatever; o q If a field has newlines, this will break. You can do something like this instead..... SELECT foo, bar, '"' || REPLACE(REPLACE(field_with_newilne, ' ', 'n'), '"', '""') || '"' FROM whatever;
file spaces to commas
<?php
$handle = @fopen("data.txt", "r");
if ($handle)
{
while (!feof($handle))
{
$buffer = fgets($handle, 4096);
// this will search for 5 spaces and replace with 1, then 4, then 3, then 2
// then only one will be left. Replace that one space with a comma
// then output with nl2br so you can see the line breaks
print nl2br(str_replace(" ", ",",ereg_replace( ' ', ' ',ereg_replace( ' ', ' ', ereg_replace( ' ', ' ', ereg_replace( ' ',' ',$buffer ))))));
}
}
fclose($handle);
?>
SQL MySQL CSVã§å‡ºåŠ›
#MySQL内ã‹ã‚‰CSVã§å‡ºåŠ› SELECT * FROM phone INTO OUTFILE '/tmp/hoge.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
SQL CSV ã§MySQLã«ãƒ‡ãƒ¼ã‚¿å…¥åŠ›
#CSVã§å…¥åŠ› LOAD DATA INFILE "~/Desktop/keywords.csv" INTO TABLE keywords FIELDS TERMINATED BY ',' ENCLOSED BY '"';
Create a CSV File From Query Results
SELECT * FROM users INTO OUTFILE '/home/user/users.csv' FIELDS TERMINATED BY ' ->LINES TERMINATED BY ' ';
Backup all your tables from ZOHO CRM in CSV format
#!/usr/bin/perl
use strict;
use LWP::UserAgent;
my $silent = 0; # set to 1 for no output on stdout
my $logindata = {
'j_username' => 'fill in your username (email)',
'j_password' => 'fill in your password',
'j_remember' => 'on',
};
my $loginurl = 'http://crm.zoho.com/crm/zohologin';
my $backupurl = 'http://crm.zoho.com/crm/ExportEntity.do';
my @backuptables = qw{Users Leads Accounts Contacts Potentials Campaigns Tasks Events Notes Competitors Products PriceBooks Cases Solutions Forecasts Vendors Quotes SalesOrders PurchaseOrders Invoices};
my $ua = LWP::UserAgent->new;
$ua->cookie_jar({ file => "cookies.txt" });
print qq{get cookie from frontpage...} unless $silent;
my $devnull = $ua->get('http://crm.zoho.com/crm/login.sas'); #get cookie
print qq{done.
} unless $silent;
print qq{login as $$logindata{'j_username'}...} unless $silent;
my $loginresponse = $ua->post($loginurl, $logindata);
print qq{done.
} unless $silent;
foreach my $whichtable (@backuptables) {
print qq{exporting $whichtable.csv...} unless $silent;
my $backupdata = {
'module' => $whichtable,
};
my $backuptable = $ua->post($backupurl, $backupdata);
open (KI, ">$whichtable.csv");
print KI $backuptable->content;
close (KI);
print qq{done.
} unless $silent;
}