Convert CSV file to JSON object in Java

Question

Is there an open source Java library for converting a CSV (or XLS) file to a JSON object?

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

I am trying to find something like http://www.cparker15.com/code/utilities/csv-to-json/ but it is written in Java.

Answer 1

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

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

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

Answer 2

Here is my Java program and I hope someone finds it useful.

The format should be like this:

"SYMBOL, DATE, CLOSE_PRICE, OPEN_PRICE, HIGH_PRICE, LOW_PRICE, TOM, 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 "

The first row is the column headings. No quotes anywhere. Separate with commas, not a semicolon. You get a 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(); } } } 

Swing is required, but comes with a small GUI, so those who have absolutely no knowledge of Java can use it after loading it into the .jar executable. Feel free to improve it. Thanks to StackOverflow for helping me all these years.

Answer 3

If your CSV is simple, then it’s easy to write by hand, but CSV can include unpleasant cases with cross-citation, missing values, etc.

• upload file using BufferedReader.readLine()
• use String.split(",") to get the value from each line - NB this approach will only work if there are no commas in your values!
• write each value to output using BufferedWriter
  • with necessary JSON brackets and quoting

You might want to use the CSV library and then convert to JSON manually '

Answer 4

@Mouscode was mainly responsible for this, so please give him credit.

Here is what I came up with:

 package edu.apollogrp.csvtojson; import au.com.bytecode.opencsv.bean.CsvToBean; import au.com.bytecode.opencsv.bean.HeaderColumnNameMappingStrategy; import org.codehaus.jackson.map.ObjectMapper; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.io.InputStreamReader; import java.util.List; public class ConvertCsvToJson { public static void main(String[] args) throws IOException, ClassNotFoundException { if (args.length > 1) { String pathToCsvFile = args[0]; String javaBeanClassName = "edu.apollogrp.csvtojson.bean." + args[1]; final File file = new File(pathToCsvFile); if (!file.exists()) { System.out.println("The file you specified does not exist. path=" + pathToCsvFile); } Class<?> type = null; try { type = Class.forName(javaBeanClassName); } catch (ClassNotFoundException e) { System.out.println("The java bean you specified does not exist. className=" + javaBeanClassName); } HeaderColumnNameMappingStrategy strat = new HeaderColumnNameMappingStrategy(); strat.setType(type); CsvToBean csv = new CsvToBean(); List list = csv.parse(strat, new InputStreamReader(new FileInputStream(file))); System.out.println(new ObjectMapper().writeValueAsString(list)); } else { System.out.println("Please specify the path to the csv file."); } } } 

I used maven to include dependencies, but you can also load them manually and include them in your classpath.

 <dependency> <groupId>net.sf.opencsv</groupId> <artifactId>opencsv</artifactId> <version>2.0</version> </dependency> <dependency> <groupId>org.codehaus.jackson</groupId> <artifactId>jackson-mapper-asl</artifactId> <version>1.9.12</version> </dependency> <dependency> <groupId>org.codehaus.jackson</groupId> <artifactId>jackson-core-asl</artifactId> <version>1.9.12</version> </dependency> 

Answer 5

