Turn JSON large files into tab delimited flat file

Question

I currently have a vendor that provides API json format files and they need to be converted into a flat file so I can load them into SQL server. Below is a row (1 of over 700K+ rows) from the JSON file.

I will need to import all the files from a directory on my C:\Users\Lumen\Documents\APIfiles_fromvendor\test.json.

Any help will be appreciated as I am learning at the moment.

Below is the sample:

 {
   "vendornameoutput":[
      {
         "stores":{
            "offers":[
               {
                  "avgRating":0.0,
                  "userRatings":0,
                  "fulfilledBy":"",
                  "availability":"IN_STOCK",
                  "listPrice":128.88,
                  "maxRating":0,
                  "salePrice":128.88,
                  "upcs":[

                  ],
                  "imageUrl":"http://d2dddpb.JPG",
                  "shippingText":"This item ships free!",
                  "mpns":[
                     "723fdads32"
                  ],
                  "seller":"",
                  "cartPrice":false,
                  "addOnItem":false,
                  "productUrl":"http://www.zoyyyyr.com",
                  "salesRank":0,
                  "usedOffers":0,
                  "priceHistoryAvailable":true,
                  "lastRecordedAt":1441326319301,
                  "buyBoxWinner":false,
                  "newOffers":0,
                  "sku":"212120G319514518571",
                  "refurbishedOffers":0,
                  "additionalImageUrls":[

                  ]
               },
               {
                  "avgRating":0.0,
                  "userRatings":0,
                  "fulfilledBy":"",
                  "availability":"IN_STOCK",
                  "listPrice":128.88,
                  "maxRating":0,
                  "salePrice":128.88,
                  "upcs":[

                  ],
                  "imageUrl":"http://d2pbmlo3fAS01.JPG",
                  "shippingText":"This item ships free!",
                  "mpns":[
                     "72fsdfg5332"
                  ],
                  "seller":"",
                  "cartPrice":false,
                  "addOnItem":false,
                  "productUrl":"http://www.zddodadfaryyyo.com/i/G3198dfasa571/?category=9668",
                  "salesRank":0,
                  "usedOffers":0,
                  "priceHistoryAvailable":true,
                  "lastRecordedAt":1429844879892,
                  "buyBoxWinner":false,
                  "newOffers":0,
                  "sku":"ddadG3dasd198ddds571",
                  "refurbishedOffers":0,
                  "additionalImageUrls":[

                  ]
               }
            ],
            "storeId":2656,
            "storeName":"testsite",
            "title":"Pump Gun Only, For Use With 1ED22 Oiler"
         },
         "mpns":[
            "72ddd332"
         ],
         "imageUrl":"http://d2ulldddda/Z_HJFvfo5oy.JPG",
         "categoryName":"Hand Tools",
         "brandName":"Ridgid",
         "categoryIdPath":"10167 > 16031 > 16032",
         "categoryNamePath":"Tools & Home Improvement > Power & Hand Tools > Hand Tools",
         "brandId":19970,
         "title":"Pump Gun Only, For Use With 1EDffsad22 Oiler",
         "categoryId":16032,
         "upcs":[

         ],
         "currency":"USD",
         "mpid":"0fb1f23c94c3bc7928ee72f9880a2174",
         "countryCode":"US",
         "lastRecordedAt":1442922991002,
         "storesCount":6,
         "offersCount":10,
         "maxSalePrice":280.75,
         "minSalePrice":128.88
      }
   ]
}

What exactly is stopping you from finishing/continuing writing your code? What kind of help do you need? Are you facing any errors/exceptions/incorrect results? — Pshemo, Sep 24 '15 at 20:29
I copied someone elses code from another post, but couldn't make it work, so i decided to post a sample and ask for help. Below is the link to that post : http://stackoverflow.com/questions/2591098/how-to-parse-json-in-java — LumenIgnis, Sep 24 '15 at 20:40

score 0 · Answer 1 · edited May 23 '17 at 11:59

You could use the Jackson streaming API (http://www.mkyong.com/java/jackson-streaming-api-to-read-and-write-json/) to go walk through your data.

If you really need to upload it through a flatfile (csv?) you could stream the output with a framework like https://commons.apache.org/proper/commons-csv/.

Import CSV file into SQL Server gives more detail on how to upload the csv file.

I can't give any implementation assistance as I don't know where you specifically need help. Think about your problem, what solutions are there and what are the drawbacks for each solution. Pick the simplest one. Split up in subproblems and start solving them one by one. Prove they can work by writing small code samples. Integrate these into working components and write tests that prove each solution works.

Note, too, that there is Jackson CSV format module, `jackson-dataformat-csv` (https://github.com/FasterXML/jackson-dataformat-csv) which can turn POJOs to/from CSV, similar to how default Jackson works with json. — StaxMan, Dec 15 '15 at 03:36

Turn JSON large files into tab delimited flat file

1 Answers1