[RESOLVED] Parsing CSV and importing based on particular format

[mrbaseball34]

I have the function listed below from the File Importer class by Md. Kausar Alam on PHPClasses.orgfor importing CSV or tab delimited files directly into my database table. The problem I have is that some people do not have the columns in the same order as they are in the table.

I have built a configuration utility for them to "tell" us how their file is configured (formatted). The default format of the configuration is 1|2|3|4|5|6|7 where the numbers represent the position of our database columns in reference to the columns in their file. For instance, this is our default column layout:
PartNo, AltPartNo, CondCode, Qty, Descr

If a customer has a file formatted like this: PartNo, Descr, Qty, Other,
CondCode, AltPartNo, Other2
Their numbering would be 1|5|4|6|3|2|7
I am allowing them to have at least 2 columns extra in their format.

How would I modify the function below to use the customer's layout for the importing of their data, basically changing the ordering of the $fieldsValues so they will be imported correctly? Is this something where I can use usort to reorder the array elements?

In the function call, the $fields array is the array of columns from the database obtained from INFORMATION_SCHEMA.COLUMNS. The $fieldsValues is the line from the CSV/TabDelimited file being imported.

public function insertDataIntoTable( $fields, $fieldsValues )
{      

    if (!strpos(strtolower($fieldsValues[0]), 'number')) {
      if(strlen(trim($fieldsValues[0])) > 0) {
        $sql = 'INSERT INTO ' .$this->tableName;
        $values = " VALUES (null, ".$this->userid.", ";
        $u = 1;
        foreach($fieldsValues as $key => $value) {
          if($u <=5){
            if ($this->separator ==',') {
              $values .= "'" .str_replace('"', '', trim($value)) ."', ";
            } else {
              $values .= "'" .trim($value) ."', ";
            }
          }
          $u++;
        }
        $query  = $sql;
        $values = substr( trim($values), 0, -1); 
        $query =  $query .' '. $values .", NOW(), '', '');";
        mysql_query( $query ) or die("<br>".$query."<br>".mysql_error());
      }
    }
}    

[Res]

I can't give any code examples, but what I ended up doing was anytime a csv file was being imported, we now have a "Map fields" form they fill out before it starts crunching. This allows them to have the csv in any format they want, and use only the fields they need.

Essentially I put all the fields of the table in one array. When the CSV file gets uploaded, I parse out the header row and get each field name. At that point instead of processing the data, the map form gets displayed. The left is their column, the right is the legal DB fields. After they submit that both the csv data and arrays to lookup what csv field corresponds to which database field are available. So when it does the final process on the csv, it looks up what fields they user is trying to import and grabs the appropriate field name based on the index.

//resulting map array formt 
$csv_field_map[$csv_col_index] = $db_field_index;

//db lookup
$db_field_lookup[] = $field_name;

//so when we want to find the csv we are
//looping over.
foreach($row as $col_index => $row_value){

   if(in_array($col_index, $csv_field_map)){

  //processing logic here and building the sql, but to
 //get the field we simply do this:
 $db_field_index = $csv_field_map[$col_index];
 $db_field_name = $db_field_lookup[$col_index];

   }

}

Sorry I can't be more descriptive with code examples, hope this puts you on the right path.

[mrbaseball34]

I finally figured it out like this:

$ar2 = array(1,6,5,3,2,4,7);

while ($index = current($ar2)) {
    $my[] = $ar1[$index-1];
    next($ar2);
}

$ar1 has the line from the customer's file. $ar2 will be filled with their ordering format as shown above. Array $my contains the data in the corrected order.

[mrbaseball34]

Duplicate post, I didn't see it when I hit the back button.

[mrbaseball34]

I parse out the header row and get each field name.

You cannot assume that there will even be a header row in the file
and we also allow upload of tab-delimited files.