CSV imortēšana MySQL datubāzēs

Cibiņš · August 21, 2010

Sveiki, nu tā es atkal te ar savām problēmām :D

Nevaru saporast kas pie velna ellē ratā neiet ka neimportē csv failā esošos datus datubāzēs attiecīgajās ailēs?? :@:@:@ 2 dienas jau nop*isois un nekas tā arī jēdzīgs nau sanācis :@ lasīt lasa bet datu imports nesnāk, kāds varētu lūdzu paskaidrot un apgaismot ko es esmu neievērojis??

excel_imporc.zip

briedis · August 21, 2010

Kādas kļūdas?

Kāds kods?

Cibiņš · August 21, 2010

Kādas kļūdas?

Kāds kods?

Reku ZIP`s tak klaat ar visaam panckaam pie mana iepriekšējā posta..

Edited August 21, 2010 by Cibiņš

spainis · August 21, 2010

kādas kļūdas, ja uz mysql_query ir uzlikts error suppress :)

Cibiņš · August 21, 2010

kādas kļūdas, ja uz mysql_query ir uzlikts error suppress :)

tur taa lieta ka vinjam pat uz mysql erroriem pofigs..pat to neizvada..

zintis8789 · August 21, 2010

php

<?
include "config.php";
if ((isset($_POST["submitted_form"])) && ($_POST["submitted_form"] == "image_upload_form")) {
$tips2 = $_FILES["csv3"]["type"];
if($tips2 !='application/vnd.ms-excel'){
Die ("Tev ir jaliek .csv fails!");
}
$idir="f/";
$copy2 = copy($_FILES['csv3']['tmp_name'], "$idir" . $_FILES['csv3']['name']); 
$filename = $_FILES["csv3"]["name"];
$handle = fopen("f/$filename", "r");
while (($data = fgetcsv($handle, 1000, ",")) != FALSE)
{
$import="INSERT into rangs (`v`, `niks`, `TP`, `AV`, `PIV`, `DSK`, `IST`, `RP`) values('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]')";
mysql_query($import) or die(mysql_error());
}
fclose($handle);
print "pievienots!";
}
else
{
print "<form action='' method='post' enctype=multipart/form-data name=image_upload_form id=image_upload_form style=margin-bottom:0px;>";
print "Fails (.csv)<br>";
print "<input type='file'  name=csv3 id=csv3 size='20'><br>";
print "<input type='submit' name='submit' value='Pievienot'>";
print "<input name=submitted_form type=hidden id=submitted_form value=image_upload_form /></form>";
}
?>

Mysql

CREATE TABLE IF NOT EXISTS `rangs` (
 `v` varchar(99) NOT NULL,
 `niks` varchar(99) NOT NULL,
 `TP` varchar(99) NOT NULL,
 `AV` varchar(99) NOT NULL,
 `PIV` varchar(99) NOT NULL,
 `DSK` varchar(99) NOT NULL,
 `IST` varchar(99) NOT NULL,
 `RP` varchar(99) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CSV

V;Niks;TP;AV;PIV;DSK;IST;RP
1;Lords;12;1;2;0;0;98
2;Johns;9;1;5;0;0;95
3;nezinislv;5;1;1;0;0;76
4;ROBERTS;11;1;n;1;0;56
5;Mareks;7;3;6;0;0;52
6;kelso;7;1;8;0;0;36
7;Arturs;10;1;7;2;0;35
8;Richijs42;12;2;14;1;0;35
9;jokers00;7;1;2;1;0;32
10;ArtikX;8;4;4;0;0;31
11;Hokejfans;2;3;3;0;0;29
12;Latvian;2;3;3;0;1;29
13;marissis;6;9;12;0;0;27
14;BOSIKSES;8;3;17;0;0;23
15;kazha20;9;4;13;0;0;22
16;zaaggiz2;4;11;11;0;0;20
17;Edgars;6;8;8;2;0;20
18;janisbo22;2;7;n;0;1;18
19;AKs;9;2;n;1;0;15
20;LitteL;4;2;n;0;1;14
21;BooMb;2;9;9;0;0;13
22;ANZman;4;4;n;0;1;13
23;edziic;12;1/4 f.;n;0;2;13
24;Ride;1;10;10;0;0;12
25;Mairons;8;3;n;3;1;10
26;mozeiks;8;2;n;1;0;9
27;mopsis;2;12;n;0;1;8
28;dupaars;2;13;n;1;0;8
29;VlaD;1;14;14;0;0;8
30;pleesona;1;15;15;0;0;5
31;vigners66;7;1/4 f.;n;1;1;5
32;Thrusty;5;4;n;2;0;4
33;Scettro;1;12;12;0;0;3
34;valcha15;1;16;16;0;0;3
35;gapp;1;18;18;0;0;3
36;Naureens;2;19;19;1;0;3
37;matiss241;1;27;27;0;0;3
38;kasy83;2;10;n;0;1;3
39;SanChesss;7;11;n;1;1;3
40;sangroks;4;4;4;0;0;2
41;Alexi-55;1;13;13;0;0;2
42;skala;1;1/8 f.;1/8 f.;0;0;2
43;Pucis;7;11;n;0;1;2
44;garenka;1;1/4 f.;1/4 f.;0;0;1
45;Vinchester;1;1/4 f.;1/4 f.;0;0;1
46;Agross;1;1/8 f.;1/8 f.;0;0;1
47;JohnnyAhome;3;13;16;0;0;1
48;4aliic;1;17;17;0;0;1
49;Pure;3;1/4 f.;20;0;0;1
50;hanters;1;23;23;0;0;1
51;LVArturs;1;24;24;0;0;1
52;nilino;3;20;n;0;1;1
53;Ginca;1;n;n;0;1;1

config

<?php

//PieslÄ“gÅ¡anÄs datubÄzei

$db_host = '';
$db_user = '';
$db_password = '';
$db_name = '';

$connection = @mysql_connect($db_host, $db_user, $db_password) or die(mysql_error());
mysql_select_db($db_name, $connection);

mysql_set_charset('utf8',$connection); 
mysql_query("SET NAMES UTF8 COLLATE utf8_general_ci"); 
?>

VAJADZETU stradat

Edited August 21, 2010 by zintis8789

Cibiņš · August 23, 2010

Paldies bet INSERT kveerijs tomeer neizpildaas arii mysql errorus neizvada.. :(

Maris-S · August 23, 2010

Ieliec kodam sākumā:

error_reporting(E_ALL);
ini_set('display_errors', true);

Iespējams līdz vaicājumam nemaz nenonāk.

Cibiņš · August 23, 2010

Warning: copy(f/kaka.csv) [function.copy]: failed to open stream: No such file or directory in D:\Web\sites\excel\import.php on line 17

Warning: fopen(f/kaka.csv) [function.fopen]: failed to open stream: No such file or directory in D:\Web\sites\excel\import.php on line 19

Warning: fgetcsv() expects parameter 1 to be resource, boolean given in D:\Web\sites\excel\import.php on line 20

Warning: fclose(): supplied argument is not a valid stream resource in D:\Web\sites\excel\import.php on line 24

WTF??

rATRIJS · August 23, 2010

Nu IMO kljuudas pazinjojumi ir gana izskaidrojoshi...

Cibiņš · August 23, 2010

Nu paareejo izlaboju un liidz kveerijam aiziet viss, bet nedalec kapeec sprauzh visus datus no dazhaadaam kolonnaam kas ir CSV failaa vienaa datubaazes tabulas kolonnaa nevis attieciigi katru savaa..??

Nedalec kas pa kluudu "Undefined offset:........."

<?php
error_reporting(E_ALL);
ini_set('display_errors', true);

include "dbconfig.php";
if ((isset($_POST["submitted_form"])) && ($_POST["submitted_form"] == "image_upload_form")) {
$tips2 = $_FILES["csv3"]["type"];
if($tips2 !='application/vnd.ms-excel'){
print "<form action='' method='post' enctype='multipart/form-data' name='image_upload_form' id='image_upload_form' style='margin-bottom:0px;'>";
print "Fails (.csv)<br>";
print "<input type='file'  name='csv3' id='csv3' size='20'><br>";
print "<input type='submit' name='submit' value='Pievienot'>";
print "<input name='submitted_form' type='hidden' id='submitted_form' value='image_upload_form' /></form>";
die ("Tev ir jaliek .csv fails!");
}

$copy2 = copy($_FILES['csv3']['tmp_name'], $_FILES['csv3']['name']); 
$filename = $_FILES["csv3"]["name"];
$handle = fopen("$filename", "r");
while (($data = fgetcsv($handle, 1000, ",")) != false)
{
mysql_query("INSERT INTO rangs(v, niks, TP, AV, PIV, DSK, IST, RP) VALUES ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]');") or die(mysql_error());
}
fclose($handle);
print "pievienots!";
print "<form action='' method='post' enctype='multipart/form-data' name='image_upload_form' id='image_upload_form' style='margin-bottom:0px;'>";
print "Fails (.csv)<br>";
print "<input type='file'  name='csv3' id='csv3' size='20'><br>";
print "<input type='submit' name='submit' value='Pievienot'>";
print "<input name='submitted_form' type='hidden' id='submitted_form' value='image_upload_form' /></form>";
}
else
{
print "<form action='' method='post' enctype='multipart/form-data' name='image_upload_form' id='image_upload_form' style='margin-bottom:0px;'>";
print "Fails (.csv)<br>";
print "<input type='file'  name='csv3' id='csv3' size='20'><br>";
print "<input type='submit' name='submit' value='Pievienot'>";
print "<input name='submitted_form' type='hidden' id='submitted_form' value='image_upload_form' /></form>";
}
?>

Edited August 23, 2010 by Cibiņš

zintis8789 · August 23, 2010

Pameigini:

Izlabo:

while (($data = fgetcsv($handle, 1000, ",")) != false)

uz:

while (($data = fgetcsv($handle, 1000, ";")) != false)

Cibiņš · August 23, 2010

Paldies, straadaa pilniigi viss taga, bet veelviena lietinja, skatiijos ka vinjsh no CSV importee arii pirmo aili kuraa parasti ir teksts boldaa jeb treknrakstaa, jeb vienkaarshi ailes nosaukums, kaa var panaakt lai taa taa pirmaa rinda tiktu vienmeer izlaista?

Edited August 23, 2010 by Cibiņš

zintis8789 · August 23, 2010

Editojam:

while (($data = fgetcsv($handle, 1000, ";")) != false)
{
mysql_query("INSERT INTO rangs(v, niks, TP, AV, PIV, DSK, IST, RP) VALUES ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]');") or die(mysql_error());
}

uz:

while (($data = fgetcsv($handle, 1000, ";")) != false)
{
mysql_query("INSERT INTO rangs(v, niks, TP, AV, PIV, DSK, IST, RP) VALUES ('$data[0]','$data[1]','$data[2]','$data[3]','$data[4]','$data[5]','$data[6]','$data[7]');") or die(mysql_error());
}
mysql_query("DELETE FROM rangs WHERE v='V'");

Edited August 24, 2010 by zintis8789

mickys · August 23, 2010

Kāda mārrutka pēc jāizpilda tas DELETE kverijs ciklā?

Sign In

CSV imortēšana MySQL datubāzēs

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation