[RESOLVED] advice: PHP for insert/update from CSV's

[ATS16805]

i have several files in MS Excel format-- a fairly massive amount of data logged there, which i need to have in my MySQL db. this data is required for the "bigger picture"-- so i have to get it in there somehow-- but it's a rather tedious process as you might imagine.

i've tried a few things-- thanks to EditPlus, i've been able to take the data from Excel, replace characters-- such as:

FIND-WHAT: "\n"
REPLACE WITH: "\n,"

and a few other tricks i've discovered which make for a pretty nifty way of prepping CSV data for a MySQL insert query-- except that i need to run an UPDATE query-- and to take all the data from the Excel file via CSV isn't going to be as easy as simple tweaks in EditPlus because of the more complex syntax required for UPDATE.

so, i'm thinking i can just write a little PHP page to do this task for me-- and even better yet, if i can simply load a CSV into the MySQL db via PHP reading/parsing a text file, that action would be MOST convenient, and very awesome.

i know that php can work w/ text files and such-- but is what i want to do possible? if so, what is your recommendation? do you have a URL from the manual that you'd suggest I read?

maybe you have encountered a similar obstacle in your own project? if so, how did you handle the tedious data transfer? i could do it by hand of course, but i'm saving that for a last possible resort-- and even then, i'll probably make an html text input form so i can do an entire row at once instead of one cell at a time.

thank you for your help!!!

[sfullman]

Hi there,

this is the most basic code, I usually do an insert but you seem to need to do an update. Just imagine the primary key was on column 0 (a numeric id for example):

$fp=fopen($myfile,'r'); //a legal csv file
while($r=fgetscsv($fp,100000)){
   //we now have the row as array $r
   $result=mysql_query("SELECT CreateDate [my thought - you might need that] from mytable where ID='".$r[0]."'"); //make sure it doesn't die
   if(mysql_num_rows($result)){
      //this requires an update statement, but you may want to check the createDate, update only partial data, etc.

   }else{
      //this requires an insert

   }
}

#be sure to addslashes() for the inserted data
#I've done stuff like this with thousands of records without a problem

[ATS16805]

hey there! thank you very much for that!!

