Parsing different types of data format from a CSV

2019-09-21 12:22发布

站内文章 / Java

17 0

叼着烟拽天下

女 | 书童

私信

可以将文章内容翻译成中文,广告屏蔽插件可能会导致该功能失效(如失效，请关闭广告屏蔽插件后再试):

问题:

I am still a beginner with Java programming so I apologise in advance if I am over-complicating my problem.

What is my program? I am building a GUI based program. The goal of the program is to load a CSV, XML or JSON file and for the program to then store the data into an Array. The data will then be displayed in a text box. Ultimately, the program will have the ability to plot data to a graph.

GUI Details:

3 Radio buttons - Allows the user to select CSV, XML OR JSON
Load File Button
Display Button - Displays the data into the textArea
Display Graph Button
Text Area

Problem: I am having trouble storing the data into an Array. I believe this is because of the format of the data. So for example, this is the first 3 lines of the CSV file:

millis,stamp,datetime,light,temp,vcc
1000, 1273010254, 2010/5/4 21:57:34, 333, 78.32, 3.54
2000, 1273010255, 2010/5/4 21:57:35, 333, 78.32, 3.92
3000, 1273010256, 2010/5/4 21:57:36, 344, 78.32, 3.95

(Note - there are 52789000 lines of data in the CSV/XML/JSON files)

The CSV-Reader Class contains the method for reading through the data, storing it into an array and then storing it to a dataList.

As you can see from the above example, some of the data types are much different. I am having particular trouble with splitting/parsing the time and date variables.

Here is what my CSV-Reader Class code looks like at the moment (Again, I apologise for noob code).

import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class CSVReader {

//create a class that will hold arraylist which will have objects representing all lines of the file

private List<Data> dataList = new ArrayList<Data>();
private String path;

public List<Data> getDataList() {
    return dataList;
}

public String getPath() {
    return path;
}
public void setPath(String path) {
    this.path = path;
}

//Create a method to read through the csv stored in the path
//Create the list of data and store in the dataList

public void readCSV() throws IOException{

    //i will create connection with the file, in the path
    BufferedReader in  = new BufferedReader(new FileReader(path));  

    String line = null;
    line = in.readLine();

    while((line = in.readLine())!=null){

        //I need to split and store in the temporary variable and create an object


        String[] splits = line.split("\\s*(=>|,|\\s)\\s*");

        long millis = Long.parseLong(splits[0].trim());
        long stamp = Long.parseLong(splits[1].trim());
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy/M/d HH:mm:ss");
        System.out.println(splits[2].trim());
        LocalDateTime dateTime = LocalDateTime.parse(splits[2].trim(), formatter);
        LocalDate dateTime = dateTime.toLocalDate();
        LocalTime time = dateTime.toLocalTime();
        int light = Integer.parseInt(splits[3].trim());
        double temp = Double.parseDouble(splits[4].trim());
        double vcc = Double.parseDouble(splits[5].trim());

        Data d = new Data(millis,stamp,datetime,light,temp,vcc);//uses constructor


        //final job is to add this object 'd' onto the dataList
        dataList.add(d);

    }//end of while loop

}

Any help would be greatly appreciated!

Edit 1 - I thought that date and time were seperate CSV headers. They were not. Therefore the time variable has been deleted from the program. It has been replaced with the datetime variable.

Edit 2 - My program is now reading the CSV file up until line 15 of the csv

27000, 1273010280, 2010/5/4 21:58:0, 288, 77.74, 3.88

CONSOLE ERRORS

Exception in thread "AWT-EventQueue-0" 
java.time.format.DateTimeParseException: Text **'2010/5/4 21:58:0'** could not 
be parsed at index 15
at java.time.format.DateTimeFormatter.parseResolved0(Unknown Source)
at java.time.format.DateTimeFormatter.parse(Unknown Source)
at java.time.LocalDateTime.parse(Unknown Source)
at CSVReader.readCSV(CSVReader.java:55)
at GUI$2.actionPerformed(GUI.java:85)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$500(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.awt.EventQueue$4.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)

回答1:

Since your date column is a string, you need to parse it into a date object. This depends on your version of Java; if you're using Java 8, you could use the new date classes. Maybe this answer could help you along.

It looks like you have two separate columns, date and time in the CSV header, but the lines have the date and time in one column. This could be part of your problem. You need to determine if they are two columns or one, but they can be parsed with the Java libraries in any case.

Also, it would probably be a good idea to use a battle-tested CSV library like Apache Commons CSV, instead of rolling your own CSV parser. You might get by with your own in a limited case, but CSV is not as simple as it first appears.

回答2:

ISO 8601

SOLVED So the program was crashing due to my CSV not following the correct date and time format (Read comments below).

When exchanging date-time values as text, use the standard ISO 8601 formats rather than inventing your own. They are wisely designed to be easy to parse by machine and easy to read by humans across cultures. So, 2010-05-04T21:57:34, not 2010/5/4 21:57:34.

The java.time classes use the ISO 8601 formats by default when parsing/generating strings.

Data types

Date-time

The 2nd and 3rd columns of your data feed represent the same thing: a date with time-of-day. The first is a count of whole seconds since the epoch reference date of 1970-01-01T00:00Z (Z means UTC).

So it is silly to include both. As mentioned above, the 3rd column is in a poorly chosen format. The 2nd column approach of using a count-from-epoch is also a poor choice in my opinion, as it is not obvious, no human can decipher its meaning, and so it makes mistakes non-obvious thereby making debugging and logging difficult.

To deal with what we have, the seconds-from-epoch can be parsed as an Instant. This class represents a moment in UTC.

Instant instant = Instant.ofEpochMilli( 1_273_010_254L ) ;

Your 3rd column gives a date and time but omits an indicator of time zone or offset-from-UTC. Since it matches the 2nd column when parsed as seconds from first moment of 1970 in UTC, we know its value was intended for UTC. Omitting such info is bad practice, like having a monetary amount with no indicator of currency.

Ideally both columns should be replaced by a string in ISO 8601 format, for example 2010-05-04T21:57:34Z including the Z to indicate UTC.

If we had to parse the 3rd column without knowing it was intended for UTC, we would parse as a LocalDateTime, a date with time-of-day but lacking a time zone or offset. We need to define a formatting pattern to match your input.

DateTimeFormatter f = DateTimeFormatter.ofPattern( "uuuu/M/d HH:mm:ss" );
LocalDateTime localDateTime = LocalDateTime.parse( "2010/5/4 21:57:34" , f );

`BigDecimal`

Your decimal fraction numbers should be represented as BigDecimal objects for accuracy. Never use double/Double or float/Float where you care about accuracy. These types use floating-point technology which trades away accuracy for speed of execution. In contrast, BigDecimal is slow but accurate.

Parse a BigDecimal from a string.

new BigDecimal ( "78.32" )

Apache Commons CSV

Do not write code when well-tested code already exists. There are libraries already written to read CSV/Tab-delimited files.

I use Apache Commons CSV for such work. There are several variations of these formats, all handled by this library.

Here is example code. First define a class to hold your data, here named Reading.

`Reading.java`

package com.basilbourque.example;

import java.math.BigDecimal;
import java.time.Instant;
import java.time.LocalDateTime;

public class Reading {
    private Integer millis;
    private Instant instant;
    private LocalDateTime localDateTime;
    private Integer light;
    private BigDecimal temp;
    private BigDecimal vcc;

    public Reading ( Integer millis , Instant instant , LocalDateTime localDateTime , Integer light , BigDecimal temp , BigDecimal vcc ) {
        // TODO: Add checks for null arguments: Objects.requireNonNull( … ).
        this.millis = millis;
        this.instant = instant;
        this.localDateTime = localDateTime;
        this.light = light;
        this.temp = temp;
        this.vcc = vcc;
    }

    @Override
    public String toString ( ) {
        return "com.basilbourque.example.Reading{" +
                "millis=" + millis +
                ", instant=" + instant +
                ", localDateTime=" + localDateTime +
                ", light=" + light +
                ", temp=" + temp +
                ", vcc=" + vcc +
                '}';
    }
}

Example data file:

millis,stamp,datetime,light,temp,vcc
1000, 1273010254, 2010/5/4 21:57:34, 333, 78.32, 3.54
2000, 1273010255, 2010/5/4 21:57:35, 333, 78.32, 3.92
3000, 1273010256, 2010/5/4 21:57:36, 344, 78.32, 3.95

And now call upon Commons CSV to parse that data, instantiate Reading objects, and collect them.

DateTimeFormatter f = DateTimeFormatter.ofPattern( "uuuu/M/d HH:mm:ss" );

List < Reading > readings = new ArrayList <>( 3 );
Reader reader = null;
try {
    reader = new FileReader( "/Users/basilbourque/data.csv" );
    Iterable < CSVRecord > records = CSVFormat.RFC4180.withIgnoreSurroundingSpaces( true ).withHeader().parse( reader );
    for ( CSVRecord record : records ) {
        // Grab inputs
        String millisInput = record.get( "millis" );
        String stampInput = record.get( "stamp" );
        String datetimeInput = record.get( "datetime" );
        String lightInput = record.get( "light" );
        String tempInput = record.get( "temp" );
        String vccInput = record.get( "vcc" );

        // Parse inputs
        Integer millis = Integer.valueOf( millisInput );
        Instant instant = Instant.ofEpochSecond( Integer.valueOf( stampInput ) );
        LocalDateTime localDateTime = LocalDateTime.parse( datetimeInput , f );
        Integer light = Integer.valueOf( lightInput );
        BigDecimal temp = new BigDecimal( tempInput );
        BigDecimal vcc = new BigDecimal( vccInput );

        // Construct object
        Reading r = new Reading( millis , instant , localDateTime , light , temp , vcc );

        // Collect object
        readings.add( r );
    }
} catch ( FileNotFoundException e ) {
    e.printStackTrace();
} catch ( IOException e ) {
    e.printStackTrace();
}

System.out.println( readings );

[com.basilbourque.example.Reading{millis=1000, instant=2010-05-04T21:57:34Z, localDateTime=2010-05-04T21:57:34, light=333, temp=78.32, vcc=3.54}, com.basilbourque.example.Reading{millis=2000, instant=2010-05-04T21:57:35Z, localDateTime=2010-05-04T21:57:35, light=333, temp=78.32, vcc=3.92}, com.basilbourque.example.Reading{millis=3000, instant=2010-05-04T21:57:36Z, localDateTime=2010-05-04T21:57:36, light=344, temp=78.32, vcc=3.95}]

Regarding your mention:

store the data into an Array

You are using an ArrayList in your code, not an array. See the Oracle Tutorials for lists and for arrays to understand the difference. Generally best to use the Java Collections framework. Where size and speed really matter, we may choose an array.

About java.time

The java.time framework is built into Java 8 and later. These classes supplant the troublesome old legacy date-time classes such as java.util.Date, Calendar, & SimpleDateFormat.

The Joda-Time project, now in maintenance mode, advises migration to the java.time classes.

To learn more, see the Oracle Tutorial. And search Stack Overflow for many examples and explanations. Specification is JSR 310.

You may exchange java.time objects directly with your database. Use a JDBC driver compliant with JDBC 4.2 or later. No need for strings, no need for java.sql.* classes.

Where to obtain the java.time classes?

Java SE 8, Java SE 9, Java SE 10, Java SE 11, and later - Part of the standard Java API with a bundled implementation.
- Java 9 adds some minor features and fixes.
Java SE 6 and Java SE 7
- Most of the java.time functionality is back-ported to Java 6 & 7 in ThreeTen-Backport.
Android
- Later versions of Android bundle implementations of the java.time classes.
- For earlier Android (<26), the ThreeTenABP project adapts ThreeTen-Backport (mentioned above). See How to use ThreeTenABP….

The ThreeTen-Extra project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as Interval, YearWeek, YearQuarter, and more.

回答3:

it seems you have 5 tokens in one line (not 6),
you are trying to parse a date string as a double,

with some modifications in your code, following one shold work for you:

String[] splits = line.split(",");// line.split("\\s*(=>|,|\\s)\\s*");

long millis = Long.parseLong(splits[0].trim());
long stamp = Long.parseLong(splits[1].trim());
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy/M/d HH:mm:ss");
LocalDateTime dateTime = LocalDateTime.parse(splits[2].trim(), formatter);
LocalDate date = dateTime.toLocalDate();
LocalTime time = dateTime.toLocalTime();
int light = Integer.parseInt(splits[3].trim());
double temp = Double.parseDouble(splits[4].trim());
double vcc = Double.parseDouble(splits[5].trim());

回答4:

You are trying to parse date as numeric and this is a String field, maybe you can use this

Date date=new SimpleDateFormat("yyyy/M/D HH:mm:ss").parse(splits[2]);

Now with the date, you can transform as you want

标签： java arrays csv parsing

叼着烟拽天下

女 | 书童

私信

收藏的人(0)

Ta的文章更多文章

0条评论

还没有人评论过~

Parsing different types of data format from a CSV

问题:

回答1:

回答2:

ISO 8601

Data types

Date-time

BigDecimal

Apache Commons CSV

Reading.java

About java.time

回答3:

回答4:

收藏的人(0)

举报内容

检举类型

检举原因

检举说明(必填)

打开微信“扫一扫”，打开网页后点击屏幕右上角分享按钮

`BigDecimal`

`Reading.java`