Retrieve data from text file...stuck...

I have a text file (accounting information) which contains a lot of information and some of this information (numbers) I want to retreive and be able to use in calucations etc in a php file. Here is a piece of the text which shows the build up of the information I want to use:

----text file excerpt----

#IB 0 2099 -226930.95
#IB 0 2510 -81219.00
#UB 0 2641 1114.28
#UB 0 2645 6978.00
#UB -1 2890 -43750.55
#RES 0 5800 46239.60
#RES -1 5900 574.92
#RES 0 5900 6402.63

----end----
The identifier is the column containing 2099, 2510, 2641 etc. So for example in a php file a function (extract for example) will open the text file and extract the number to the right based on the identifier column. Php example

(extract(2099) + extraxt(2641)) will result in: -225816,67

Any help much appreciated.

use a database to store the information, pull the content in by the identifiers, store the values as variables... then perform the necessary actions

Do you have a database? You may want to check with your web host to see how you can go about doing this.

Code:

---

$connection = mysql_connect($host,$user,$password) or die ("couldn't connect to server");
$db = mysql_select_db($database,$connection) or die ("Couldn't select database");
$query = "create table tablename(
(col2 text,
col1 int,
col2 int,
col3 int not null
col4 decimal(15,2) not null
primary key(col3))";
$result = mysql_query($query) or die ("Couldnnt execute query.");
echo "table has been created";

---

After setting one up I would insert the info you described into the database. There are various ways of doing this depending on how your data is organized. If it is in the format above (which is not ideal) I would separate the info into a multidimensional array based first on the line it is on and then each line would be divided using the space between each col as a separator.

I just don't know how your data is sorted or what resources you are working with (ie whether you have a database and what lvl of knowledge you have in working with them).

If need be what you are asking for can be done without a database if you don't want to store the info anywhere. Either way the next part will help you to separate the data based on the line it is on. More specifically by the line terminator.

Code:

---

$data="#IB 0 2099 -226930.95
#IB 0 2510 -81219.00";
$data=pregsplit('/\n/',$data);
echo "data[1]";
\\  outputs #IB 0 2510 -81219.00

---

I am a beginner at this but need a solution wihtout mysql or other database. Just a php script to retrieve the number from a .txt-file and making me able to handle the number further in the php script...any help appreciated.

I am looking into this. I am hoping that someone can get to you before me, because I am no expert, but I do have a little bit of knowledge and there are people that are far better at it than I am.

If I can I will try to give you the code that you need. First you will need to rename the txt file with a php extension and create a string like $data="data"; where the data is all of the data in the document. I am assuming you are a little familiar with php ;). Then there will be a very small bit of php code at the end of the document that will sort the info and echo the info that you want. Part of the info that I am asking about is found in this thread.

I really do suggest that you look into setting up a MySQL database, because it can really help with storing and sorting info in an easy way.

EDIT: Getting closer, but not quite there yet. Take a look:

PHP Code:

---

<?php
$string="1 2 3
4 5 6
7 8";
$arr = array_map(create_function('$a', 'return explode(\' \', $a);'), preg_split('/\n/', $string));
print_r($arr);
echo "<br><br>";
echo $arr[0][1]+$arr[0][2];
?>

---

The output will be five as it adds values 2 and 3 from line one.

This code needs work, but at present it takes the data and will divide it based on the line it is on and then divides it by the items on each line, so it isn't done yet, but this is a good step in the right direction.

Actually the code works already. Take a look at the following:

