java - 如何在Java中有效地计算CSV文件的行数

Question

我开发了一个代码，可以打开 CSV 文件并使用 for 循环计算行数，但我觉得这种方法效率不高，并且会导致一些延迟。

• TargetFile.mdb 有 120 行
• report.csv 有 11000 行

如果我使用此方法，代码需要运行 120*11000=1320000 次 来计算每个资源计数。这是我的代码:

这是由 Xavier Delamotte 编写的新的有效代码，可以有效地计算行数:

import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import au.com.bytecode.opencsv.CSVReader;

import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.Table;

public class newcount {

    public static class ValueKey{
        String mdmId;
        String pgName;

        @Override
        public int hashCode() {
            final int prime = 31;
            int result = 1;
            result = prime * result + ((mdmId == null) ? 0 : mdmId.hashCode());
            result = prime * result
                + ((pgName == null) ? 0 : pgName.hashCode());
            return result;
        }
        @Override
        public boolean equals(Object obj) {
            if (this == obj)
                return true;
            if (obj == null)
                return false;
            if (getClass() != obj.getClass())
                return false;
            ValueKey other = (ValueKey) obj;
            if (mdmId == null) {
                if (other.mdmId != null)
                    return false;
            } else if (!mdmId.equals(other.mdmId))
                return false;
            if (pgName == null) {
                if (other.pgName != null)
                    return false;
            } else if (!pgName.equals(other.pgName))
                return false;
            return true;
        }
        public ValueKey(String mdmId, String pgName) {
            super();
            this.mdmId = mdmId;
            this.pgName = pgName;
        }
    }

    public static void main(String[] args) throws IOException, SQLException,Throwable{


        Integer count;

        String MDMID,NAME,PGNAME,PGTARGET,TEAM;

        Table RESOURCES = Database.open(new File("C:/STATS/TargetFile.mdb")).getTable("RESOURCES");
        int pcount = RESOURCES.getRowCount();


        String csvFilename = "C:\\MDMSTATS\\APEX\\report.csv";
        CSVReader csvReader = new CSVReader(new FileReader(csvFilename));
        List<String[]> content = csvReader.readAll();
        Map<ValueKey, Integer> csvValuesCount = new HashMap<ValueKey, Integer>();
        for (String[] rowcsv  : content) {
            ValueKey key = new ValueKey(rowcsv[6], rowcsv[1]);
            count = csvValuesCount.get(key);
            csvValuesCount.put(key,count == null ? 1: count + 1);

        }

        //int count = 0;
        // Taking 1st resource data
        for (int i = 0; i < pcount-25; i++) {
            Map<String, Object> row = RESOURCES.getNextRow();
            TEAM = row.get("TEAM").toString();
            MDMID = row.get("MDM ID").toString();
            NAME = row.get("RESOURCE NAME").toString();
            PGNAME = row.get("PG NAME").toString();
            PGTARGET = row.get("PG TARGET").toString();
            int PGTARGETI = Integer.parseInt(PGTARGET);
            Integer countInteger = csvValuesCount.get(new ValueKey(MDMID, PGNAME));
            count = countInteger == null ? 0: countInteger;
            System.out.println(NAME+"\t"+PGNAME+"\t"+count);

        }
    }
}

Answer 1

我建议只读取一次csv文件，并统计mdmId和pgName组成的key的出现次数。

如果你有 Guava ，你可以使用MultiSet<ValueKey> http://guava-libraries.googlecode.com/svn-history/r8/trunk/javadoc/com/google/common/collect/Multiset.html而不是 Map<ValueKey,Integer>

编辑:要使用 ValueKey 类，您需要放入另一个文件或将其声明为静态。

类值键:

    public static class ValueKey{
        String mdmId;
        String pgName;
        @Override
        public int hashCode() {
            final int prime = 31;
            int result = 1;
            result = prime * result + ((mdmId == null) ? 0 : mdmId.hashCode());
            result = prime * result
                    + ((pgName == null) ? 0 : pgName.hashCode());
            return result;
        }
        @Override
        public boolean equals(Object obj) {
            if (this == obj)
                return true;
            if (obj == null)
                return false;
            if (getClass() != obj.getClass())
                return false;
            ValueKey other = (ValueKey) obj;
            if (mdmId == null) {
                if (other.mdmId != null)
                    return false;
            } else if (!mdmId.equals(other.mdmId))
                return false;
            if (pgName == null) {
                if (other.pgName != null)
                    return false;
            } else if (!pgName.equals(other.pgName))
                return false;
            return true;
        }
        public ValueKey(String mdmId, String pgName) {
            super();
            this.mdmId = mdmId;
            this.pgName = pgName;
        }
    }

你的方法:

    Table RESOURCES = Database.open(new File("TargetFile.mdb")).getTable("RESOURCES");
    int pcount = RESOURCES.getRowCount();

    String csvFilename = "C:\\STATS\\APEX\\report.csv";
    CSVReader csvReader = new CSVReader(new FileReader(csvFilename));
    List<String[]> content = csvReader.readAll();
    Map<ValueKey, Integer> csvValuesCount = new HashMap<ValueKey, Integer>();
    for (String[] rowcsv  : content) {
        ValueKey key = new ValueKey(rowcsv[6], rowcsv[1]);
        Integer count = csvValuesCount.get(key);
        csvValuesCount.put(key,count == null ? 1: count + 1);

    }

    int count = 0;
    // Taking 1st resource data
    for (int i = 0; i < pcount; i++) {
        Map<String, Object> row = RESOURCES.getNextRow();
        TEAM = row.get("TEAM").toString();
        MDMID = row.get("MDM ID").toString();
        NAME = row.get("RESOURCE NAME").toString();
        PGNAME = row.get("PG NAME").toString();
        PGTARGET = row.get("PG TARGET").toString();
        int PGTARGETI = Integer.parseInt(PGTARGET);
        Integer countInteger = csvValuesCount.get(new ValueKey(MDMID, PGNAME));
        count = countInteger == null ? 0: countInteger;
    }