使用php将两个csv文件合并在一起 [英] Merging two csv files together using php

问题描述

我有两组csv文件 - 一个包含合同数据，另一个包含授予的合同。我需要使用公共字段（contractName）组合两个csv文件，并计算已结束授予的合同的总金额。链接到csv文件

到目前为止，我已经设法将两个csv文件合并，它在final.csv文件，但我不能合并两个csv文件使用公共字段（contractName）。这是代码。

 <？php 
 $ nn = 0; 
 foreach（glob（*。csv）as $ filename）{
 if（（$ handle = fopen（$ filename，r））！== FALSE）{
 while （$ data = fgetcsv（$ handle，0，，））！== FALSE）{
 
 $ c = count（$ data）; 
 
 for（$ x = 0; $ x< $ c; $ x ++）
 {
 $ csvarray [$ nn] [] = $ data [$ x] 
} 
 $ nn ++; 
} 
 
 fclose（$ handle）; 
} 
 
} 
 
 $ fp = fopen（'../ final.csv'，'w'）; //这里设置的输出文件
 
 foreach（$ csvarray as $ fields）{
 fputcsv（$ fp，$ fields）; 
} 
 
 fclose（$ fp）;？> 
  

这是我的最终输出。

  contractName，contractDate，completionDate，awardee，awardeeLocation，amount 
 Contract-2070-3,5 / 9 / 14,8 / 25/14，SK Builders，Banke，200000 
 Contract-2070-5,3 / 18 / 14,4 / 8/14，S engineering industries，Makwanpur，300000 
 Contract-2070-9,3 / 6 / 14,4 / 6 / 14，Gourishankar nirman sewa，Lalitpur，400000 
 Contract-2070-10,2 / 6 / 14,6 / 16/14，SK Builders，Banke，500000 
 contractname，status， bidPurchaseDeadline，bidSubmissionDeadline，bidOpeningDate，tenderid，publicationDate，publishedIn 
 Contract-2070-1，Closed，6/12 / 14,6 / 13 / 14,6 / 13 / 14,2070 / 071 / 2,5 / 14 / 14，Nagarik Daily
 Contract-2070-2，Closed，6/10 / 14,6 / 11 / 14,6 / 11 / 14,16 / 070 / 71,5 / 12 /喜马拉雅时报
 Contract-2070-3，Current，3/8 / 14,3 / 9 / 14,3 / 9/14，DDC / Bag / Bridge / 03-070 / 71,3 / 10 / 14，Nagarik Daily
 Contract-2070-4，Current，4/23 / 14,4 / 25 / 14,4 / 25 / 14,04（2070/071），4/9 / Hetauda sandesh
 Contract-2070-5，Closed，4/23 / 14,4 / 25 / 14,4 / 26 / 14,04（2070/071），4/10/14，Hetauda sandesh 
 Contract-2070-6，Current，4/23 / 14,4 / 25 / 14,4 / 27 / 14,04（2070/071），4/11/14，Hetauda sandesh
 Contract-2070-7，Current，4/23 / 14,4 / 25 / 14,4 / 28 / 14,04（2070/071），4/12/14，Hetauda sandesh 2070-8，Current，4/23 / 14,4 / 25 / 14,4 / 29 / 14,04（2070/071），4/13/14，Hetauda sandesh
 Contract-2070-9 ，Closed，2/6 / 14,2 / 8 / 14,2 / 8 / 14,15 / 070 / 71,1 / 9/14，The Himalayan Times
 Contract-2070-10，Current， 1/14/14/15 / 14,1 / 16/14，13,2070 / 2071，1/6 / 14，The Himalayan Times
  pre> 
 
 
