Importing MYOB tab delimited files into MySQL

So, you're wanting to import some MYOB tab-delimited files into your MySQL database, possibly to mess around with on your drupal site? Give this a try.


<?php
/**
* These 2 lines put drupal's full api at your disposal.
*/
require_once './includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);

/**
* Strings everything together, call this function.
*/
function myob_mysql_action() {
// define the location of the file(s) you want to import & table names
// note: remember drupal runs all php from root directory, regardless of where your module resides
$locations[] = array(
'path' => '../private/myob/items.dat',
'table' => 'myob_items',
);
$locations[] = array(
'path' => '../private/myob/services.dat',
'table' => 'myob_ervices',
);
$locations[] = array(
'path' => '../private/myob/customers.dat',
'table' => 'myob_customers',
);

foreach ($locations as $file) {
// file_get_contents is for testing some permissions hitch
if (file_exists($file['path']) || file_get_contents($file['path'])) {
if (!myob_mysql_tables_check($file['table'])) {
// if a table doesn't exists for current file create one
myob_mysql_tables_create($file['path'], $file['table']);
}
// add data to DB
myob_mysql_tables_insert($file['path'], $file['table']);
}
else {
// couldn't lock the file
watchdog('accounts', 'Couldnt lock file: '. $file['file']);
}
}
}

/**
* This is a helper function that myob_mysql_tables_create uses to
* prevent duplicate field names.
*
* There are similar core php functions, but none that fufilled my
* requirements.
*/
function array_not_unique($raw_array) {
$dupes = array();
natcasesort($raw_array);
reset ($raw_array);

$old_key = NULL;
$old_value = NULL;
foreach ($raw_array as $key => $value) {
if ($value === NULL) { continue; }
if ($old_value == $value) {
$dupes[$old_key] = $old_value;
$dupes[$key] = $value;
}
$old_value = $value;
$old_key = $key;
}
return $dupes;
}

/**
* This function checks to see if a table exists for the file you are
* importing
*/
function myob_mysql_tables_check($table) {
$table_check = db_result(db_query("SHOW TABLES LIKE '%s';", $table));
return ($table_check) ? TRUE : FALSE;
}

/*
* This function creates a new table if one doesn't already exist
* for the file you're importing
*/
function myob_mysql_tables_create($path, $table) {
// load file contents into a variable
$string = file_get_contents($path);
// grabs the first line only (field headers are on the first line)
// note, r is osx line break. change to n for unix or rn for doze
$i = strpos($string, "r");
$string = substr($string, 0, $i);
// explodes tabs into a new array, runs some name-cleaning stuff
$field = explode("t", $string);
foreach ($field as $key => $f) {
$field[$key] = strtolower(ereg_replace("[^A-Za-z0-9]", "", $f));
}
// renames duplicate fields with an underscore
$dupes = array_not_unique($field);
$i = 0;
foreach ($dupes as $key => $f) {
$field[$key] = $field[$key] .'_'. $i;
$i++;
}

// begins building the sql query, makes all fields 200 character VARCHAR
$sql = 'CREATE TABLE `%s` (
`id` INT NOT NULL AUTO_INCREMENT, ' . "n";

foreach ($field as $key => $f) {
if ($key != 0) {
$sql .= '`'. $f ."` VARCHAR( 200 ) NOT NULL,n";
}
}
$sql .= 'PRIMARY KEY (`id`)
)';
// executes sql
db_query($sql, $table);
}

/**
* This function inserts the tab delimited file into the DB
*/
function myob_mysql_tables_insert($path, $table) {
db_query("TRUNCATE TABLE `%s`;", $table);
// note, r is osx line break. change to n for unix or rn for doze
$sql = "LOAD DATA LOCAL INFILE '%s' INTO TABLE `%s` FIELDS TERMINATED BY ' . "t" . ' LINES TERMINATED BY ". '"r"'
db_query($sql, $path, $table);
}

// call the glue function to actually run the script
myob_mysql_action();

?>