Most clients in today’s market have hundreds and hundreds of products that they offer to their customers. I notice a lot of clients do not want to take the jump to adding their products to a website because they do not want to have to do all the data entry that is involved.
Well now they can! I am going to explain how to import an inventory spreadsheet into a MySQL database.
First you must make sure your spreadsheet is clean and that there is only one product per line.
Product Name Description Category Weight Price
Once you have a clean spreadsheet you can highlight every cell and click edit, copy. Now open up notepad and click edit paste.
You should now have all your data in notepad with the cells seperated by “tabs”. Simply highlight one of the tabs and click edit copy. You now have the “tab” symbol copied. Go up to find and replace and paste in the the tab symbol as find. It should look like a little box. Now for replace enter a character that doesn’t exist in your data such as a “|”. This will remove all the tabs and replaced them with a delimiter. You may be wondering why didn’t we just do a save as .csv from excel? Well the problem with that is what if you data contains a comma? It blows up the import.
Now that you have a clean .txt file you are almost ready to import. You can go ahead and copy this file to your server where your php script is going to be:
$file = “datafile.txt”;
$fh = file($file);
foreach($fh as $key=>$line) {
$line = explode(“|”, $line);
$name = $line[0];
$description = $line[1];
$category = $line[2];
$weight = $line[3];
$price = $line[4];
$query = “INSERT INTO products (`ID`, `Name`, `Description`, `Category`, `Weight`, `Price`) VALUES (”, ‘$name’, ‘$description’, ‘$category’, ‘$weight’, ‘$price’)”;
}