I used the excel file in this code. You can use csv. I wrote this class for a specific Excel / csv format that I know of.

 import java.io.File; public class ReadExcel { private String inputFile; public void setInputFile(String inputFile) { this.inputFile = inputFile; } public void read() throws IOException { File inputWorkbook = new File(inputFile); Workbook w; try { w = Workbook.getWorkbook(inputWorkbook); // Get the first sheet Sheet sheet = w.getSheet(0); // Loop over first 10 column and lines int columns = sheet.getColumns(); int rows = sheet.getRows(); ContactList clist = new ContactList(); ArrayList<Contact> contacts = new ArrayList<Contact>(); for (int j = 1; j < rows; j++) { Contact contact = new Contact(); for (int i = 0; i < columns; i++) { Cell cell = sheet.getCell(i, j); switch (i) { case 0: if (!cell.getContents().equalsIgnoreCase("")) { contact.setSrNo(Integer.parseInt(cell.getContents())); } else { contact.setSrNo(j); } break; case 1: contact.setName(cell.getContents()); break; case 2: contact.setAddress(cell.getContents()); break; case 3: contact.setCity(cell.getContents()); break; case 4: contact.setContactNo(cell.getContents()); break; case 5: contact.setCategory(cell.getContents()); break; } } contacts.add(contact); } System.out.println("done"); clist.setContactList(contacts); JSONObject jsonlist = new JSONObject(clist); File f = new File("/home/vishal/Downloads/database.txt"); FileOutputStream fos = new FileOutputStream(f, true); PrintStream ps = new PrintStream(fos); ps.append(jsonlist.toString()); } catch (BiffException e) { e.printStackTrace(); System.out.println("error"); } } public static void main(String[] args) throws IOException { ReadExcel test = new ReadExcel(); test.setInputFile("/home/vishal/Downloads/database.xls"); test.read(); } } 

I used jxl.jar to read excel

Answer 6

With Java 8, writing JSON is at hand.

You did not specify which JSON API you want, so I assume that by “JSON object” you mean a string with a serialized JSON object.

What I did in the CSV Cruncher project:

1. Download CSV using HSQLDB . This is a relatively small (~ 2 MB) library that actually implements the SQL 2008 database.
2. Query this database using JDBC.
3. Create a JSON JDK object ( javax.json.JsonObject ) and serialize it.

Here's how to do it:

 static void convertResultToJson(ResultSet resultSet, Path destFile, boolean printAsArray) { OutputStream outS = new BufferedOutputStream(new FileOutputStream(destFile.toFile())); Writer outW = new OutputStreamWriter(outS, StandardCharsets.UTF_8); // javax.json way JsonObjectBuilder builder = Json.createObjectBuilder(); // Columns for (int colIndex = 1; colIndex <= metaData.getColumnCount(); colIndex++) { addTheRightTypeToJavaxJsonBuilder(resultSet, colIndex, builder); } JsonObject jsonObject = builder.build(); JsonWriter writer = Json.createWriter(outW); writer.writeObject(jsonObject); 

The whole point is here . (Initially, I wrote my own CSV analysis and JSON record, but found out that both are complex enough to gain access to a proven standard library.)

Answer 7

If you are using Java 8, you can do something like this. No libraries or complex logic required.

First, create a POJO representing your new JSON object. In my example, it is called "YourJSONObject" and has a two-line constructor.

What the code does is initially read the file, and then create a stream of lines based on the lines. (line is equivalent to line in CSV file).

Then we pass the string to the display function, which splits it into a comma, and then creates the YourJSONObject.

All these objects are then assembled into a list, which we pass to the JSONArray constructor.

You now have an array of JSONObjects. Then you can call toString () for this object if you want to see a textual representation of this.

  JSONArray objects = new JSONArray(Files.readAllLines(Paths.get("src/main/resources/your_csv_file.csv")) .stream() .map(s -> new YourJSONObject(s.split(",")[0], s.split(",")[1])) .collect(toList())); 

Answer 8

Here is the class that I generated to return a JSONArray, and not just to print to a file.

 import com.fasterxml.jackson.databind.ObjectMapper; import com.fasterxml.jackson.dataformat.csv.CsvMapper; import com.fasterxml.jackson.dataformat.csv.CsvSchema; import org.json.simple.JSONArray; import org.json.simple.parser.JSONParser; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import java.io.File; import java.util.List; import java.util.Map; public class CsvToJson { private static final Logger log = LoggerFactory.getLogger(UtilsFormat.class); private static CsvToJson instance; public static JSONArray convert(File input) throws Exception { JSONParser parser = new JSONParser(); CsvSchema csvSchema = CsvSchema.builder().setUseHeader(true).build(); CsvMapper csvMapper = new CsvMapper(); // Read data from CSV file List<? extends Object> readAll = csvMapper.readerFor(Map.class).with(csvSchema).readValues(input).readAll(); ObjectMapper mapper = new ObjectMapper(); JSONArray jsonObject = (JSONArray) parser.parse(mapper.writerWithDefaultPrettyPrinter().writeValueAsString(readAll)); System.out.print(jsonObject.toString()); return new JSONArray(); } } 

Answer 9

Old post, but I decided to share my decision. Quotes are supposed to be used around a significant comma. Then it also removes all quotes.

This method accepts a string in CSV format. Therefore, it is assumed that you have already read the CSV file for the line. Make sure you do not delete NextLine characters ('\ n') while reading.

This method is by no means perfect, but it may be a quick solution to one method in pure java that you are looking for.

 public String CSVtoJSON(String output) { String[] lines = output.split("\n"); StringBuilder builder = new StringBuilder(); builder.append('['); String[] headers = new String[0]; //CSV TO JSON for (int i = 0; i < lines.length; i++) { String[] values = lines[i].replaceAll("\"", "").split("۞"); if (i == 0) //INDEX LIST { headers = values; } else { builder.append('{'); for (int j = 0; j < values.length && j < headers.length; j++) { String jsonvalue = "\"" + headers[j] + "\":\"" + values[j] + "\""; if (j != values.length - 1) { //if not last value of values... jsonvalue += ','; } builder.append(jsonvalue); } builder.append('}'); if (i != lines.length - 1) { builder.append(','); } } } builder.append(']'); output = builder.toString(); return output; }