Uploading and inserting CSV files in to mySQL

Azazeal

Hi Folks.
I am in the process of writing an admin page for a mySQL database.
I have it set right now that a user can enter 1 user at a time, but I would like them to be able to upload a comma delimited file, and insert that into their database.
The basic table is:
user_id, user_name, user_email, user_password, unique_id

I am getting a grip on the file upload, but the insert is where I am stuck at.

Do any of you have any references that I may use as a springboard for this?

Thanks
Az

Azazeal

Ok...I have found the method of this, but this leaves one issue.
If the comma delimited file is provided by a user, chances are the password isn't going to be encrypted md5() style.
Any ideas on how that can work into the upload?
Az

90Nz0

Can you please post the code for this, im finding it really hard to do some thing similar... this is what I want to do

1 - Website User creates a CSV from Excel that contains stock. This then gets added to there user dir as "$Date.csv" - DONE

2 - Script opens this file
For each row, create a new <TR>.
For each Column, make a new <TD><input name="field1" value=<? echo $row[field1]; ?></TD>.
The $row[field1], $row[field2], $row[field3] etc are the code. I don't care how it gets inserted into the <input>, just that it does.
Then close the <TR>. - NEED HELP

3 - User submits form after checking everything is in it's correct place, and selecting wether it's an 'insert' or 'update'. Then do the correct MySQL bits - DONE

nnichols

This should give you something to work from -

$record_delimiter = "\n";
$field_delimiter = ",";

// open uploaded file and read into a var
$filename = "upload_dir/file.name";
$handle = fopen ($filename, "r");
$contents = fread ($handle, filesize ($filename));
fclose ($handle);

// nowcreate array - each element being a row from the file
$contents_array = explode($record_delimiter, $contents);

reset ($contents_array);
echo '<table border="1"><tr><th>ID</th><th>Name</th><th>Password</th></tr>';
$i = 0;
while (list ($key, $val) = each ($contents_array)) {
	$fields = explode($field_delimiter, $val);
	echo '<tr><td><input type="text" name="row['.$i.'][id]" value="'.$fields[0].'"></td>
		<td><input type="text" name="row['.$i.'][name]" value="'.$fields[1].'"></td>
		<td><input type="text" name="row['.$i.'][password]" value="'.$fields[2].'"></td></tr>';
$i++;
}
echo '</table>';

Hope this helps 😉

90Nz0

Cheers, I did it by modifying a script I found on the 'net, creating the following funct

function GetPreview($csvFile, $delimiter, $previewLimit, $useFRAH) {

$getPreview['previewData'] = '<table cellspacing="1" cellpadding="2" border="0" class="grid">';
$line = 0;

if (!$myFile = @fopen(urldecode($csvFile), "r")) die("Can't re-open .CSV file: " . $csvFile);

while (($line < $previewLimit) && ($data = fgetcsv($myFile, 1024, $delimiter))) {
	$getPreview['numOfCols'] = count($data);

	if ($line == 0) {
		$getPreview['previewData'] .= '<tr align="center">';
		for ($i = 0; $i < $getPreview['numOfCols']; $i++) {
			$getPreview['previewData'] .= "\n\t\t<td>Col " . ($i+1) . "</td>";
		}
		$getPreview['previewData'] .= "</tr>";
		$getPreview['previewData'] .= ($useFRAH != "") ? "\n\t<tr id=\"headerRow\" class=\"header\">" : "\n\t<tr id=\"headerRow\">";
	} else {
		$getPreview['previewData'] .= "\n\t<tr>";
	}

// Start SQL STRING
$MyRowNumber = 0;
$sql = $sql."INSERT INTO `mbProd` (`pID`, `pPartNo`, `pDesc`, `pManu`, `pCon`, `pQty`, `pPrice`, `pCompID`, `pDate`) VALUES (\n";

	for ($i = 0; $i < $getPreview['numOfCols']; $i++) {
		$getPreview['previewData'] .= (strlen($data[$i]) > 20) ? "\n\t\t<td>" . stripslashes($data[$i]) . "</td>" : "\n\t\t<td>" . stripslashes($data[$i]) . "</td>";

// Sort out row values
// If first row - make blank for ID
if ($MyRowNumber == 0) {$sql = $sql."'',\n";} 

// Number Bassed rows - no '' encaps
elseif ($MyRowNumber == 5) {$sql = $sql.stripslashes($data[$i]).",\n";}
elseif ($MyRowNumber == 6) {$sql = $sql.stripslashes($data[$i]).",\n";}
elseif ($MyRowNumber == 7) {$sql = $sql.stripslashes($data[$i]).",\n";}

// date() column
elseif ($MyRowNumber == 8) {$sql = $sql."now()\n";} 

// No rules, Make standered
else {$sql = $sql."'".$data[$i]."',\n";}

// Plus the Number
$MyRowNumber = $MyRowNumber + 1;
}

$sql = $sql.");\n\n\n\n";	
$MyRowNumber = 0;

	$getPreview['previewData'] .= "\n\t</tr>";

	$line++;

}

fclose($myFile);

$getPreview['previewData'] .= "</table>";
$getPreview['previewData'] .= "<BR><BR><center><B><u>SQL</u></b></center><BR>".nl2br($sql)."<BR><BR><BR><BR>$sql";
$returnthisshit = $getPreview['previewData'];
return $returnthisshit;
}

This produces a table with the correct stuff (just need to add the <input> stuff) and also below it the correct SQL I'll need.