<?php
$string="#IB 0 2099 -226930.95
#IB 0 2510 -81219.00
#UB 0 2641 1114.28
#UB 0 2645 6978.00
#UB -1 2890 -43750.55
#RES 0 5800 46239.60
#RES -1 5900 574.92
#RES 0 5900 6402.63";
$arr = array_map(create_function('$a', 'return explode(\' \', $a);'), preg_split('/\n/', $string)); <---this line was made (99&#37;) by Twey.
print_r($arr);
echo "<br><br>";
echo $arr[0][3]+$arr[2][3];
?>

The value is the answer from adding the two values mentioned above. I just think the code should be easier to use :o. Gonna try and work on it some more, because this is kind of a cool script idea.

EDIT: I have the submit form created, so that all you have to do is type in the values 2099 and 2641 and it will add the two selected values from the two lines and come out with the answer of -225816.67. Just need to tie it into the above script.

EDIT: will the number that you want to add always be the 4th item on the row and/or the last item on the row and will the identifier always be in the 3rd column? Will the identifier always be unique? This script will assume that is the case. Either way I am almost done. Just give me 15min or so to work out the bugs.

EDIT: here is the code. I'll need to know a few more things about this data you are working with so as to avoid possible wrong answers. I am having a bit of fun with this code and will try to make it operate better. For now try this:

PHP Code:

---

<STYLE TYPE="TEXT/CSS">
textarea{background-color:#a9a9a9;color:black;}
body{
background-color:tan;
}
</style>
<?php
$data=@$_POST['data'];
$one=@$_POST['one'];$one=str_replace(" ","",$one);$onearr=explode(",",$one);
$url=@$_POST['url'];

if ($url != "") 
{
$handle = file_get_contents("$url",NULL);
$data=htmlentities($handle);
}

$string="";
$arr=array_map(create_function('$a', 'return preg_split(\'[\s+]\', $a);'), preg_split('/\n/', $data));

$oops2=count($onearr);
$oops=count($arr);
$a=0;
while ($a<$oops)
{
$arr2  =$arr[$a][0];
$arr2 .=$arr[$a][1];
$arr2 .=$arr[$a][2];
array_unshift($arr[$a],$arr2);
array_unshift($arr[$a],"nerfed");
$a++;
}
?>

<form action=<?php echo $_SERVER['PHP_SELF']; ?> method="POST">
copy and place your data in here or enter the url where the data is stored<br>in the space provided at the end:<br>
             <textarea name="data" cols=55 rows=25><?php echo "$data"; ?></textarea><br>
identifiers (remember to separate identifiers with commas): <br><textarea name= "one" cols=45 rows=3><?php echo "$one"; ?></textarea><br>

url of .txt document to scan (optional): <br><input type="text" name="url" size=75 value=<?php echo @$_POST['url']; ?> >
<br><br>
<input type='submit' name="queryButton" value="Submit and Add">
</form>

<?php
$a=0;$b=0;$c=0;$d=array(0);
while ($b<$oops2) {
while ($a<$oops) { 
$ans=array_search($onearr[$b],$arr[$a]);
if ($ans==1) {$c++;echo "Result $c is located at array[$a][$ans].";array_push($d,$arr[$a][5]); echo $arr[$a][5];echo "<br>";}
$a++;
}$b++;$a=0;
}

$ans1=array_sum($d);
echo ".<br><br>If you add the values of these identifiers together you will get $ans1";
?>

---

EDIT: updated the code to make it easier to use. The above code makes certain assumptions as stated in the second edit listed above. If you need the code modified or to behave differently let me know and I'll see what I can do. :)

EDIT: Fixed a tiny bug. The script will now only return a result if the identifier selected is found in the third column as opposed to anywhere in the data.

EDIT: I noticed that the column you listed as your identifier has two numbers that are the same. How would you like the program to handle that? Add both numbers for a total of three values when there is more than one identifier with the same value? Also updated the script to handle multiple spaces.

EDIT: Fixed a minor bug. Added the ability to list the url of the text document to search (with thanks to thetestingsite and this thread). Added some style to make it easier on the eyes. Made it cross browser compatible.

P.S. I strongly encourage you to get acquainted with MySQL. I have found it to be much simpler than PHP. MySQL is nothing more than a glorified spreadsheet. Working around MySQL will make coding much more difficult. For example I can have 50 pages that are very similar. In 15 seconds or much less I can update all 50 pages. It's a lifesaver really.

Wow! You surely took some time to do this and it works very well. Major thanks!

I have however come across a small problem. As you can see on the sample data there are sometimes two values for an identifier. They are further more preceded by -1 or 0 in front of them. The minus one is for previous year and the 0 for current year...somehow this needs to be taken into consideration in the script as well as the first column...so each number has to be identified by:

1. Either #IB, #UB or #RES
2. -1 or 0
3. the four digit number

I guess I made it a bit more complicated now? Trying to understand you code and will give it a try myself.

Thanks

Kev

I think I can do what you are asking, but I am not all that sure what you are asking ;) Can you give me a detailed explanation of what the program is supposed to do? For example, what do you want to type in that will identify the particular number that you are looking for? How many figures will you be adding together? Willl there be other calculations, if so, how will they be performed. Any other details besides the question I just asked would help me a lot. Especially the ones I asked you in my earlier posts.

I had a lot of fun with it and learned lots along the way as I created the script :) I do hope you read my posts above as they have some links to useful threads and also important questions I notice you have not answered yet.

