PHPBuilder - myCSV-dump for PHP



RSS Twitter
Snippets Databases

myCSV-dump for PHP

by: Thomas
|
June 16, 2001

Version: 1.0

Type: Full Script

Category: Databases

License: GNU General Public License

Description: "myCSV-dump" converts a simple CSV-flatfile-database into an MySQLdump, so that you can import it easily into your MySQL-database. Great timesaver when upgrading from flatfile to SQL-databases!



<html>
<head>
<title>MyCSV-Dump for PHP</title>
</head>
<body bgcolor=ffffff>

<?
/* script configuration */
$dbname= "test"; //  define database name
$datetime = strftime('%d%m%Y'); // dateformat
$filename_input = "flatfile.txt"; // filename of input CSV file
$filename_output = $dbname . "_" . $datetime . ".sql"; // sample output filename: "test_16062001.sql"
$debug_input = "no"; // yes = show file input
$debug_output = "yes"; // yes = show generated SQLquery
$seperator = "|"; // symbol used as CSV field seperator

/* output html page header */
$htmlheader = "<pre>";
$htmlheader .= "# MyCSV-Dump for PHP 1.0<BR>";
$htmlheader .= "# by Thomas Frhlich (thomas.froehlich@uibk.ac.at)<BR>";
$htmlheader .= "# ------------------------------------------------<BR>";
$htmlheader .= "# Dumping data for table '" . $dbname . "' from file '" . $filename_input . "'<BR>";
$htmlheader .= "</pre><PRE>";
echo $htmlheader;

/* read file */
$fcontents = file ($filename_input);

/* process each single line */
while (list ($line_num, $line) = each ($fcontents)) {
    
        /* debug info (input) */
        if ($debug_input == 'yes') {
        	 echo "<br><b>Line $line_num:</b> " . htmlspecialchars($line) . "<br>";
	} 

	/* seperate line into array */
        $without_space = trim (chop ($line));
        $ln = explode ($seperator, $without_space);

	/* construct SQL query (max 30 entries - add more if you wish) */
	$sqlquery = 'INSERT INTO ' . $dbname . ' VALUES ('; // INSERT INTO test VALUES("1","0","abc");
	if ($ln[0]) { $sqlquery .= '"' . $ln[0] . '"'; }
	if ($ln[1]) { $sqlquery .= ',"' . $ln[1] . '"'; }
	if ($ln[2]) { $sqlquery .= ',"' . $ln[2] . '"'; }
	if ($ln[3]) { $sqlquery .= ',"' . $ln[3] . '"'; }
	if ($ln[4]) { $sqlquery .= ',"' . $ln[4] . '"'; }
	if ($ln[5]) { $sqlquery .= ',"' . $ln[5] . '"'; }
	if ($ln[6]) { $sqlquery .= '"' . $ln[6] . '"'; }
	if ($ln[7]) { $sqlquery .= ',"' . $ln[7] . '"'; }
	if ($ln[8]) { $sqlquery .= ',"' . $ln[8] . '"'; }
	if ($ln[9]) { $sqlquery .= ',"' . $ln[9] . '"'; }
	if ($ln[10]) { $sqlquery .= ',"' . $ln[10] . '"'; }
	if ($ln[11]) { $sqlquery .= ',"' . $ln[11] . '"'; }
	if ($ln[12]) { $sqlquery .= ',"' . $ln[12] . '"'; }
	if ($ln[13]) { $sqlquery .= ',"' . $ln[13] . '"'; }
	if ($ln[14]) { $sqlquery .= ',"' . $ln[14] . '"'; }
	if ($ln[15]) { $sqlquery .= ',"' . $ln[15] . '"'; }
	if ($ln[16]) { $sqlquery .= ',"' . $ln[16] . '"'; }
	if ($ln[17]) { $sqlquery .= ',"' . $ln[17] . '"'; }
	if ($ln[18]) { $sqlquery .= ',"' . $ln[18] . '"'; }
	if ($ln[19]) { $sqlquery .= ',"' . $ln[19] . '"'; }
	if ($ln[20]) { $sqlquery .= ',"' . $ln[20] . '"'; }
	if ($ln[21]) { $sqlquery .= ',"' . $ln[21] . '"'; }
	if ($ln[22]) { $sqlquery .= ',"' . $ln[22] . '"'; }
	if ($ln[23]) { $sqlquery .= ',"' . $ln[23] . '"'; }
	if ($ln[24]) { $sqlquery .= ',"' . $ln[24] . '"'; }
	if ($ln[25]) { $sqlquery .= ',"' . $ln[25] . '"'; }
	if ($ln[26]) { $sqlquery .= ',"' . $ln[26] . '"'; }
	if ($ln[27]) { $sqlquery .= ',"' . $ln[27] . '"'; }
	if ($ln[28]) { $sqlquery .= ',"' . $ln[28] . '"'; }
	if ($ln[29]) { $sqlquery .= ',"' . $ln[29] . '"'; }
	$sqlquery .= ');';
	$sqlquery .= "\n"; // new line
		
	/* debug info (output) */
        if ($debug_output == 'yes') {
		echo $sqlquery;
	}

	/* save SQL query to file (append) */
	if(file_exists($filename_output)) { // if file exists, only append sql query
		$save = fopen ($filename_output, "a");
		fputs($save, $sqlquery);
		flock($save,3);
		fclose($save);
		$save_status = "append";
	}
	else { // if file does not exist, write a txt header first
		$sqlheader = "# MyCSV-Dump for PHP 1.0\n";
		$sqlheader .= "# written by thomas.froehlich@uibk.ac.at\n";
		$sqlheader .= "# --------------------------------------\n\n\n\n";
		$sqlheader .= "#\n";
		$sqlheader .= "# Dumping data for table '" . $dbname . "'\n";
		$sqlheader .= "#\n\n\n";

		$save = fopen ($filename_output, "a");
		fputs($save, $sqlheader . $sqlquery);
		flock($save,3);
		fclose($save);
		$save_status = "newfile";
	}
	
}

echo "</PRE>";
/* output success information */
if ($save_status == "append") {
	echo "<font color=green><B>DONE:</B> Data has been appended to existing file: <B>" . $filename_output . "</B>";
}
if ($save_status == "newfile") {
	echo "<font color=green><B>DONE:</B> Data has been written to new file: <B>" . $filename_output . "</B>";
}
?>

</body>
</html>

Comment and Contribute

Your comment has been submitted and is pending approval.

Author:
Thomas

Comment:



Comment:

(Maximum characters: 1200). You have characters left.