| View Question |
|
|
 | ||
|
| Subject:
how to import excel spreadsheet as table into mysql
Category: Computers > Programming Asked by: lightray-ga List Price: $5.00 |
Posted:
02 Oct 2006 17:11 PDT
Expires: 03 Oct 2006 15:11 PDT Question ID: 770290 |
I want to import an excel spreadsheet into mysql as a new table. In particular, I'm trying to import the spreadsheet that is output by http://finance.yahoo.com/q/hp?s=%5EGSPC when you click "Download to Spreadsheet" . I have an existing mysql database running on the same machine that is pulling the data. | |
| |
| |
|
| ||
|
| There is no answer at this time. |
| ||
|
| Subject:
Re: how to import excel spreadsheet as table into mysql
From: frankcorrao-ga on 03 Oct 2006 08:07 PDT |
I don't think that is really feasible because how would mysql know what type to make the columns? And how would it know what the primary key is? Normally you would create the table before hand, or possibly create the table in a script that has the schema outlined already. For instance, if you wanted multiple tables with a similar schema, a script would be the right vehicle. In the import itself, you would usually add 'ignore 1 lines' to the import command so that the header line is dropped. You will also need to use the 'terminated by' clause to tell mysql what the delimiter is, as tab is the default. |
| Subject:
Re: how to import excel spreadsheet as table into mysql
From: stewartrose-ga on 03 Oct 2006 08:48 PDT |
Hi Frank,
Create a mysql table first using myphpadmin or webmin what ever your
hosting company allows...
Then change the folling code to match your needs...good luck..
<?php
require ( "../functions/config.php" );;
## Script for dumping tables from a MySQL database into an excel spreadsheet
$exceldate = (date ("FYD"));
$header = "";
$data ="Members Id\t Address\t City\t Country\t Zip Code\t Telephone\t
Date Added\t Activated\t Tag Yes/No\t Tag Number\t\n";
{
mysql_connect($server, $DBusername, $DBpassword) or die ("Gotta Database Error");
mysql_select_db($database);
$result = mysql_query("SELECT
mp_members.memberid,
mp_members.m_address,
mp_members.m_city_town,
mp_members.m_country,
mp_members.m_post_zipcode,
mp_members.m_telephone,
mp_members.m_dateadded,
mp_members.m_activated,
mp_register.listed,
mp_register.identity_number FROM mp_members,mp_register WHERE
mp_members.memberid=mp_register.memberid") or die ("Gotta Database
Error");
if (!$result) {print "No results could be exported";}
$count = mysql_num_fields($result);
for ($i = 0; $i < $count; $i++){
$header .= mysql_field_name($result, $i)."\t";
}
while($row = mysql_fetch_row($result)){
$line = '';
foreach($row as $value){
if(!isset($value)){
$line .= " \t";
}
elseif ($value != ''){
$line .= $value."\t";
}
else{
$line .= " \t"; //nessecary?!
}
}
$data .= trim($line)."\t \n";
}
//print more than one header per line?
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=$exceldate.xls");
print $data;
}
All the best from Alan |
| Subject:
Re: how to import excel spreadsheet as table into mysql
From: stewartrose-ga on 03 Oct 2006 08:53 PDT |
Ha Ha,
Well that one was downloading into csv file this one is uploading
sorry about that..
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Admin</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<?
$SiteName = "Affiliate Window Datafeed";
// Config section
$server = "localhost"; // Server Host
$DBusername = ""; // Database Username
$DBpassword = ""; // Database Password
$database = "";
$tablename = "";
// Do the stuff
if (isset($_POST["op"]) && ($_POST["op"]=="upload")) {
$handle = fopen('og.csv', 'r');
while ($data = fgetcsv ($handle, 9999, ",")) // Pipe is for pipe
{
foreach ($data as $key => $value) $data[$key] = addslashes($data[$key]);
mysql_connect($server, $DBusername, $DBpassword) or die ("$DatabaseError 54");
$query = mysql_db_query($database,"INSERT INTO $tablename (
`advertiser`,
`language`,
`masterCategoryID`,
`masterCategory`,
`productID`,
`refNumber`,
`name`,
`brand`,
`promotionalText`,
`description`,
`deepLink`,
`imageURL`,
`delivery`,
`validFrom`,
`validTo`,
`currency`,
`displayPrice`,
`searchPrice`
)
VALUES('$data[0]',
'$data[1]',
'$data[2]',
'$data[3]',
'$data[4]',
'$data[5]',
'$data[6]',
'$data[7]',
'$data[8]',
'$data[9]',
'$data[10]',
'$data[11]',
'$data[12]',
'$data[13]',
'$data[14]',
'$data[15]',
'$data[16]',
'$data[17]'
)");
$result = mysql_query($query);
}
print "$result";
fclose ($handle);
print "$SiteName Database Update Successful";
}
else {
print "<form method=post>
<INPUT NAME=\"op\" TYPE=\"hidden\" VALUE=\"upload\">
<p><INPUT TYPE=\"submit\" VALUE=\"Click here to upload the $SiteName
Database\"></p></form>";
}
?>
</body>
</html>
All the best from Alan |
| Subject:
Re: how to import excel spreadsheet as table into mysql
From: lightray-ga on 03 Oct 2006 14:47 PDT |
That makes sense. I've been accustomed to MS Access where it does this automatically, and hoped that there was an equivalent in mySQL. Thanks for the answer. |
| Subject:
Re: how to import excel spreadsheet as table into mysql
From: lightray-ga on 03 Oct 2006 14:55 PDT |
I think you need to re-submit the answer for me to be able to approve it and authorize payment. right now the button doesn't appear that allows me to approve the answer. |
If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you. |
| Search Google Answers for |
| Google Home - Answers FAQ - Terms of Service - Privacy Policy |