Jump to content

Reformatting CSV cell


RobertAmsterdam
Go to solution Solved by Psycho,

Recommended Posts

Ok! This fixed the problem, cool!

 

Final output is now sorted by names  aplphabetically!

 

The heard row are gone however:

 

 

Could you have a look? I thought this issue was take care of within the body of formatDate function?

$inputfile  = 'input.csv';
$outputFile = 'output.csv';


$nameColumn = 1;
$BDColumn = 8;


function formatName($name) {
    $nameArray = explode(' ', $name);
   
    $lastName = array_pop($nameArray);
  
    $name = $lastName . (count($nameArray) ? ', ' : '' ) . implode(' ', $nameArray);
    
    return $name;;
}


function formatDate($date)
{
        
        $timestamp = strtotime($date);
        //if there is timestamp in a row, do data bd conversion else it is header field
        if($timestamp) {
         $date = date('Ymd', $timestamp);
        }
    
    
   return $date;
}


$inputHandle = fopen($inputfile, "r");
$outputHandle = fopen($outputFile, 'w');


//Temporary array to hold processed data
$tempData = array();
//Flag to not process the header data
$headerRow = true;


while (($dataRow = fgetcsv($inputHandle, 1000, ",")) !== FALSE)
{
  
    //Do not process the first row of data
    if($headerRow)
    {
        //Set flag so additional rows will be processed
        $headerRow = true;


        //Modify the name
        $dataRow[$nameColumn] = formatName($dataRow[$nameColumn]);
        //print("<pre>".print_r($dataRow[$nameColumn], true)."</pre>");
        //Modify the date
        $dataRow[$BDColumn] = formatDate($dataRow[$BDColumn]);
       
        $tempData[] = $dataRow;
    }
}


usort($tempData, 'sortMyArray');  

sortMyArray($tempData[$nameColumn], SORT_REGULAR);

function sortMyArray($a, $b)
{
  $nameColumn = 1;
  return strcmp($a[$nameColumn], $b[$nameColumn]);
  
}

foreach ($tempData as  $dataRow)
{
    
    fputcsv($outputHandle, $dataRow);
}


////Close the files
fclose($inputHandle);
fclose($outputHandle);
Link to comment
Share on other sites

what actually happend is that sort(), moved this header row somewhere to the middle of the CSV file.

 

Yeah, good catch. If that is the case, then I would propose the following.

 

Keep the $header flag variable and make the initial value false. Then, in the loop, if $header = FALSE, set $header to the current row (i.e. the actual header row content). Then process all the other records as you normally do and put their values into $tempData. Then, after all the processing is complete, sort $tempData. Finally, when you write the data to the output file, first write $header then loop over $tempData writing all those rows.

Link to comment
Share on other sites

You mean like this?

 

 

$inputfile  = 'input.csv';
$outputFile = 'output.csv';

$nameColumn = 1;
$BDColumn = 8;
function formatName($name) {
    $nameArray = explode(' ', $name);
   
    $lastName = array_pop($nameArray);
  
    $name = $lastName . (count($nameArray) ? ', ' : '' ) . implode(' ', $nameArray);
    
    return $name;;
}
function formatDate($date)
{
        
        $timestamp = strtotime($date);
        //if there is timestamp in a row, do data bd conversion else it is header field
        if($timestamp) {
         $date = date('Ymd', $timestamp);
        }
    
    
   return $date;
}

$inputHandle = fopen($inputfile, "r");
$outputHandle = fopen($outputFile, 'w');

//Temporary array to hold processed data
$tempData = array();
//Flag to not process the header data
$headerRow = FALSE; // - setting to false

while (($dataRow = fgetcsv($inputHandle, 1000, ",")) !== FALSE)
{
  
    //Do not process the first row of data
    if($headerRow == FALSE)
    {
        //Set flag so additional rows will be processed
        $headerRow = $dataRow;                         // - getting the first raw
     
        $dataRow[$nameColumn] = formatName($dataRow[$nameColumn]);
        
        $dataRow[$BDColumn] = formatDate($dataRow[$BDColumn]);
       
        $tempData[] = $dataRow;
    }
}

usort($tempData, 'sortMyArray');  

sortMyArray($tempData[$nameColumn], SORT_REGULAR);

function sortMyArray($a, $b)
{
  $nameColumn = 1;
  return strcmp($a[$nameColumn], $b[$nameColumn]);
  
}

fputcsv($outputHandle, $headerRow);  // header first


foreach ($tempData as  $dataRow)
{
    
    fputcsv($outputHandle, $dataRow);
}

fclose($inputHandle);
fclose($outputHandle);

I get Undefined offset: 1 on sortMyArray($tempData[$nameColumn], SORT_REGULAR); line

 

What does that mean?

Link to comment
Share on other sites

  • Solution

Where did this line come from?

 

sortMyArray($tempData[$nameColumn], SORT_REGULAR);

 

You already have the usort() function with is calling the sortMyArray() function, so why are you calling it separately? It wouldn't even do anything in that context.

 

Plus, the loop is messed up now since it will only process data if $headerRow = false! Instead, you need to put a test at the start of the code within the loop to test if $headerRow equals false or not. If it does, then it should set $headerRow to the contents of the current row of data being processed and then "continue" with the loop. Then put all the processing logic AFTER that if() condition and code block.

 

if($headerRow == FALSE)
{
    $headerRow = $dataRow;
    continue; //Start next iteration of the loop
}
 
//Code to process normal $dataRow records continues here

 

Then, after the while() loop completes you should have two variables:

$headerRow: Contains the unprocessed array from the header row

$tempData: The array of all the processed data records

 

Then, go ahead and sort the $tempData as you need it. Lastly, write the $headerRow to the output file and then run a loop over the $tempData to write all the processes/sorted data to the output file.

Link to comment
Share on other sites

ok if this is what you mean:

while (($dataRow = fgetcsv($inputHandle, 1000, ",")) !== FALSE)
{
        
    if($headerRow == FALSE)
    {
        $headerRow = $dataRow;
        continue; //Start next iteration of the loop
    }
    print("<pre>".print_r($headerRow, true)."</pre>"); // now the header row is ok, contains all good fields
    {
        //Set flag so additional rows will be processed
        $headerRow = TRUE;
        //Modify the name
        $dataRow[$nameColumn] = formatName($dataRow[$nameColumn]);
        //Modify the date
        $dataRow[$BDColumn] = formatDate($dataRow[$BDColumn]);
        //put modified data in array
        $tempData[] = $dataRow;
    }
   //print("<pre>".print_r($headerRow, true)."</pre>"); // but here output is 1...
}

at the end it has 1, what would overwrite it?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.