Import Excel file

[ven.ganeva]

Does anyone know if there is a good open source script that imports excel files (csv, xml, whatever) into a MySQL database?  ???

[johnjuan]

you can try this, as a macro in a an excel spreadsheet (csv format)

 

(if you have a better one, could you post it?)

 

Sub Button2_Click()
    Dim ad As ADODB.Connection
    
    Set Conn = New ADODB.Connection   ' Connect to remote MySQL database
    Conn.ConnectionString = "Driver={mySQL ODBC 3.51 Driver};Server=ABCDE;Port=1234;Option=4;Database=MYDATABASE;Uid=MYUSERNAME;Pwd=MYPASSWORD;"
    Conn.Open
    
    Set rs = New ADODB.Recordset
    rs.CursorLocation = adUseClient
    StrSample = "Sample"
    rs.Open StrSample, Conn, adOpenStatic, adLockOptimistic, adCmdTable
    
    iRow = 1
    
    While Not rs.EOF
        Worksheets("Sheet1").Cells(iRow, 3).Value = rs.Fields(0).Value
        Worksheets("Sheet1").Cells(iRow, 4).Value = rs.Fields(1).Value
        Worksheets("Sheet1").Cells(iRow, 5).Value = rs.Fields(2).Value
        Worksheets("Sheet1").Cells(iRow, 6).Value = rs.Fields(3).Value
        Worksheets("Sheet1").Cells(iRow, 7).Value = rs.Fields(4).Value
        Worksheets("Sheet1").Cells(iRow, .Value = rs.Fields(5).Value
    
        iRow = iRow + 1
        On Error GoTo MyEnd
        rs.MoveNext
    
    Wend
    
MyEnd:
    rs.Close
    Conn.Close
    
End Sub

[johnjuan]

OOPS

 

THAT GRABS DATA FROM A MYSQL DATABASE

 

THIS CODE UPLOADS A CSV FILE INTO A MYSQL DATABASE

 

File is on the server... first part of the code uploads the file from a web form to the server

 

<?php

$target_path = "";

$target_path = $target_path . basename($_FILES['uploadedfile']['name']);

if (move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) {
echo "The file ". basename( $_FILES['uploadedfile']['name']).
" has been uploaded";
} else{
echo "There was an error uploading the file, please try again!";
}

/*
$db1 = dbase_open($target_path, 0);
$db2 = dbase_open('empty.dbf', 2);

if ($db1) {
$record_numbers = dbase_numrecords($db1);
for ($i = 1; $i <= $record_numbers; $i++) {
$row = dbase_get_record($db1, $i);
$row1 = array($row[0], $row[1], $row[2], $row[3], $row[4], $row[5]);

dbase_add_record($db2, $row1);

}
}

dbase_pack($db2);

dbase_close($db2);
dbase_close($db1);
*/


$dbhost = 'localhost';
$dbuser = 'USERNAME';
$dbpass = 'PASSWORD';

$link = mysql_connect($dbhost, $dbuser, $dbpass);
if (!$link) {
die('Could not connect: ' . mysql_error());
}

$dbname = 'DATABASENAME';
mysql_select_db($dbname);

$lines = file($target_path);
foreach ($lines as $line_num => $line)
{
$fields = explode(",", $line);
$mydate = explode("/", $fields[0]);
$year = substr($mydate[2], 0, 4);
$month = $mydate[0];
$day = $mydate[1];
$mydate1 = $year."-".$month."-".$day;
$query = "('".$mydate1."', '".$fields[1]."', '".$fields[2]."', '";
$query = $query.$fields[3]."', '".$fields[4]."', '".$fields[5]."')";
$query = "INSERT INTO Sample VALUES ".$query;

mysql_query($query) or die('Error, insert query failed');
}

mysql_close($link);


?>