但最终输出应该是这样的。
 合同名称，状态，bidPurchaseDeadline，bidSubmissionDeadline，bidOpeningDate，tenderid，publicationDate，publishedIn，contractDate，completionDate，awardee，awardeeLocation，amount 
 Contract-2070-1，Closed，6/12 / 14,6 / 13 / 14,6 / 13 / 14,2070 / 071 / 2,5 / 14/14，Nagarik Daily ,,,,, 
 Contract-2070-2，Closed，6/10 / 14,6 / 11 / 14,6 / 11 / 14,16 / 070 / 71,5 / 12/14，The Himalayan Times ,,,,,, 
 Contract-2070-3，Current，3/8 / 14,3 / 9 / 14,3 / 9/14，DDC / Bag / Bridge / 03-070 / 71,3 / 10/14，Nagarik Daily，5/9 / 14,8 / 25/14，SK Builders，Banke，200000 
 Contract-2070 -4，Current，4/23 / 14,4 / 25 / 14,4 / 25 / 14,04（2070/071），4/9/14，Hetauda sandesh ,,,, 
 Contract-2070 -5，Closed，4/23 / 14,4 / 25 / 14,4 / 26 / 14,04（2070/071），4/10/14，Hetauda sandesh，3/18 / 14,4 / 8/14 ，S engineering industries，Makwanpur，300000 
 Contract-2070-6，Current，4/23 / 14,4 / 25 / 14,4 / 27 / 14,04（2070/071），4/11/14 ，Hetauda sandesh ,,,,,, 
 Contract-2070-7，Current，4/23 / 14,4 / 25 / 14,4 / 28 / 14,04（2070/071），4/12/14 ，Hetauda sandesh ,,,,,, 
 Contract-2070-8，Current，4/23 / 14,4 / 25 / 14,4 / 29 / 14,04（2070/071），4/13/14 ，Hetauda sandesh ,,,,,, 
 Contract-2070-9，Closed，2/6 / 14,2 / 8 / 14,2 / 8 / 14,15 / 070 / 71,1 / 9/14， The Himalayan Times，3/6 / 14,4 / 6/14，Gourishankar nirman sewa，Lalitpur，400000 
 Contract-2070-10，Current，1/14 / 14,1 / 15 / 14,1 / 16 / 14，13,2070/2071，1/6 / 14，The Himalayan Times，2/6 / 14,6 / 16/14，SK Builders，Banke，500000 
  
 
 
解决方案
这个问题很难，你总是可以把数据放在那些csv上，像这个解决方案：
  //第一节
 $ fh = fopen（'awards.csv'，'r' ）; 
 $ fhg = fopen（'contracts.csv'，'r'）; 
 while（（$ data = fgetcsv（$ fh，0，，））！== FALSE）{
 $ awards [] = $ data; 
} 
 while（（$ data = fgetcsv（$ fhg，0，，））！== FALSE {
 $ contracts [] = $ data; 
} 
 //第二节
 for（$ x = 0; $ x  {
 if（$ x = = 0）{
 unset（$ awards [0] [0]）; 
 $ line [$ x] = array_merge（$ contracts [0]，$ awards [0]）; // header 
} 
 else {
 $ deadlook = 0; 
 for（$ y = 0; $ y< = count（$ awards）; $ y ++）
 {
 if（$ awards [$ y] [0] == $ contracts [ $ x] [0]）{
 unset（$ awards [$ y] [0]）; 
 $ line [$ x] = array_merge（$ contracts [$ x]，$ awards [$ y]）; 
 $ deadlook = 1; 
} 
} 
 if（$ deadlook == 0）
 $ line [$ x] = $ contracts [$ x]; 
} 
} 
 // 3 section 
 $ fp = fopen（'final.csv'，'w'）; //这里设置的输出文件
 
 foreach（$ line as $ fields）{
 fputcsv（$ fp，$ fields）; 
} 
 fclose（$ fp）; 
 
很难解释代码，因为iam来自一个西班牙国家，所以我的英语不是很好...但我可以尝试
 
 
 
基本上代码有3节，
 
 
 
在第1节，
打开这两个文件并将内容放在数组$ awards []和$ contracts []中，所以$ awards [0]是awards.csv中的第一行，$ awards [1]是奖励的第二行.csv等，在$ contracts []中相同。
 
 
 
