/    Sign up×
Community /Pin to ProfileBookmark

Importing Excel Sheet ->MySQL

Copy linkTweet thisAlerts:
Jan 12.2007

hi,

i am having a excel file (attttached in a zip file with this topic) , and the file is also somewhat clumsy to import ; i want to import that file to a MySQL database in a table.

can u suggest something ..? any idea ….

thanks for reading patiently

-gaurav

[upl-file uuid=8a4651fa-f670-4866-ad1c-ce359667ec28 size=4kB]example_excel_sheet.zip[/upl-file]

to post a comment
PHP

6 Comments(s) ↴

Copy linkTweet thisAlerts:
@NightShift58Jan 12.2007 — Is CSV an option?
Copy linkTweet thisAlerts:
@avaJan 12.2007 — Yes, CSV seems to be the best way.

Export data from excel as CSV. Then use the MySQL command

LOAD DATA INFILE 'path/export.txt' INTO TABLE my_table;

Of course, if you want a challenge, you can also try a COM script ?
Copy linkTweet thisAlerts:
@NightShift58Jan 12.2007 — If you can't get the Excel data in CSV format in an automated fashion - that is, without having to manually convert 22 sheets a week, 52 weeks a year - there's a piece of software out there, actually a PHP class, that purports to do just what you may need.

Take a look at: http://sourceforge.net/projects/phpexcelreader/

If the spreadsheets have a fixed format and they don't move cells around on you, it should do the trick for you, as you'll be able to pick individual cells form the sheet and store them as variables, which in turn can be inserted in one or more records.
Copy linkTweet thisAlerts:
@gaurav_095authorJan 12.2007 — thanks for the help , let me try one of these solutions ..


thanks once again ......................
Copy linkTweet thisAlerts:
@NightShift58Jan 12.2007 — You're welcome!
Copy linkTweet thisAlerts:
@jenyJan 12.2007 — try this one....


[code=php]<?
if($_POST==array())
{
?>
<form method="post" action="csv_sample.php" enctype="multipart/form-data">
<input type="file" name="file" tabindex="1"/>
<input type="submit" name="submit">
</form>

<?php
}
else
{
$uploaddir='upload/';
$fimadd=date("ymd").date("gis");
$imagename=$fimadd.$_FILES['file']['name'];
$uploadfile=$uploaddir.$imagename;

$imagetmp=$_FILES["file"]["tmp_name"];
if(move_uploaded_file($_FILES['file']['tmp_name'],$uploadfile))
{
//echo "file uploaded";
echo "delete * from table";
//mysql_query("delete * from table"); // uncomment this query
$fd = fopen ($uploadfile, "r");
while (!feof ($fd))
{
$buffer = fgetcsv($fd, 4096);


echo "<table><tr>";
for ($i = 0; $i <count($buffer); ++$i)
{
echo "<td>";
$replaced=str_replace(";", ",", $buffer[$i]);
//mysql_query("insert into members ('name','age') values($replaced)"); //uncomment this query
echo "insert into members (id,name,password,email) values($replaced)";
if ($buffer[$i] == "")
{
$buffer[$i] = "&nbsp;";

}
//echo "$buffer[$i]</td>";
}
echo "</tr></table>";
}
fclose ($fd);
}
}

?>[/code]
×

Success!

Help @gaurav_095 spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 — BETA 5.4,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @Yussuf4331,
tipped: article
amount: 1000 SATS,

tipper: @darkwebsites540,
tipped: article
amount: 10 SATS,

tipper: @Samric24,
tipped: article
amount: 1000 SATS,
)...