Use OpenCSV to perform CSV data verification and cleaning
Use OpenCSV to perform CSV data verification and cleaning
Overview:
When dealing with a large amount of data, you often encounter a situation that needs to be checked and cleaned to the CSV file.OpenCSV is an open source Java library, which provides a set of powerful tools that can easily read, verify and clean the CSV files.This article will introduce how to use OpenCSV to verify and clean the CSV data, and provide the corresponding Java code example.
Environment preparation:
First, we need to introduce the OpenCSV library in the Java project.It can be achieved by adding OpenCSV dependency items to the project's construction file (such as Maven's pom.xml).The following is an example of using OPENCSV to use Maven:
<dependency>
\u00a0 \u00a0 <groupId>com.opencsv</groupId>
\u00a0 \u00a0 <artifactId>opencsv</artifactId>
\u00a0 \u00a0 <version>5.5.1</version>
</dependency>
Data validation:
Using OpenCSV can check the data in the CSV file to ensure the accuracy and consistency of the data.The following is an example code that checks the CSV file:
public class CSVValidator {
\u00a0 \u00a0 public static void main(String[] args) {
\u00a0 \u00a0 \u00a0 \u00a0 try (ICSVParser parser = new CSVParserBuilder()
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 .withSeparator(',')
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 .withQuoteChar('"')
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 .withEscapeChar('\\')
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 .withStrictQuotes(true)
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 .withIgnoreLeadingWhiteSpace(true)
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 .build();
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 CSVReader reader = new CSVReaderBuilder(new FileReader("data.csv"))
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 .withCSVParser(parser)
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 .build()) {
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 String[] nextLine;
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 while ((nextLine = reader.readNext()) != null) {
\ U00A0 \ U00A0 \ U00A0 \ U00A0 00A0 00A0 00A0 00a0 00a0 // Check each line of data
\ U00A0 \ U00A0 \ U00A0 \ U00A0 00A0 00A0 00A0 00a0 00a0 // Add your verification logic here, such as checking whether the field is empty or whether it meets a specific format
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 }
\u00a0 \u00a0 \u00a0 \u00a0 } catch (IOException e) {
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 e.printStackTrace();
\u00a0 \u00a0 \u00a0 \u00a0 }
\u00a0 \u00a0 }
}
In the above example, we use OpenCSV's CSVPARSER and CSVReader to read CSV files.We can specify the separators of CSV files, reference characters, transit characters, etc. by configure CSVPARSER.When reading every line of data of CSV files in a loop, you can use a customized verification logic to check the data.
Data cleaning:
In addition to data verification, OpenCSV also provides data cleaning functions, such as removing duplicate data, deleting unnecessary columns, formatting dates, etc.The following is a sample code for cleaning the CSV file:
public class CSVCleaner {
\u00a0 \u00a0 public static void main(String[] args) {
\u00a0 \u00a0 \u00a0 \u00a0 try (CSVWriter writer = new CSVWriter(new FileWriter("cleaned_data.csv"))) {
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 Set<String> uniqueRecords = new HashSet<>();
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 try (CSVReader reader = new CSVReader(new FileReader("data.csv"))) {
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 String[] nextLine;
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 while ((nextLine = reader.readNext()) != null) {
\ U00A0 \ U00A0 \ U00A0 \ U00A0 00A0 00A0 00A0 00A0 00A0 00a0 00a0 // Add your data cleaning logic here
\ U00A0 \ U00A0 \ U00A0 \ U00A0 00A0 00A0 00A0 00A0 00A0 00a0 00a0 // For example, removing duplicate data
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 if (!uniqueRecords.contains(Arrays.toString(nextLine))) {
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 writer.writeNext(nextLine);
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 uniqueRecords.add(Arrays.toString(nextLine));
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 }
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 }
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 }
\u00a0 \u00a0 \u00a0 \u00a0 } catch (IOException e) {
\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 e.printStackTrace();
\u00a0 \u00a0 \u00a0 \u00a0 }
\u00a0 \u00a0 }
}
In the above example, we use CSVWriter to write the cleaning data to the new CSV file.When reading each line of data of the original CSV file in a loop, you can use a custom cleaning logic to process the data, such as removing duplicate data and writing the cleaning data into the new CSV file.
in conclusion:
With the OpenCSV library, we can easily check and clean the CSV files.By configured CSVPARSER and CSVReader, we can achieve custom logic logic.At the same time, by using CSVWriter, we can write the cleaned data into the new CSV file.In addition, OpenCSV also provides other powerful functions, such as the export and import of CSV files.
