[RESOLVED] Issue with scripte (ARRAY)

[spiritssight]

Hello All,

I am getting this error:

Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 71908481 bytes) in /home/httpd/vhosts/example.org/httpdocs/converter/file_reader.php on line 6

// SUCK FILE INTO ARRAY
    $rows = file('/home/httpd/vhosts/seas-network.org/httpdocs/uploads/EO1.LST') or die("Could not read file contents");
    foreach( $rows as $i => $row ) {
        print_r($row);
        echo "line length: ".strlen($row)."<br/>";

   // PARSE EACH LINE
   $data = array();
   $data[0] = substr( $row, 0,9);
   $data[1] = substr( $row, 9,70); 

Thanks for your help ahead of time, if there is a way around this issue please tell me as one of the files is 208mb is size and these files would be uploaded to the system every month

Sincerely,
Christopher
SEAS-Net

[bradgrafelman]

Looks like you need to increase the amount of memory PHP can consume at a time.

Try using [man]ini_set/man to change the memory_limit directive. If you know the size of the files is a (nearly) constant 208mb, then try setting the limit to 210M or 215M. Otherwise, you could dynamically adjust the memory by using [man]filesize/man before loading the file.

[spiritssight]

Hello and thanks for responing,

I was just told that there is a better way of taking the data from the file and uploading that data to the database table, he would not tell me as it would be a payed service and I don't have money as its for a non-profit. He said to take one line by one line and have it inserted in the database, how would I do this for a file that has tons of records and keep track, I don't care if it was to delete the record from the flat file after it was entered as this would make life easer for the server space

Sincerely,
Christopher
SEAS-Net

[bradgrafelman]

Well, if you wanted to process the file line by line, you could use [man]fopen[/man] and [man]fread/man in a while() loop. This would, however, require you to execute a query for each row inserted - that's quite a bit of overhead!

If the file is in a standard format, you might want to look into using the LOAD DATA INFILE syntax to let the MySQL server handle the import.

[spiritssight]

What do you mean by a standard format? do you mean like this:

0,9) <-- starts at 0 and has 9 char EIN
9,70); <-- starts at 9 and has 70 char Org name
etc etc

[Weedpacket]

Well, the sort that's recognised by LOAD_DATA_INFILE (CSV, roughly speaking).

If it's not (and why not, since they exist and not using them causes hassles?) you could write a conversion script that generates a temporary file. Read one line, parse it, write the corresponding CSV line ([man]fputcsv[/man]) to the temporary file. When you're done you've got something that LOAD_DATA_INFILE can use.

[spiritssight]

but would that be the same as reading the file line by line and puting it into the database?

if not what would be the easer way and fastest way to do this?

Sincerely,
Christopher

[bradgrafelman]

Well, you could still use LOAD DATA INFILE if you know for sure that the string lengths are constant...

LOAD DATA INFILE 'myfile.txt'
   INTO TABLE myTable
   (@var1)
   SET col1 = SUBSTRING(@var1,0,9), col2 = SUBSTRING(@var1,9,70)

[spiritssight]

Hello there and thanks for your help greatly,

Yes the file is very constant in the layout as its been being done this way for 10yrs I was told today by the guy that does it at the IRS.

where you have myfile.txt would I put the location like this: '/IRS_EOML_FILES/EO1.LST' or with out the other / in front

Also the col1, col2 is this where I would have the names of the feilds?
What does SUBSTRING (@VAR1,0,9) do? I know the 0 is the start of the col for that feild is the 9 how many char there is in the feild if so great?

Also one more thing can the @var1 be changed and if so how do i know which to pick for the feild that being worked on.

I await you respone and then will work on this!

Sincerely,
Christopher

[bradgrafelman]

