Reading A Fixed Field File and Insterting Into DB

scriderpsd

Hello Friends!

I have a fixed field length flat file with about 100 fields and several hundred records in it. What I need to do is open the file and grab each record, pull some specific fields and insert them into the database. Here are a couple of example records.

010807011207011607SUBFINDER  0000020940109070810154001090708100109071540 0007.5000 01422C003 Illness                                                                  Filled        0109070810154001090708100109071540 0007.5000 BILINGUAL K                    124       00001TF 002253705Allison              Alice                V TF 001007416Martin               Sali                   FF Mark Twain Elementary School               0000082205       1                                                                                                                                                                                                                                                                                                                                                                     0108071544
010807011207011607SUBFINDER  0000009550108070810154001080708100109071540 0007.5000 00822A005 Professional Leave            5157                                       Filled        0108070810154001080708100109071540 0007.5000 BILINGUAL 1                    125       00001TF 002253758Evans                Carolina             A TF 001005859Dunn                 Melissa              M TF Mark Twain Elementary School               0000082205       2                                                                                                                                                                                                                                                                                                                                                                     0102071321

This seems like it should be pretty simple but I get really confused since I only want certain fields. For example, I would like to pull the 6 character field with the offset of 53-58 and then the 30 character field with the offset of 87-116 and then the 9 character field with the offset of 331-339 and so on.

Any help is very much appreciated.

Thanks,

Scott

dagon

explode on new line, then explode on double space, clean out the empties should then be able to grab the data you want by array key

dagon

ok i was bored so went of and wrote it:

<?php

$data="010807011207011607SUBFINDER  0000020940109070810154001090708100109071540 0007.5000 01422C003 Illness                                                                  Filled        0109070810154001090708100109071540 0007.5000 BILINGUAL K                    124       00001TF 002253705Allison              Alice                V TF 001007416Martin               Sali                   FF Mark Twain Elementary School               0000082205       1                                                                                                                                                                                                                                                                                                                                                                     0108071544
010807011207011607SUBFINDER  0000009550108070810154001080708100109071540 0007.5000 00822A005 Professional Leave            5157                                       Filled        0108070810154001080708100109071540 0007.5000 BILINGUAL 1                    125       00001TF 002253758Evans                Carolina             A TF 001005859Dunn                 Melissa              M TF Mark Twain Elementary School               0000082205       2        ";

//you will want to read the file in to a string file_get_contents()

$first=explode("\n",$data);


function clean_input(&$data){
return ereg_replace (' +', ' ', trim($data));
}


foreach ($first as $var){
	$sec=explode('  ', $var);

foreach ($sec as $key => $value){
	if ($value == '') {
		unset($sec[$key]);
	} 
}
$sec=array_map('clean_input',$sec);	
$sec=array_values($sec);

//show output array
//print_r($sec); 

/* mysql insert stuff */

}
?>

NogDog

I doubt that explode() will work, as it is not a given that there is a space between any 2 given fields. You'll probably need to use [man]substr/man to extract each field (remembering that the first character of each line will be number 0, not 1).

dagon

well my code above works, as far as i understand the question.

NogDog

dagon wrote:
well my code above works, as far as i understand the question.

Since it's a "fixed field" format, that implies fields are delineated by character position in each record, not by any delimiters. Thus...

010807011207011607SUBFINDER  0000020940109070810154001090708100109071540(...etc...)

...could conceivably be broken down into the following (totally hypothetical) fields:

0108070112
07011607
SUBFINDER..   [where "." are spaces]
00000209
401090708101540
0109070810
0109071540
(...etc....)

Therefore, the individual field data will probably need to be picked out of each line positionally (e.g., via substr()).

dagon

I sit corrected :-)

scriderpsd

Thank you both for taking the time to visit this post and provide some insight. So far it has been helpful. NoDog is correct that the records are fixed field and delineated be by character position and not any type of delimiter.

Based on dagon's example above, I could use explode() on the \n but then how would I write the foreach loop to use substr()?

Thanks in advance for your help.

Scott

NogDog

I would suggest defining an array that has the start and length of each field, that you want to extract:

// just specify the fields you need:
$fields = array(
   array(0, 6),    // (start, length)
   array(6, 3),
   array(12, 22),
   array(55, 6)
);
$file = file('datafile.txt') or die('couldn't read file');
$data = array();
foreach($file as $key => $line)
{
   foreach($fields as $ix => $field)
   {
      $data[$key][$ix] = substr($line, $field[0], $field[1]);
   }
}

At this point $data should be a 2-dim array with your data, something like:

array (
  [0] => array (
    [0] => value,
    [1] => value,
    <...etc....>
  ),
  [1] => array (
    [0] => value,
    [1] => value,
    <...etc....>
  ),
  <...etc....>
)

You might want to specify the fields as an associative array to help with readability:

$fields = array(
   'field_name_1' => array(0, 6),    // (start, length)
   'field_name_2' => array(6, 3),
   // <...etc....>

scriderpsd

Very cool. That looks like exactly what I'm looking for. So then to insert my results into the database should I put my SQL statement inside the outer foreach loop?

NogDog

With a little modification, you could create a SQL insert list:

$fields = array(
   array(0, 6),    // (start, length)
   array(6, 3),
   array(12, 22),
   array(55, 6)
);
$file = file('datafile.txt') or die('could not read file');
$data = array();
foreach($file as $line)
{
   $thisInsert = array();
   foreach($fields as $field)
   {
      $thisInsert[] = substr($line, $field[0], $field[1]);
   }
   $data[] = sprintf("('%s')", implode("', '", $thisInsert));
}
$insertList = implode(", ", $data);
$sql = "INSERT INTO table_name (fld1, fld2, ...etc....) VALUES $insertList";

scriderpsd

Hi Guys, I got pulled off this task for the last month and now I'm back on...

So NogDog's solution above worked perfectly. When I do a print_r on $data I get an array that looks like this.

Array
(
    [0] => ('010907', '010907', 'Illness                 ')
    [1] => ('010807', '010907', 'Professional Leave      ')
    [2] => ('010807', '010907', 'Professional Leave      ')
    [3] => ('010807', '010807', 'Sick Leave              ')
    [4] => ('010807', '010807', 'Illness                 ')
    [5] => ('010807', '010807', 'Sick Leave              ')
    [6] => ('011007', '011007', 'Professional Leave      ')
    [7] => ('011107', '011107', 'Professional Leave      ')

.....

Now, I need to go through and be able to go through that array and change 'Illness' to '1' and 'Professional Leave' to '2' and 'Sick Leave' to '3', etc.

So my question is, now that I have this broken down into an array, how do I go in and manipulate the values in the array???

Thanks in advance.