Help, import script for access text file not working.

[phn737]

I am trying to use the script below to import data from a text file exported from access 97, but the script is not working. I am getting the message: "Invalid DELETE query". Could someone please help. Thank you.

<?php
# MySQL database User ID, Password and DB name
$sql_id = &quot;root&quot;;
$sql_pwd = &quot;password&quot;;
$sql_db = &quot;inventorydb&quot;;

# Connect to the database
mysql_connect('',$sql_id,$sql_pwd);

# Delete the current content of the table
$result = mysql_db_query($db,&quot;DELETE FROM checkout_db&quot or die (&quot;Invalid DELETE query&quot;

# Optimize the current table (recover empty space)
$result = mysql_db_query($db,&quot;OPTIMIZE TABLE checkout_db&quot or die (&quot;Invalid OPTIMIZE query&quot;

# Load local comma separated, fields enclosed by quotes text database - File has to be in the same directory of this file
$result = mysql_db_query($db,&quot;LOAD DATA LOCAL INFILE 'export_file_name.txt' INTO TABLE checkout_db FIELDS TERMINATED BY ',' ENCLOSED BY '\&quot;'&quot or die (&quot;Invalid DATA LOAD query&quot;

# Get how many records are present in the table now
$result = mysql_db_query($db,&quot;SELECT * from checkout_db&quot or die (&quot;Invalid SELECT query&quot;
$rows_count = mysql_num_rows($result);

echo &quot;Records: $rows_count&quot;; mysql_free_result($result);

?>

 

[sleipnir214]

$db isn't initialized, for one thing....

Instead of just using &quot;or die(...)&quot; when your mysql commands fail, print out a more meaningful error message.

Take a look at mysql_error() (

http://www.php.net/manual/en/function.mysql-error.php)

______________________________________________________________________
TANSTAAFL!

 

[phn737]

Thank you for your comment, I was able to get the script to work with the error message from mysql. The script below works for win32 and apache. The pathname for the text file might needs changing on unix environment.

<?php
# MySQL database User ID, Password and DB name
$sql_id = &quot;root&quot;;
$sql_pwd = &quot;password&quot;;
$sql_db = &quot;dbname&quot;;

# Connect to the database
mysql_connect(&quot;localhost&quot;, $sql_id, $sql_pwd) or die(mysql_error());

mysql_select_db($sql_db) or die(mysql_error());

# Delete the current content of the table
$result = mysql_db_query($sql_db,&quot;DELETE FROM tablename&quot or die (mysql_error());

# Optimize the current table (recover empty space)
$result = mysql_db_query($sql_db,&quot;OPTIMIZE TABLE tablename&quot or die (mysql_error());

# Load local comma separated, fields enclosed by quotes text database - File has to be in the same directory of this file
$result = mysql_db_query($sql_db,&quot;LOAD DATA LOCAL INFILE 'e:/htdocs/db_upload/export_file_name.txt' INTO TABLE tablename FIELDS TERMINATED BY ',' ENCLOSED BY '\&quot;'&quot or die (mysql_error());

# Get how many records are present in the table now
$result = mysql_db_query($sql_db,&quot;SELECT * from tablename&quot or die (mysql_error());
$rows_count = mysql_num_rows($result);

echo &quot;Records: $rows_count&quot;; mysql_free_result($result);

?>

 

[phn737]

The script is importing the data, but there is some problem. Since the .txt file exported from MS Access, some fields are separated by comma only (eg: &quot;index&quot;&quot;field1&quot;,,,,&quot;field5&quot;...) and it looks like the empty field are not properly handled in the script.

How can I fix the script so that it import the data correctly (to handle case of empty data field?

And how to tell mysql to not insert into new row until newline?

Thank you.

 

[phn737]

I think changing the load line to:

$result = mysql_db_query($sql_db,&quot;LOAD DATA LOCAL INFILE 'e:/htdocs/db_upload/export_file_name.txt' INTO TABLE imgprodtab2 FIELDS TERMINATED BY ',' ENCLOSED BY '\&quot;' LINES TERMINATED BY '\r\n'&quot or die (mysql_error());

To handle the empty string '', will work okay, is this correct? It seems to working correctly, correct number of records are being inserted.