i haven't tried it yet-- but wait till you see this crazy thing i made in the meantime to get my Excel stuff into my db! (and while my connectivity was down temp for some reason).
hahaha. it's nuts, but it works accurately for getting the data in there-- except for the fact that it's still tedious-- i have to keep copy/pasting rows from the Excel sheet, one row at a time-- and an intermediate step of replacing "\n" with ",\n" using EditPlus (which has become quite my little friend this day!-- didn't realize it was so powerful for that stuff, and regexpressions, etc). i was so happy when i got it to finally work that i've subsequently been wasting an incredible amount of time working on trying to get a series of implode() and explode() to do the job for me. this is how i enjoy my day. what in heavens' is wrong with me!!? hhaha. oh well. at least i'm not out robbin' convenience stores or something slightly less constructive, so i suppose it's a good thing. 😉

/* filename1.php */

<?php

if (empty($_GET['var'])) {
	$form_id = "<input type=\"text\" value=\"\" name=\"new_row\" size=\"2\" />route id<br />";

} else {
	$id = $_GET['var'];
	$form_id = "<code>Enter the times for route no.".$id."</code><br />
	<input type=\"hidden\" value=".$id." name=\"new_row\" size=\"2\" />route id<br />";
}

$htmlblock = <<<EXCELTO
<h1>Timetable manager:</h1>

<form action="nowinthere.php" method="post">
<table>
<tr>
<td>Time of Day:<br /><textarea name="csvdata" rows="25" cols="10"></textarea></td>
$form_id
</tr>
<tr><input type="submit" name="excelform" value="explode!"></tr>
</table>
</form>
EXCELTO;
echo $htmlblock; 
?>
/* end filename1.php */

/* nowinthere.php */
<?php
$conn = mysql_connect("localhost", "xxx", "xxx") or die(mysql_error());
mysql_select_db("zzz", $conn) or die(mysql_error());

$times = "rt01, rt02, rt03, rt04, rt05, rt06, rt07, rt08, rt09, rt10, rt11, rt12, rt13, rt14, rt15, rt16, rt17, rt18, rt19, rt20, rt21";
$stops = $_POST['csvdata']; // (7:17, 8:22, 9:27)
$id = $_POST['new_row'];

$stopexplode = explode (",",$stops);
$stopstring = implode(",",$stopexplode);

$timesarray = explode(",", $times);
$stopsarray = explode(",", $stopstring);

$combined = array_combine($timesarray,$stopsarray);

$insert = "";
$preinsert = "UPDATE h_times_new"; /* tablename insert in quotes */ 
$preinsert .= " SET ";

foreach($combined as $key => $value) {
	$insert .= $key."='".ltrim($value)."',";

}
$trimmed = rtrim($insert, ",");
$insert = $trimmed .= " WHERE time_id = '".$id."';"; 
$htmlblock = $preinsert.$insert;

$query = mysql_query($htmlblock)or die(mysql_error());

?>
<h1>Congratulations!</h1>
	<p>You've successfully updated the Database with
	the following information:</p>
		<code style="color:rgb(125,255,50);font-size:90%;">
<?php
echo $htmlblock."</code>"; 
     $new_row = $id + 1;
	 echo "<p>Go back and <a href=\"gimme_csv.php?var=$new_row\">add the next route</a>'s time-grid.</p>";
?>

[ATS16805]

hey, sfullman, i'm not sure i understand exactly what's going on there in your code. hmm..

could you explain just a little more, please? i'd greatly appreciate it!
i looked up FOPEN and FGETCSV (assuming that's what you meant? as it appears there is no function FGETSCSV as you've typed it exactly?), but i still don't quite get what's going on. i mean-- why the SELECT query? also, i'm not familiar w/ using createDate either.

the reason i have to do UPDATE's is that there's a row in the Excel sheet which i've used to populate all rows of the field called "stop_code" in my db.
what you see in the screen capture as the fields, rt01, rt02, etc-- those are row names in the Spreadsheet which hold all of those times in the cells-- so, in order to get those stop_codes in there (prior to my idea about the CSV), this was the way i was thinking i must do it-- take a look at the way the excel sheet is laid out vs. the way i need to have my db, and maybe my confusion will make sense-- to be honest, i don't know if i'm making this more difficult that it needs to be, or what. ugh! i appreciate the help though!!

edit: maybe it would help if i explain the purpose-- this is showing bus routes. the stop code represents all of the places in town where people can stand and wait for a bus-- the times are those times-of-day when the bus is at that point, depending on which revolution of the day, which is depicted as rt01 (early morning, hence it didn't start yet-- but i have no idea why it begins its first revolution half-way through the route!), and so on. the other null value represents, i believe a limited route for summer (this is a college town).

observe: 27, 28, 29 in the Excel pic are shown as h27, h28, h29 in the other. oh, and time_id is my primary key, (auto increment, although it doesn't really need to be).
EDIT: one more thing-- rt01 eventually has values-- but they don't appear in the spreadsheet to way down there. i thought this would be the best way to show everything and keep the png small enough. thanks again!

[Roger_Ramjet]

Step back and think about this for a moment.

You have data in Excel spreadsheets that you can, or have, saved out as CSV files. Our clients all send us excel files and I have to deal with this all the time. I personally refer a Tab-delimited text file to a CSV as it copes with embedded commas automatically; but that is a matter of choice and content.

Step 1 is to get that data - uncorrupted - into your db. You do this by creating 'holding-pens' - tables that are designed to accept the data in it's text-file format. So creat work tables with most columns as varchar or text as size dictates.

Import your data into work tables. You can then use the power of SQL functions to verify, edit, and compare the new data with existing db contents. In the rare cases where the mysql inbuilt functions just won't do it then you can fall back on php.

eg. you can use the mysql date and time functions in the SELECT clause of your INSERT or UPDATE queries to convert your time data to mysql TIME data type

Work tables also allow you to more fully normalise your data into seperate tables as it shoud be.

[ATS16805]

RR, first let me just say that i can't tell ya how much i'm beating my head against the wall for having not gone to php.net and done a search for keyword: CSV!! geezs! i would have been finished with this 3 days ago! hahaa.... grrr!

i've learned SOO much just from sfullman's suggestion, and then of course the examples at fgetcsv() that i'm just darned upset about it. 😉

anyway-- now, what you're telling me here-- about the power of SQL. i hadn't thought of that-- however, i am not what i would consider to be much of a MySQL power-player-- don't get me wrong, i'm definitely getting better at it, but perhaps you'd understand why that wasn't my first thought.

let me see if i understand you correctly-- you're telling me to, instead of using some ancillary PHP file/ script/ whatever (as i've hopelessly been attempting thus far), i could use some ancillary tables instead to ultimately get the stuff into which table i really need it? see, to me that seems like a problem because i really don't know enough wizardry to move data around, between tables like that in MySQL. i'd love to learn, and this would be an excellent opportunity to do so! where do you suggest i begin?

really-- this whole thing is much of a "learning" project for me-- although it will hopefully go into production sometime, i am not "under the gun" so to speak-- i merely want to do the best possible job, and learn as much as i can about db applications-- so i'm having fun w/ it thus far, but it's moments like this which are a bit frustrating because i feel as if i've reached such a bog-down, and yet, i'm not even doing anything really in terms of the greater application-- that's the bummer of it! ugh-- all this ancillary stuff-- will i ever get to the goodies!?!
😉

ah, i suppose such is the nature of "the real world" though, eh?

EDIT:
you know, after taking a closer look at the manual example1 on fgetcsv(), i'm confident that i can tweak that very script to do exactly what i am looking for-- for example, it's telling me the row number, and it's reading every line in that row-- all i need to do is turn the row number into my db row number and stuff that data into an update query, and i should be good to go!

(uh... right?)

[ATS16805]

i'm almost there, but i'm startin' to bug-out. i'm hoping someone can help me to find my error; what i need to change here... thanks! (i'll probably get it in a few mins, but just in case my eyes pop-out in the meantime... thanks!)

<?php
$row = 1;
$myfile = "../csv/H.csv";
$handle = fopen($myfile, "r");

$insert = "";
$preinsert = "UPDATE h_times_new"; /* tablename insert in quotes */ 
$preinsert .= " SET ";

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
   $num = count($data);
   echo "<p> $num fields in line $row: <br /></p>\n";

   for ($c=0; $c < $num; $c++) {
   			$pad = str_pad($c, 2, "0", STR_PAD_LEFT);
		   $field = "rt".$pad;

   	$insert .= "UPDATE h_times_new SET ".$field."='".$data[$c]."' WHERE time_id = '".$row."';<br />";
$row++;	
}
echo $insert;
}
fclose($handle);
?> 

this is a combination of what i made yesterday (as shown in my 2nd post in this thread, and the script that i found at php.net). thanks for reading! i'll be sure to post it once it's been fixed (or maybe you'll post that final bit which will fix it) so that maybe it might help someone else too!

[ATS16805]

hey there. sorry if my previous reply sounded rude at all-- i had been up for about 48 hours, and i wasn't thinking clearly, and preoccupied since then. now that i have had a chance to look at this, i see that there were a few simple things i was overlooking.

i realize that my output, because of this line:

$insert .= "UPDATE h_times_new SET ".$field."='".$data[$c]."' WHERE time_id = '".$row."';<br />"; 

although the file is apparently being read accurately, my output is pretty far off from what i need to go into my database. for example-- my table has fields for the "route revolutions" from rt01, to rt21 which indicates the 21st, and final route of the day, at night-- so any output showing rt22 is WRONG. and i should only have a total of 48 rows, matching the 48 stops-- but my UPDATE query, as this little erroneous script is outputting, is giving me 48 routes instead of 21 (so i've got variables juxtaposed -- bus-stops instead of route loops, and instead of being limited to 48 bus-stops (which it does the first time around through the FOR loop) it seems to be concatenating the output upon each new loop through the while statement-- such that the very last line of output shows:

UPDATE h_times_new SET rt47='7:30' WHERE time_id = '987';

.

i see a division equasion here:

987/47 = 21. i am supposed to have 21 rows and 48 stops, but something is causing the CSV rows to multiply the amount of CSV columns, resulting in this HUGE erroneous output.

i think i had better start from scratch, but i wanted to illustrate that i realized how very FAR from "almost there" i really was that day!

i think i'm goign to take another look at fullman's code and see if i can make some more progress based on his work.

thanks for reading!!

oh, and by the way-- i'm still all ears for your feedback-- anyone who might care to chime in, i'd appreciate it. thanks.

[ATS16805]

i'm writing again because i'm still really lost-- and i should be more far along on the whole thing, but i really want to figure out this whole stinkin Excel conversion thing or i'm not going to be satisfied!

the first time i tried sfullmans's offering, i didn't get anything from it because i was too fixated on trying to understand the functions therein, and how their purpose applied to my data-- but this time i disregarded those particulars and made a little script to get a better look at what those functions are actually doing in this arrangement, and what sfullman really intended for me to get from it.

here's my code.
FYI - the mysql_result() lines are inserted as part of an "extract MySQL row data" wizard in my preferred IDE, TSW WebCoder 2005 , which has several PHP/MySQL productivity enhancing options while it's connected to the MySQL server - an easy way for me to test sfullman's code w/out understaning what he wanted me to get from the query, so i took a stab at echoing the bus-stop codes to see what would happen:

$myfile = "../csv/H.csv";

 $fp=fopen($myfile,'r'); //a legal csv file
while($r=fgetcsv($fp,100000)){
   //we now have the row as array $r
   $result = mysql_query("SELECT time_id, stop_code, rt01, rt02, rt03, rt04, rt05, rt06, rt07, rt08, rt09, rt10, rt11, rt12, rt13, rt14, rt15, rt16, rt17, rt18, rt19, rt20, rt21 FROM h_csv_test WHERE time_id = '".$r[0]."'");	//make sure it doesn't die
$time_id = mysql_result($result, 0, "time_id");
$stop_code = mysql_result($result, 0, "stop_code");
$rt01 = mysql_result($result, 0, "rt01");
$rt02 = mysql_result($result, 0, "rt02");
$rt03 = mysql_result($result, 0, "rt03");
$rt04 = mysql_result($result, 0, "rt04");
$rt05 = mysql_result($result, 0, "rt05");
$rt06 = mysql_result($result, 0, "rt06");
$rt07 = mysql_result($result, 0, "rt07");
$rt08 = mysql_result($result, 0, "rt08");
$rt09 = mysql_result($result, 0, "rt09");
$rt10 = mysql_result($result, 0, "rt10");
$rt11 = mysql_result($result, 0, "rt11");
$rt12 = mysql_result($result, 0, "rt12");
$rt13 = mysql_result($result, 0, "rt13");
$rt14 = mysql_result($result, 0, "rt14");
$rt15 = mysql_result($result, 0, "rt15");
$rt16 = mysql_result($result, 0, "rt16");
$rt17 = mysql_result($result, 0, "rt17");
$rt18 = mysql_result($result, 0, "rt18");
$rt19 = mysql_result($result, 0, "rt19");
$rt20 = mysql_result($result, 0, "rt20");
$rt21 = mysql_result($result, 0, "rt21");


   if(mysql_num_rows($result)){ 

   $rows = mysql_num_rows($result);
   echo "<p>the stop code is ".$stop_code."</p>";
   echo "<p>mysql num rows is".$rows."</p>";
      //this requires an update statement, but you may want to check the createDate, update only partial data, etc.

   }else{ echo $rt02;
      //this requires an insert

   }
}

(i realize now that the $rows is irrelevant, but rather than filter that, i just showed exactly what i did...)

and here's what i get as output from that:
Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 5 in C:...\sfullman_orig.php on line 12

(one of those for lines 12 - 34, at which point it does echo: )

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 5 in C:\...\sfullman_orig.php on line 34
the stop code is h31
mysql num rows is1
the stop code is h32
mysql num rows is1
the stop code is h33
mysql num rows is1
the stop code is h34
mysql num rows is1
the stop code is h34a
mysql num rows is1
the stop code is h34b
mysql num rows is1
the stop code is hc131a
mysql num rows is1
the stop code is hc131b
mysql num rows is1
the stop code is h27
mysql num rows is1
the stop code is h28
mysql num rows is1
the stop code is h29
mysql num rows is1
the stop code is h30
mysql num rows is1
the stop code is h31
mysql num rows is1
the stop code is h32
mysql num rows is1

and one more series of the mysql_result warnings (which, btw, i realize i wouldn't get those if i were using a different method-- but i used the data extraction wizard to investigate sfullman's script, not for any preferred functionality necessarily)

Warning: mysql_result() [function.mysql-result]: Unable to jump to row 0 on MySQL result index 20 in C:\...\sfullman_orig.php on line 34
the stop code is h34
mysql num rows is1
the stop code is h34b
mysql num rows is1
the stop code is hc131b
mysql num rows is1
the stop code is h28
mysql num rows is1
the stop code is h30
mysql num rows is1

so, i'm looking for ideas-- based on the screen shots i provided above, for how to tweak this so that i can get what i need from the CSV file and put it into my db.

what can i do to test if the CSV file data is even being read at all? or-- if this shows that it's being read, then how do i move on to the second step, which i think would be to figure out how make use of the raw csv data.

suggestions? help? thanks!

EDIT: i added this-- i think i'm on to something finally-- but i could still use help, advice, a knock in the head-- whatever you have for me 😉

 if(mysql_num_rows($result)){ 

	foreach($r as $csvdata) {
		echo "the \$r=fgetcsv shows:".$csvdata."<br />";
	}

which gives me:

the $r=fgetcsv shows:7:17
the $r=fgetcsv shows:7:18
the $r=fgetcsv shows:7:19
//snip...
the $r=fgetcsv shows:7:25
the $r=fgetcsv shows:7:27
the $r=fgetcsv shows:7:30
the stop code is h30
mysql num rows is1

which appears to be the ENTIRE csv contents (7:17 is AM, and 7:30 is PM, so the entire day is represented)... now i just have to figure out what to do here...

[ATS16805]

okay-- here's my solution. this worked fine for me.

SITUATION:
read above for details on the state of my tables, but in general, my table was populated w/ default values because i needed to insert one field's entire set of rows-- which resulted in my need for an UPDATE query instead of an INSERT query-- which i think is what caused me all of this extra work (maybe not, but hey, at least i learned something!!)

i hope someone can get some use of this. keywords: excel, CSV, extract data, migrate data from excel to mysql, from xls to table, etc

$myfile = "../csv/H.csv";

$exceldata=fopen($myfile,'r'); //a legal csv file

$f = 0;
while($r=fgetcsv($exceldata,100000)){   //we now have the row as array $r

$result = mysql_query("SELECT time_id FROM h_csv_test WHERE time_id = '".$r[0]."'");	

$f++;

$pad = str_pad($f, 2, "0", STR_PAD_LEFT);
$fieldname = "rt".$pad;
echo "<br />this will insert into fieldname:".$fieldname."<br />";
if(mysql_num_rows($result)){ 

	foreach($r as $row => $csvdata) {

		$timeidcsv = ($row + 1);

	$timequery = mysql_query(
	"SELECT time_id, stop_code FROM h_csv_test WHERE time_id = '".$timeidcsv."'");

		   $row_for_stops = mysql_result($timequery, 0, "stop_code");

			echo "bus stop $row_for_stops should be showing time:".$csvdata."<br />";
$insert = mysql_query("UPDATE h_csv_test SET ".$fieldname."='".$csvdata."' WHERE stop_code = '".$row_for_stops."'");

	}

   }else{ 

   }
}

anyone who is trying to get data from an excel file into your mysql db, please feel free to consult me about this if you wish. i might not be of any help, but i have been playing w/ it for a few days, so i definitely have some experience, and the above solution works for me-- although my CSV file had some NULL values which i had to assign a value of 1 so the PHP would read those rows, but other than that, it works like a charm.

thanks to sfullman for helping me w/ this!!