looking for suggestions

bliss322

i have a VERY large (55mb) .tab file that i need to parse, analyze and insert into mysql.

i have the file sitting on the server, so no upload worries; however, just parsing the file hangs the server, and i haven't even tried analyzing yet

so, at this point, i'm looking for suggestions on working with this data. is it possible to split up the file into smaller bits? are there other options that i might not know about?


function importfile($fileName,$explodeDelimiter)
	{
		$lines = file($fileName);

	foreach ($lines as $line_num => $line) {

		$lineArr = explode($explodeDelimiter,$line);

			foreach($lineArr as $newString_num =>$newString){
				//do the analyzing and inserting
			}


	}

}

bubblenut

Well, for a start, don't use [man]file()[/man], it loads the entire file into memory and is probably one of the reasons it's jamming up your server. Use [man]fopen()[/man] and [man]fgets()[/man] so that you only have as much as you're currently using in memory. Now to the mysql side of things, you don't want to do a seperate insert for each element because that would take a long long time. What would be better is maybe build longer insert statement with 20 or 30 records in and upload them in chunks like that, alternatively format how the database will be expecting it, write it to file and then import the file into MySQL in one chunk.

bliss322

Originally posted by bubblenut
Well, for a start, don't use [man]file()[/man], it loads the entire file into memory and is probably one of the reasons it's jamming up your server. Use [man]fopen()[/man] and [man]fgets()[/man] so that you only have as much as you're currently using in memory.

thanks for that..

Now to the mysql side of things, you don't want to do a seperate insert for each element because that would take a long long time. What would be better is maybe build longer insert statement with 20 or 30 records in and upload them in chunks like that, [/QUOTE.

?? multiple inserts ?? i've never done that. must go read manual

bubblenut

INSERT INTO tabl (var1, var2) VALUES ('data1', 'data2'), ('data3', 'data4'), ('data5', 'data6');
See?