MongoDB with CSV data

Question

I am writing an application where the input source of data is in the CSV format. I need to perform CRUD operations on the DB. I am planning to go with the MongoDB since I feel it suits my need.

My question is to 1. How to store the CSV data in the MongoDB? 2. Do I need to parse the CSV to json? 3. I want to display the data on the UI, so Can MongoDB retrieve the data in json format?

Please help me since I am a newbie in MongoDB.

Regards, Pradeep

Answer 1

Storing the CSV directly in MongoDB as a string would be a very bad idea as it would be impractical to manipulate and query your data in that format, so you'd want to convert it to JSON (BSON actually) documents as you're adding it to MongoDB.

If it's a one-time import of the CSV data into MongoDB, then you can use the mongoimport utility to do that. Otherwise you'll need to do the conversion in your code that deals with the CSV input data.

Answer 2

Using JAVA

Grid FS stores files in two collections:

• bucketName.chunks (GridFS divides the file into chunks that are stored as distinct documents in a chunks collection.)
• bucketName.files GridFS stores information about stored files in file collection. There will be one files collection document per stored file.

To retrieve a stored file, GridFS locates and returns all of its component chunks.

Store csv file to mongoDB by using GridFS

public static void main(String[] args) throws IOException {
    mongoDB_GRIDFS("D:\\Yash\\JavaCSV.csv");
}
public static void mongoDB_GRIDFS(String csvlocation) throws IOException{
    Mongo Mongo = new Mongo( "localhost" , 27017 ); // Connect to MongoDB
    DB db = Mongo.getDB( "DBName" ); // Get database
    String bucketName = "BucketName";
    GridFS gridFs = new GridFS(db,bucketName); //Create instance of GridFS implementation  
    String imageName = "image1";
        //upload(gridFs, inputcsvlocation);
        //download(gridFs, imageName);
        //delete(gridFs, imageName);        
    Mongo.close();
}
public static void upload(GridFS gridFs, String csvlocation, String imageName) throws IOException{
    GridFSInputFile gridFsInputFile = gridFs.createFile(new File(csvlocation));
    gridFsInputFile.setId("777");
    gridFsInputFile.setFilename(imageName); //Set a name on GridFS entry
    gridFsInputFile.save(); //Save the file to MongoDB
}
public static void download(GridFS gridFs, String imageName) throws IOException{
    GridFSDBFile outputImageFile = gridFs.findOne(imageName);
    String outcsvLocation = "D:\\Yash\\mongoCSV.csv";//Location of the file read from MongoDB to be written
    outputImageFile.writeTo(new File(outcsvLocation));
}
public static void delete(GridFS gridFs, String imageName){
    gridFs.remove( gridFs.findOne(imageName) );
}

Convert csv data to JSON (or) JSON to csv

public static void main(String myHelpers[]) throws ParseException, IOException{
    String jsonString = "{'csvdata': [{'field1': 11,'field2': 12,'field3': 13},{'field1': 21,'field2': 22,'field3': 23},{'field1': 31,'field2': 32,'field3': 33}]}";

    Json2Csv("D:\\Yash\\JavaCSV.csv", jsonString);
    csv2Josn("D:\\Yash\\JavaCSV.csv");
}
@SuppressWarnings("unchecked")
public static void csv2Josn(String fileName) throws IOException{
    BufferedReader csv = new BufferedReader(new FileReader(new File(fileName)));
    JSONObject obj = new JSONObject();
    JSONArray csvFields = new JSONArray();
    String csvRow = "", keys = "";
    int rowscount = 0;
    while((csvRow = csv.readLine()) != null){
        String[] data = csvRow.split(","); 
        if (rowscount == 0) keys = csvRow;
        else if(data.length == keys.split(",").length){ 
            JSONObject fieldobj = new JSONObject();
            for (int i = 0; i < data.length; i++) fieldobj.put( keys.split(",")[i], data[i]);
            csvFields.add(fieldobj);
        }
        rowscount++;
    }
    obj.put("scvdata", csvFields);
    System.out.println("Final Object : "+obj);
}
public static void Json2Csv(String fileName, String jsonString) throws IOException, ParseException{
    JSONParser jparser = new JSONParser();      
    jsonString = jsonString.replace("'", "\"");
    java.io.FileWriter writer = new FileWriter(fileName);
        JSONObject output = (org.json.simple.JSONObject) jparser.parse(jsonString);
        JSONArray csvdata = (org.json.simple.JSONArray) output.get("csvdata");
        String[] orderdkeys = {"field1", "field2", "field3"}; // JSONObject = HashMap (unorered)
        writer.append(orderdkeys[0]+","+orderdkeys[1]+","+orderdkeys[2]+"\n");
        for (int i = 0; i < csvdata.size(); i++) {
            JSONObject row = (JSONObject) csvdata.get(i);
            StringBuffer rowdata= new StringBuffer();
            if (orderdkeys.length == row.size())
            for (int j = 0; j < row.size(); j++) {
                rowdata.append(row.get(orderdkeys[j]));
                if (j+1 < row.size()) rowdata.append(",");
            }
            writer.append(rowdata.append("\n").toString());
        }
        writer.flush();         writer.close();
}

Answer 3

power shell script

import-csv "SampleInput.csv" | ConvertTo-Json | Add-Content -Path "output.json"
    
cmd.exe /c "mongoimport -h hostname:port--jsonArray --db dbName --collection collectionName --file output.json"