在第2节，
 
 
 
 如果 
，则第一个字在每个数组中为$ awards [x] [0]和$ contracts [x] code>，if（$ x == 0），就是做标题。 
首先，使用 unset 函数删除第一个字 contractname ，并加入$ awards [0]和$然后，使用为使用 array_merge 函数
 
 c $ c> i从$ contracts数组中选择每一行中的第一个单词，并与$ awards数组中每一行的第一个单词进行比较。 
 so， if（$ awards [$ y] [0] == $ contracts [$ x] [0]）  Contract-2070-3are ）是相同的，如果这些是相同的字符串，请删除它并合并这些行。
 
 
 
如果这些单词不一样，请保存$ lines数组中的$ contracts [x]行并继续。
 
 
 
在第3节，
保存内容从$ line数组中的文件。
 
I have two sets of csv files - one containing the contracts data and the other containing the awarded contracts. I need to combine the two csv files using the common field (contractName) and compute the total amount of closed awarded contracts.Link to the csv files

So far I have managed to merge the two csv files together and write it in final.csv file but i am not being able to merget the two csv files using the common field(contractName). Here is the code.`
<?php
$nn = 0;
foreach (glob("*.csv") as $filename) {
    if (($handle = fopen($filename, "r")) !== FALSE) {
        while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {

            $c = count($data);

            for ($x=0;$x<$c;$x++)
            {
                $csvarray[$nn][] = $data[$x];
            }
            $nn++;
        }

        fclose($handle);
    }

}

$fp = fopen('../final.csv', 'w');//output file set here

foreach ($csvarray as $fields) {
    fputcsv($fp, $fields);
}

fclose($fp);?>
This is my final output.
contractName,contractDate,completionDate,awardee,awardeeLocation,Amount
Contract-2070-3,5/9/14,8/25/14,"SK Builders",Banke,200000
Contract-2070-5,3/18/14,4/8/14,"S engineering industries",Makwanpur,300000
Contract-2070-9,3/6/14,4/6/14,"Gourishankar nirman sewa",Lalitpur,400000
Contract-2070-10,2/6/14,6/16/14,"SK Builders",Banke,500000
contractname,status,bidPurchaseDeadline,bidSubmissionDeadline,bidOpeningDate,tenderid,publicationDate,publishedIn
Contract-2070-1,Closed,6/12/14,6/13/14,6/13/14,2070/071/2,5/14/14,"Nagarik Daily"
Contract-2070-2,Closed,6/10/14,6/11/14,6/11/14,16/070/71,5/12/14,"The Himalayan Times"
Contract-2070-3,Current,3/8/14,3/9/14,3/9/14,DDC/Bag/Bridge/03-070/71,3/10/14,"Nagarik Daily"
Contract-2070-4,Current,4/23/14,4/25/14,4/25/14,04(2070/071),4/9/14,"Hetauda sandesh"
Contract-2070-5,Closed,4/23/14,4/25/14,4/26/14,04(2070/071),4/10/14,"Hetauda sandesh"
Contract-2070-6,Current,4/23/14,4/25/14,4/27/14,04(2070/071),4/11/14,"Hetauda sandesh"
Contract-2070-7,Current,4/23/14,4/25/14,4/28/14,04(2070/071),4/12/14,"Hetauda sandesh"
Contract-2070-8,Current,4/23/14,4/25/14,4/29/14,04(2070/071),4/13/14,"Hetauda sandesh"
Contract-2070-9,Closed,2/6/14,2/8/14,2/8/14,15/070/71,1/9/14,"The Himalayan Times"
Contract-2070-10,Current,1/14/14,1/15/14,1/16/14,"13,2070/2071",1/6/14,"The Himalayan Times"
but the final output should be something like this.
contractname,status,bidPurchaseDeadline,bidSubmissionDeadline,bidOpeningDate,tenderid,publicationDate,publishedIn,contractDate,completionDate,awardee,awardeeLocation,Amount 
Contract-2070-1,Closed,6/12/14,6/13/14,6/13/14,2070/071/2,5/14/14,Nagarik Daily,,,,,
Contract-2070-2,Closed,6/10/14,6/11/14,6/11/14,16/070/71,5/12/14,The Himalayan Times,,,,,
Contract-2070-3,Current,3/8/14,3/9/14,3/9/14,DDC/Bag/Bridge/03-070/71,3/10/14,Nagarik Daily,5/9/14,8/25/14,SK Builders,Banke,200000
Contract-2070-4,Current,4/23/14,4/25/14,4/25/14,04(2070/071),4/9/14,Hetauda sandesh,,,,,
Contract-2070-5,Closed,4/23/14,4/25/14,4/26/14,04(2070/071),4/10/14,Hetauda sandesh,3/18/14,4/8/14,S engineering industries,Makwanpur,300000
Contract-2070-6,Current,4/23/14,4/25/14,4/27/14,04(2070/071),4/11/14,Hetauda sandesh,,,,,
Contract-2070-7,Current,4/23/14,4/25/14,4/28/14,04(2070/071),4/12/14,Hetauda sandesh,,,,,
Contract-2070-8,Current,4/23/14,4/25/14,4/29/14,04(2070/071),4/13/14,Hetauda sandesh,,,,,
Contract-2070-9,Closed,2/6/14,2/8/14,2/8/14,15/070/71,1/9/14,The Himalayan Times,3/6/14,4/6/14,Gourishankar nirman sewa,Lalitpur,400000
Contract-2070-10,Current,1/14/14,1/15/14,1/16/14,"13, 2070/2071",1/6/14,The Himalayan Times,2/6/14,6/16/14,SK Builders,Banke,500000

解决方案
this problem isnt that hard, you always could put the data in those csv on array's and work with them, like this solution:
// 1st section
        $fh = fopen('awards.csv', 'r');
        $fhg = fopen('contracts.csv', 'r');
         while (($data = fgetcsv($fh, 0, ",")) !== FALSE) {
            $awards[]=$data;
        }
        while (($data = fgetcsv($fhg, 0, ",")) !== FALSE) {
                $contracts[]=$data;
        }
 // 2nd section   
        for($x=0;$x< count($contracts);$x++)
        {
            if($x==0){
                unset($awards[0][0]);
                $line[$x]=array_merge($contracts[0],$awards[0]); //header
            }
            else{
                $deadlook=0;
                for($y=0;$y <= count($awards);$y++)
                {
                    if($awards[$y][0] == $contracts[$x][0]){
                        unset($awards[$y][0]);
                        $line[$x]=array_merge($contracts[$x],$awards[$y]);
                        $deadlook=1;
                    }           
                }
                if($deadlook==0)
                    $line[$x]=$contracts[$x];
            }
        }
  // 3 section     
        $fp = fopen('final.csv', 'w');//output file set here

        foreach ($line as $fields) {
            fputcsv($fp, $fields);
        }
        fclose($fp);
is hard to me to explain the code, because iam from an spanish country, so my english isnt very good... but i can try

basically the code have 3 section,

in section 1, 
open those 2 file and put the content in an array $awards[] and $contracts[], so $awards[0] is the first line in awards.csv, $awards[1] is the second line in awards.csv, etc, the same in $contracts[].

in section 2,

i compare the first word in every array, $awards[x][0] and $contracts[x][0].

the first if, if($x==0), is to make the header.
first, i delete the first word contractname using unset function and join $awards[0] and $contracts[0] using array_merge function.

then, using those for i select the first word in every line from $contracts array and compare with the first word from every line from $awards array.
so, if($awards[$y][0] == $contracts[$x][0]) check if those first word (ej. Contract-2070-3are) are the same, if those are the same string, delete it and merge those lines.

if those word arent the same, save the $contracts[x] line in $line array and continue.

in section 3,
save the content from $line array in the file.

                        
这篇关于使用php将两个csv文件合并在一起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！