LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE `locations` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '' LINES TERMINATED BY ' r n'
Tag Archive for csv
Load CSV file to SQL DB
Array#to_csv
require 'CSV' class Array def to_csv str='' CSV::Writer.generate(str) do |csv| self.each do |r| csv << r end end str end end
Export MySQL query results to CSV
// Export to CSV
if($_GET['action'] == 'export') {
$rsSearchResults = mysql_query($sql, $db) or die(mysql_error());
$out = '';
$fields = mysql_list_fields('database','table',$db);
$columns = mysql_num_fields($fields);
// Put the name of all fields
for ($i = 0; $i < $columns; $i++) {
$l=mysql_field_name($fields, $i);
$out .= '"'.$l.'",';
}
$out .="
";
// Add all values in the table
while ($l = mysql_fetch_array($rsSearchResults)) {
for ($i = 0; $i < $columns; $i++) {
$out .='"'.$l["$i"].'",';
}
$out .="
";
}
// Output to browser with appropriate mime type, you choose <img src='http://www.snippetsmania.com/wp-includes/images/smilies/icon_wink.gif' alt=';)' class='wp-smiley' />
header("Content-type: text/x-csv");
//header("Content-type: text/csv");
//header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=search_results.csv");
echo $out;
exit;
}
Load CSV data into a database
<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd"> <etl> <connection id="in" driver="csv" url="data.csv" classpath="opencsv.jar"/> <connection id="out" driver="oracle" url="jdbc:oracle:thin:@localhost:1521:ORCL" classpath="ojdbc14.jar" user="scott" password="tiger"/> <!-- Copy all CSV rows to a database table --> <query connection-id="in"> <!-- Empty query means select all columns --> <script connection-id="out"> INSERT INTO Table_Name VALUES (?id,?priority, ?summary, ?status) </script> </query> </etl>
PHP5 CSV Iterator
<?php
class CSVIterator implements Iterator
{
const ROW_SIZE = 4096;
private $filePointer;
private $currentElement;
private $rowCounter;
private $delimiter;
public function __construct( $file, $delimiter = ',' )
{
$this->filePointer = fopen( $file, 'r' );
$this->delimiter = $delimiter;
}
public function rewind()
{
$this->rowCounter = 0;
rewind( $this->filePointer );
}
public function current()
{
$this->currentElement = fgetcsv( $this->filePointer, self::ROW_SIZE, $this->delimiter );
$this->rowCounter++;
return $this->currentElement;
}
public function key()
{
return $this->rowCounter;
}
public function next()
{
return !feof( $this->filePointer );
}
public function valid()
{
if( !$this->next() )
{
fclose( $this->filePointer );
return FALSE;
}
return TRUE;
}
} // end class
?>
ArticleHandler.class usage example
$currentCat = $_REQUEST['cat'];
$categories = new articleHandler($CATEGORIES_PATH) ;
...
// getting the breadcrumds
if(is_string($currentCat)){
$bread = $categories->get_breadCrumbs($currentCat);
for($i = count($bread)-1; $i >= 0; $i--){
if($i==O){
//this is the location the user is @, so no link has to be set
$t->set_var("currentone" , $bread[$i][0]);
}else{
//parse the breadcrumbs
$t->set_var("loc", $bread[$i][1]);
$t->set_var("tekst", $bread[$i][0]);
$t->parse("crumbs" , "bread", true);
}
}
}
//get items in a categorie
//1 stands for the field number being used
$itemsInCat = $items->searchFor($currentCat, 1);
//get subcategories of an categorie
//5 stands for the field number being used
$subCats = $categories->searchFor($currentCat, 5);
//adding an article without a photo
$items->add_art($cat, $csv);
//adding a product with a photo
$items->add_art($cat, $csv,$_FILES["_pic"]["tmp_name"]);
//deleting an article
$items->del_art(...);
...
//The same goes with categories
Provincias españolas (CSV)
15;A CORUÑA/LA CORUÑA 01;ALAVA/ARABA 02;ALBACETE 03;ALICANTE/ALACANT 04;ALMERÃA 33;ASTURIAS 05;AVILA 06;BADAJOZ 08;BARCELONA 09;BURGOS 10;CÃCERES 11;CÃDIZ 39;CANTABRIA 12;CASTELLÓN/CASTELLÓ 51;CEUTA 13;CIUDAD REAL 16;CUENCA 14;CÓRDOBA 17;GIRONA/GERONA 18;GRANADA 19;GUADALAJARA 20;GUIPUZKOA/GUIPÚZCOA 21;HUELVA 22;HUESCA 07;ILLES BALEARS/I. BALEARES 23;JAÉN 26;LA RIOJA 35;LAS PALMAS 24;LEÓN 25;LLEIDA/LÉRIDA 27;LUGO 29;MÃLAGA 28;MADRID 52;MELILLA 30;MURCIA 31;NAVARRA 32;OURENSE/ORENSE 34;PALENCIA 36;PONTEVEDRA 37;SALAMANCA 40;SEGOVIA 41;SEVILLA 42;SORIA 38;STA CRUZ DE TENERIFE 43;TARRAGONA 44;TERUEL 45;TOLEDO 46;VALENCIA 47;VALLADOLID 48;VIZCAYA/BIZKAIA 49;ZAMORA 50;ZARAGOZA
PaÃses en español (CSV)
151;Afganistán 2;Albania 17;Alemania 3;Andorra 98;Angola 53;Anguilla 54;Antigua y Barbuda 73;Antillas Holandesas 194;Arabia Saudà 97;Argelia 81;Argentina 182;Armenia 55;Aruba 197;Australia 4;Austria 183;Azerbayan 56;Bahamas 184;Bahrain 152;Bangladesh 57;Barbados 6;Bélgica 58;Belice 99;Benin 48;Bermudas 5;Bielorrusia 82;Bolivia 7;Bosnia - Herzegovina 100;Botswana 83;Brasil 154;Brunei 8;Bulgaria 101;Burkina Faso 155;Burma (Myanmar) 102;Burundi 153;Bután 104;Cabo Verde 156;Camboya 103;Camerún 49;Canada 106;Chad 84;Chile 157;China 10;Chipre 85;Colombia 107;Comoros 108;Congo 170;Corea del Norte 174;Corea del Sur 217;Costa de Marfil 60;Costa Rica 9;Croacia 61;Cuba 12;Dinamarca 62;Dominica 86;Ecuador 110;Egipto 64;El Salvador 195;Emiratos Arabes Unidos 112;Eritrea 39;Eslovaquia 40;Eslovenia 1;España 52;Estados Unidos 13;Estonia 113;Etiopia 199;Fiji 172;Filipinas 15;Finlandia 16;Francia 114;Gabón 115;Gambia 185;Georgia 116;Ghana 18;Gibraltar 65;Granada 19;Grecia 50;Groenlandia 66;Guadalupe 201;Guam 67;Guatemala 90;Guayana 88;Guayana Francesa 117;Guinea 111;Guinea Ecuatorial 118;Guinea-Bissau 68;Haità 32;Holanda 69;Honduras 158;Hong Kong 20;HungrÃa 59;I. Caimán 219;I. Cocos (Keeling) 198;I. Cook 14;I. Feroe 89;I. Galápagos 87;I. Malvinas 203;I. Marianas del Norte 204;I. Marshall 133;I. Reunión 212;I. Salomón 218;I. VÃrgenes Británicas 80;I. VÃrgenes EEUU 216;I. Wallis y Futuna 159;India 160;Indonesia 186;Irán 187;Iraq 22;Irlanda 21;Islandia 188;Israel 23;Italia 70;Jamaica 161;Japón 189;Jordania 162;Kazajistán 119;Kenia 163;Kirguizistán 202;Kiribati 190;Kuwait 164;Laos 120;Lesotho 24;Letonia 191;LÃbano 121;Liberia 122;LÃbia 25;Liechtenstein 26;Lituania 27;Luxemburgo 165;Macao 28;Macedonia 123;Madagascar 166;Malasia 124;Malawi 167;Maldivas 125;Mali 29;Malta 128;Marruecos 71;Martinica 127;Mauricio 126;Mauritania 51;México 205;Micronesia 30;Moldavia 31;Mónaco 168;Mongolia 72;Montserrat 129;Mozambique 130;Namibia 206;Nauru 169;Nepal 74;Nicaragua 131;NÃger 132;Nigeria 33;Noruega 207;Nueva Caledonia 208;Nueva Zelanda 192;Omán 171;Pakistán 209;Palau 75;Panamá 210;Papua Nueva Guinea 91;Paraguay 92;Peru 200;Polinesia Francesa 34;Polonia 35;Portugal 76;Puerto Rico 193;Qatar 45;Reino Unido 105;Rep. Centroafricana 11;Rep. Checa 63;Rep. Dominicana 134;Ruanda 36;Rumania 37;Rusia 147;Sáhara Occidental 211;Samoa 38;San Marino 135;Sao Tomé y PrÃncipe 136;Senegal 137;Seychelles 138;Sierra Leona 173;Singapur 220;Siria 139;Somalia 175;Sri Lanka 78;St. Vicent y Grenadines 77;St.Kitts & Nevis 141;Sudán 41;Suecia 42;Suiza 93;Sur Georgia e I. Sandwich 140;Suráfrica 94;Surinam 142;Swazilandia 178;Tailandia 176;Taiwán 177;Tajikistán 143;Tanzania 144;Togo 213;Tonga 79;Trinidad y Tobago 145;Túnez 179;Turkmenistán 43;Turquia 214;Tuvalu 44;Ucrania 146;Uganda 95;Uruguay 180;Uzbekistán 215;Vanuatu 46;Vaticano 96;Venezuela 181;Vietnam 196;Yemen 109;Yibuti 47;Yugoslavia 148;Zaire 149;Zambia 150;Zimbabwe
Load CSV into MySQL
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ';