Inserting a tab delimited text file into mySQL

natman3

I need a php page with a browse button to choose the tab delimited text file that can be inserted into mySQL table. I created a table called 2004results using myphpadmin that has the 8 fields i need. Does any one know how i might approach this issues. My tab delimited file look like this.

Bib FirstName LastName Address City State Zip FinTimeOfDay
101 Willy Pickhardt 2563 Toy Street Fayetteville AR 72704 9:29:48
102 marcus barnoskie 2116 Owens ln. lawrence KS 66046 9:45:48
103 beau theriot 2910 baxter conway AR 72034 9:44:30
104 Robert Henley 2175 Dowers Ln. Fayetteville AR 72704 9:36:25
105 Luke Williams JBU 2000 West University Str Siloam Springs AR 72761 9:40:52
106 alan anderson #3 hopkins lane Conway AR 72032 9:47:13
107 Daniel Stafford 2714 E Kantz Dr Fayetteville AR 72703 10:58:29
108 David Grundman 3 Flintsstone Dr. Conway AR 72034 9:44:30

cahva

This should get you started:

if (!empty($_POST['submit']) && $_FILES['uploadfile']['error']==0) {
	$uploadfile = file($_FILES['uploadfile']['tmp_name']);
	$uploadfile = str_replace("\r",'',$uploadfile);
	$uploadfile = str_replace("\n",'',$uploadfile);

foreach ($uploadfile as $line) {
	$tmp=explode("\t",$line);

	// Check that line has 8 values
	if (count($tmp)==8) {
		list($bib , $firstName , $lastName , $address , $city , $state , $zip , $finTimeofday) = $tmp;

		// Do some checking that the row doesnt exist allready and if not do the INSERT query

	}
}
}

Roger_Ramjet

Well, on the page you need an <input type="file" ...

This will create a text box with a browse button to select the file.

The target script can deal with the file in many ways, but the specific method for inserting a csv is the mysql command
load data infile . This is the simplest and most efficient way to do it. It is what phpmyadmin uses.

natman3

Thanks for your sugestions I will see what i can do.

bonmchu

if you only load those file for one time, i suggest you to download Navicat's trial version and load those text into MySQL.

If you are required to load those file frequently, you may not want to purchase Navicat to do so, cos you might think that it is too expensive.

However, it depends on the frequency and your usage of MySQL. If you would also like to administrator your MySQL with a GUI tool, Navicat is definitely a greate choice for you.

with Navicat, you can set a schedule to run the import of the tab delimited text file automatically. besides, there are also plenty of functions available to webmaster like us.

There is a review on Navicat by O'Reily OnLamp.com, you may see if you would like to know more about this tool:
http://www.onlamp.com/pub/a/onlamp/2004/12/22/navicat.html

Here is the official homepage of Navicat:
http://www.navicat.com