I agree with you a database would be more natural but I prefer to avoid this. In the end I want to use the numbers extracted from the text file in any way I want (subtract, multiply etc...not limited to just two). So a function that will get the number based on these three identifiers. Based on the example text file extract below, function(#UB,0,2640) would return the number 15256.00. So further: function(#RES,0,3301) + function(#IB,0,1689) would return: -519165.5

#IB 0 1689 1698.00
#IB 0 1930 267692.02
#UB -1 2510 -81219.00
#UB 0 2510 -81219.00
#UB 0 2610 -130530.34
#UB 0 2615 -6978.00
#UB -1 2640 495.37
#UB 0 2640 15256.00
#UB 0 2641 1114.28
#RES 0 3301 -520863.50
#RES -1 3306 -371230.50
----

Here is a codesnippet I found that seems to be a beginning to something I need...

Code:

---

<?PHP


$file_handle = fopen("sie.txt", "rb");

while (!feof($file_handle) ) {

$line_of_text = fgets($file_handle);
$parts = explode('\t', $line_of_text);

print $parts[0] . $parts[1]. "<BR>";
}

fclose($file_handle);

?>

---

kk, I think I have a better understanding now of what you need. I may be a bit slower getting it updated, because I have three tests this week that I am in the middle of and have been running low on sleep. Who all will be using this program? This will help me to account for possible typos and errors.

I am no expert, but isn't the code snippet you just posted do the same thing as my script? ie type in the url of the text document and viola, you have the contents of the document in front of you to manipulate. Just out of curiosity, how large are these text documents that we are talking about?

EDIT: updated the above script to search based on col1 - col3.

The solution james438 proffered will work, but is far from optimal. It is very memory-intensive if you have a lot of data, since it loads it all into memory at once. I strongly agree with the opinions expressed thus far about how much easier and more efficient this would be with a proper database. However, here is a somewhat better way of doing it using a buffer:

Code:

---

define('BUF_SIZ', 128);

define('TYPE_COLUMN', 0);
define('YEAR_COLUMN', 1);
define('ID_COLUMN', 2);
define('VALUE_COLUMN', 3);

function select_from_file($get, $where, $val, $multiple = false) {
  $col %= 4;
  $r = $multiple ? array() : '';
  $f = open('yourfile.dat', 'r');

  for($current_line = '', $next_line = '', $pos; !feof($file);) {
    while(($pos = strpos($current_line, "\n")) === false)
      $next_line .= fread($f, BUF_SIZ);
    $next_line = str_replace("\r", '', $next_line);
    list($current_line, $next_line) = explode("\n", $next_line, 2);
    if($current_line) {
      $current_line = explode(' ', $current_line);
      if($current_line[$where] == $val)
        if($multiple)
          $r[] = $current_line[$get];
        else {
          $r = $current_line[$get];
          break 2;
        }
    }
  }
  fclose($f);
  return $r;
}

---

E.G.:

Code:

---

select_from_file(VALUE_COLUMN, ID_COLUMN, 2099) + select_from_file(VALUE_COLUMN, ID_COLUMN, 2641)

---

is -225816.67. You can also use it for other columns; select_from_file(TYPE_COLUMN, VALUE_COLUMN, '-226930.95') will return '#IB', and select_from_file(VALUE_COLUMN, TYPE_COLUMN, '#IB', true) will return an array of values from rows that begin with #IB (if you don't pass the $multiple argument, only the first one will be selected).

Untested.

Take your time! I am very thankful for the help so far and don't feel pressure to deliver. I am the one who will be using this for various data.

I updated the script one last time to allow for unlimited identifiers, but I don't plan on doing any more work with it. There is plenty there in the script for you to learn from.

I really don't like the script much any more despite all I have learned from it in the process. This was good practice for me too. The code I wrote is too bulky. It is taxing on the server to say the least and in my experience these types of programs are just not as secure as databases. MySQL can do the same thing in one short line that is far easier to understand. The single MySQL line that Twey showed you above is all you really need.

Lastly, and most importantly, this script does not teach good coding practices, so listen to Twey.

Quote:

---

The single MySQL line that Twey showed you above is all you really need.

---

What MySQL line? That was a call to my function :p

But yes, in MySQL it's as simple as:

Code:

---

list($value) = mysql_fetch_array(mysql_query('SELECT value FROM yourtable WHERE id = 2099;'), MYSQL_NUM);

---

James superthanks for your help. Very greatful! I might consider mysql but will wait...it is a bit too much for me right now.

Twey, here is my test code but it doesn't return anything...

Code:

---

<?PHP


define('BUF_SIZ', 128);

define('TYPE_COLUMN', 0);
define('YEAR_COLUMN', 1);
define('ID_COLUMN', 2);
define('VALUE_COLUMN', 3);

function select_from_file($get, $where, $val, $multiple = false) {
  $col &#37;= 4;
  $r = $multiple ? array() : '';
  $f = open('file.dat', 'r');

  for($current_line = '', $next_line = '', $pos; !feof($file);) {
    while(($pos = strpos($current_line, "\n")) === false)
      $next_line .= fread($f, BUF_SIZ);
    $next_line = str_replace("\r", '', $next_line);
    list($current_line, $next_line) = explode("\n", $next_line, 2);
    if($current_line) {
      $current_line = explode(' ', $current_line);
      if($current_line[$where] == $val)
        if($multiple)
          $r[] = $current_line[$get];
        else {
          $r = $current_line[$get];
          break 2;
        }
    }
  }
  fclose($f);
  return $r;
}


select_from_file('#IB', '0', '2099');

?>

---

You've used it wrong. It takes three arguments, in the order of an SQL query: the column to get, the column to check, and the value that the checked column must have in order to match. The first two arguments should be *_COLUMN constants as defined at the top of the file. You'll also probably need to change 'file.dat' in the function to the name of your text file.

Got this piece of code from another forum. Any comments in it? (memory perspective etc):

Code:

---

<?PHP


$contents = file('file.txt');

foreach($contents as $line_value)
{

list($cat, $int, $id, $num) = explode("\t", $line_value);

    $cat = str_replace('#', '', $cat);

    $number[$cat][$int][$id] = trim($num);
}

//echo '<pre>' . print_r($number, true) . '</pre>';


echo $number['UB'][-1][1650]*2;// returns -81219.00

// similarly $number['UB'][-1][1229]; will return 14100.00

?>

---

As with james438's code, it loads the whole file into memory at once.

Possible to modify for better memory handling?

Aa I see. There are no column constants as in categories in my file...Well I have got some working solutions to continue my journey with.

Quote:

---

Originally Posted by Twey

You've used it wrong. It takes three arguments, in the order of an SQL query: the column to get, the column to check, and the value that the checked column must have in order to match. The first two arguments should be *_COLUMN constants as defined at the top of the file. You'll also probably need to change 'file.dat' in the function to the name of your text file.

---

Quote:

---

There are no column constants as in categories in my file...

---

I meant the PHP script I wrote. E.G. to select the value from the row where the ID is 2066, you'd use:

Code:

---

select_from_file(VALUE_COLUMN, ID_COLUMN, 2066);

---

Still a no go.

Quote:

---

Originally Posted by Twey

I meant the PHP script I wrote. E.G. to select the value from the row where the ID is 2066, you'd use:

Code:

---

select_from_file(VALUE_COLUMN, ID_COLUMN, 2066);

---

---