Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Converting an CSV file to a JSON object in Java

Tags:

java

json

csv

xls

Is there an open source java library to convert a CSV (or XLS) file to a JSON object?

I tried using json.cdl, but somehow it does not seem to work for large CSV strings.

I'm trying to find something like http://www.cparker15.com/code/utilities/csv-to-json/, but written in Java.

like image 631
g2jose Avatar asked Mar 01 '12 21:03

g2jose


People also ask

How do I read a CSV file and convert to JSON in Java?

Convert CSV file to JSON file. Get cvs data as InputStream from the resources directory. We use the BufferedReader and InputStreamReader to iterate and read the InputStream and return it as a string. Convert the csv string into JSON string using CDL. toJSONArray() .

Can we convert list to JSON in Java?

We can convert a list to the JSON array using the JSONArray. toJSONString() method and it is a static method of JSONArray, it will convert a list to JSON text and the result is a JSON array.


2 Answers

You can use Open CSV to map CSV to a Java Bean, and then use JAXB to convert the Java Bean into a JSON object.

http://opencsv.sourceforge.net/#javabean-integration

http://jaxb.java.net/guide/Mapping_your_favorite_class.html

like image 176
Mouscellaneous Avatar answered Sep 22 '22 14:09

Mouscellaneous


Here is my Java program and hope somebody finds it useful.

Format needs to be like this:

"SYMBOL,DATE,CLOSE_PRICE,OPEN_PRICE,HIGH_PRICE,LOW_PRICE,VOLUME,ADJ_CLOSE

AAIT,2015-02-26 00:00:00.000,-35.152,0,35.152,35.12,679,0

AAL,2015-02-26 00:00:00.000,49.35,50.38,50.38,49.02,7572135,0"

First line is the column headers. No quotation marks anywhere. Separate with commas and not semicolons. You get the deal.

/* Summary: Converts a CSV file to a JSON file.*/

//import java.util.*;
import java.io.*;

import javax.swing.*;
import javax.swing.filechooser.FileNameExtensionFilter;

public class CSVtoJSON extends JFrame{
    private static final long serialVersionUID = 1L;
    private static File CSVFile;
    private static BufferedReader read;
    private static BufferedWriter write;

    public CSVtoJSON(){
        FileNameExtensionFilter filter = new FileNameExtensionFilter("comma separated values", "csv");
        JFileChooser choice = new JFileChooser();
        choice.setFileFilter(filter); //limit the files displayed

        int option = choice.showOpenDialog(this);
        if (option == JFileChooser.APPROVE_OPTION) {
            CSVFile = choice.getSelectedFile();
        }
        else{
            JOptionPane.showMessageDialog(this, "Did not select file. Program will exit.", "System Dialog", JOptionPane.PLAIN_MESSAGE);         
            System.exit(1);
        }
    }

    public static void main(String args[]){
        CSVtoJSON parse = new CSVtoJSON();
        parse.convert();

        System.exit(0);
    }

    private void convert(){
        /*Converts a .csv file to .json. Assumes first line is header with columns*/
        try {
            read = new BufferedReader(new FileReader(CSVFile));

            String outputName = CSVFile.toString().substring(0, 
                    CSVFile.toString().lastIndexOf(".")) + ".json"; 
            write = new BufferedWriter(new FileWriter(new File(outputName)));

            String line;
            String columns[]; //contains column names
            int num_cols;
            String tokens[];

            int progress = 0; //check progress

            //initialize columns
            line = read.readLine(); 
            columns = line.split(",");
            num_cols = columns.length;


            write.write("["); //begin file as array
            line = read.readLine();


            while(true) {
                tokens = line.split(",");

                if (tokens.length == num_cols){ //if number columns equal to number entries
                    write.write("{");

                    for (int k = 0; k < num_cols; ++k){ //for each column 
                        if (tokens[k].matches("^-?[0-9]*\\.?[0-9]*$")){ //if a number
                            write.write("\"" + columns[k] + "\": " + tokens[k]);
                            if (k < num_cols - 1) write.write(", ");                                                }
                        else { //if a string
                            write.write("\"" + columns[k] + "\": \"" + tokens[k] + "\"");
                            if (k < num_cols - 1) write.write(", ");
                        }
                    }

                    ++progress; //progress update
                    if (progress % 10000 == 0) System.out.println(progress); //print progress           


                    if((line = read.readLine()) != null){//if not last line
                        write.write("},");
                        write.newLine();
                    }
                    else{
                        write.write("}]");//if last line
                        write.newLine();
                        break;
                    }
                }
                else{
                    //line = read.readLine(); //read next line if wish to continue parsing despite error 
                    JOptionPane.showMessageDialog(this, "ERROR: Formatting error line " + (progress + 2)
                     + ". Failed to parse.", 
                            "System Dialog", JOptionPane.PLAIN_MESSAGE);                    
                    System.exit(-1); //error message
                }
            }

            JOptionPane.showMessageDialog(this, "File converted successfully to "     + outputName, 
                    "System Dialog", JOptionPane.PLAIN_MESSAGE);

            write.close();
            read.close();
        }
        catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }       
    }
    }

Requires Swing but comes with a nifty little GUI so those who know absolutely no Java can use it once packaged into an executable .jar. Feel free to improve upon it. Thank you StackOverflow for helping me out all these years.

like image 25
Marco Rosas Avatar answered Sep 19 '22 14:09

Marco Rosas