spiritssight[where you have myfile.txt would I put the location like this: '/IRS_EOML_FILES/EO1.LST' or with out the other / in front[/quote wrote:

You should probably use the full path, e.g. /home/httpd/vhosts/.../EO1.LST .

spiritssight wrote:

Also the col1, col2 is this where I would have the names of the feilds?

Yes - I used those as abbreviations for column #1 and column #2.

spiritssight wrote:

What does SUBSTRING (@VAR1,0,9) do? I know the 0 is the start of the col for that feild is the 9 how many char there is in the feild

Yes, it works just like the PHP function [man]substr/man.

spiritssight wrote:

Also one more thing can the @var1 be changed and if so how do i know which to pick for the feild that being worked on.

@var1 is just a variable in the query; it can be called whatever you want - it doesn't affect where the data is stored or anything like that.

[spiritssight]

Here is the code, what do you think about it, is there any thing that I should or could change to make it run better and also shorten it?

Thanks again for your help!

Sincerely,
Christopher

<?php

// Server Name, also can put port number like localhost.3306
$db_hostname = 'localhost1';

// Database name to logon to
$db_name = 'seasnet_db';

// Database Username to use to login
$db_username = 'user_write';

// Database Password to use to login
$db_password = '****';

$tbl_users = 'users';
$tbl_irs_eoml = 'IRS_EOML';

// include 'config.php';
// require_once( "config.php" );

// used to connect to db server and select db to use
 include 'db_open.php';

// Connect to the database server
$db_conn = mysql_connect($db_hostname, $db_username, $db_password) or die ('Error connecting to mysql');

// Picks the database to use
mysql_select_db($db_name) or die ( "Unable to select database!" );


// INSERT the data into the database table
	$query = "LOAD DATA INFILE '/home/httpd/vhosts/example.org/httpdocs/uploads/EO.LST'
   INTO TABLE IRS_EOML
   (@var1)

   SET
	col0 = substr(@var1,0,9);
	col1 = substr(@var1,9,70);
	col2 = substr(@var1,79,35);
	col3 = substr(@var1,114,35);
	col4 = substr(@var1,149,22);
	col5 = substr(@var1,171,2);
	col6 = substr(@var1,173,10);
	col7 = substr(@var1,183,4);
	col8 = substr(@var1,187,2);
	col9 = substr(@var1,189,1);
	col10 = substr(@var1,190,4);
	col11 = substr(@var1,194,6);
	col12 = substr(@var1,200,1);
	col13 = substr(@var1,201,2);
	col14 = substr(@var1,203,9);
	col15 = substr(@var1,212,1);
	col16 = substr(@var1,213,2);
	col17 = substr(@var1,215,6);
	col18 = substr(@var1,221,6);
	col19 = substr(@var1,227,1);
	col20 = substr(@var1,228,1);
	col21 = substr(@var1,229,3);
	col22 = substr(@var1,232,3);
	col23 = substr(@var1,235,2);
	col24 = substr(@var1,237,13);
	col25 = substr(@var1,250,13);
	col26 = substr(@var1,263,1);
	col27 = substr(@var1,264,13);
	col28 = substr(@var1,277,1);
	col29 = substr(@var1,278,4);
	col30 = substr(@var1,282,35);
	col31 = substr(@var1,317,1);

$result = mysql_query($query);
	if(!$result) die(mysql_error());
	else { $affected++; }
	}

printf("File inserted: %d records\n", $affected);

// closes the mysql database connection
mysql_close($db_conn);

?>

[bradgrafelman]

Well first of all, column names in the SET list should be separated by a comma, not a semicolon; like in PHP, a semicolon indicates the end of a query. Also, you forgot to close the query string with an ending double quote.

[spiritssight]

Hello,

Like this?

<?php

// Server Name, also can put port number like localhost.3306
$db_hostname = 'localhost1';

// Database name to logon to
$db_name = 'seasnet_db';

// Database Username to use to login
$db_username = 'user_write';

// Database Password to use to login
$db_password = '****';

$tbl_users = 'users';
$tbl_irs_eoml = 'IRS_EOML';

// include 'config.php';
// require_once( "config.php" );

// used to connect to db server and select db to use
 include 'db_open.php';

// Connect to the database server
$db_conn = mysql_connect($db_hostname, $db_username, $db_password) or die ('Error connecting to mysql');

// Picks the database to use
mysql_select_db($db_name) or die ( "Unable to select database!" );


// INSERT the data into the database table
	$query = "LOAD DATA INFILE '/home/httpd/vhosts/example.org/httpdocs/uploads/EO.LST'
   INTO TABLE IRS_EOML
   (@var1)

   SET
	col0 = substr(@var1,0,9),
	col1 = substr(@var1,9,70),
	col2 = substr(@var1,79,35),
	col3 = substr(@var1,114,35),
	col4 = substr(@var1,149,22),
	col5 = substr(@var1,171,2),
	col6 = substr(@var1,173,10),
	col7 = substr(@var1,183,4),
	col8 = substr(@var1,187,2),
	col9 = substr(@var1,189,1),
	col10 = substr(@var1,190,4),
	col11 = substr(@var1,194,6),
	col12 = substr(@var1,200,1),
	col13 = substr(@var1,201,2),
	col14 = substr(@var1,203,9),
	col15 = substr(@var1,212,1),
	col16 = substr(@var1,213,2),
	col17 = substr(@var1,215,6),
	col18 = substr(@var1,221,6),
	col19 = substr(@var1,227,1),
	col20 = substr(@var1,228,1),
	col21 = substr(@var1,229,3),
	col22 = substr(@var1,232,3),
	col23 = substr(@var1,235,2),
	col24 = substr(@var1,237,13),
	col25 = substr(@var1,250,13),
	col26 = substr(@var1,263,1),
	col27 = substr(@var1,264,13),
	col28 = substr(@var1,277,1),
	col29 = substr(@var1,278,4),
	col30 = substr(@var1,282,35),
	col31 = substr(@var1,317,1)"

$result = mysql_query($query);
	if(!$result) die(mysql_error());
	else { $affected++; }
	}

printf("File inserted: %d records\n", $affected);

// closes the mysql database connection
mysql_close($db_conn);

?>

Sincerely,
Christopher