0

I am having a file having 100 lines in below format.

{ "type": "Feature", "properties": { "id":"01","score":"10000","Name": "ABC", "description": "<html xmlns:fo=\"http:\/\/www.w3.org\/1999\/XSL\/Format\" xmlns:msxsl=\"urn:schemas-microsoft-com:xslt\">\n\n<head>\n\n<META http-equiv=\"Content-Type\" content=\"text\/html\">\n\n<meta http-equiv=\"content-type\" content=\"text\/html; charset=UTF-8\">\n\n<\/head>\n\n<body style=\"margin:0px 0px 0px 0px;overflow:auto;background:#FFFFFF;\">\n\n<table style=\"font-family:Arial,Verdana,Times;font-size:12px;text-align:left;width:100%;border-collapse:collapse;padding:3px 3px 3px 3px\">\n\n<tr style=\"text-align:center;font-weight:bold;background:#9CBCE2\">\n\n<td>PASIR PANJANG 1<\/td>\n\n<\/tr>\n\n<tr>\n\n<td>\n\n<table style=\"font-family:Arial,Verdana,Times;font-size:12px;text-align:left;width:100%;border-spacing:0px; padding:3px 3px 3px 3px\">\n\n<tr>\n\n<td>SHAPE<\/td>\n\n<td>Polygon<\/td>\n\n<\/tr>\n\n<tr bgcolor=\"#D4E4F3\">\n\n<td>Subzone Number<\/td>\n\n<td>13<\/td>\n\n<\/tr>\n\n<tr>\n\n<td>Subzone Name<\/td>\n\n<td>ABC 1<\/td>\n\n<\/tr>\n\n<tr bgcolor=\"#D4E4F3\">\n\n<td>Subzone Code<\/td>\n\n<td>QTSZ13<\/td>\n\n<\/tr>\n\n<tr>\n\n<td>Central Area Indicator<\/td>\n\n<td>N<\/td>\n\n<\/tr>\n\n<tr bgcolor=\"#D4E4F3\">\n\n<td>Planning Area Name<\/td>\n\n<td>QUEENSTOWN<\/td>\n\n<\/tr>\n\n<tr>\n\n<td>Planning Area Code<\/td>\n\n<td>QT<\/td>\n\n<\/tr>\n\n<tr bgcolor=\"#D4E4F3\">\n\n<td>Region Name<\/td>\n\n<td>CENTRAL REGION<\/td>\n\n<\/tr>\n\n<tr>\n\n<td>Region Code<\/td>\n\n<td>CR<\/td>\n\n<\/tr>\n\n<tr bgcolor=\"#D4E4F3\">\n\n<td>INC_CRC<\/td>\n\n<td>1F721290C421BFAB<\/td>\n\n<\/tr>\n\n<tr>\n\n<td>FMEL_UPD_D<\/td>\n\n<td>12\/5\/2014 9:26:20 PM<\/td>\n\n<\/tr>\n\n<tr bgcolor=\"#D4E4F3\">\n\n<td>X_ADDR<\/td>\n\n<td>22077.3383<\/td>\n\n<\/tr>\n\n<tr>\n\n<td>Y_ADDR<\/td>\n\n<td>29893.7812<\/td>\n\n<\/tr>\n\n<tr bgcolor=\"#D4E4F3\">\n\n<td>SHAPE_Length<\/td>\n\n<td>6571.323082<\/td>\n\n<\/tr>\n\n<tr>\n\n<td>SHAPE_Area<\/td>\n\n<td>1084792.320587<\/td>\n\n<\/tr>\n\n<\/table>\n\n<\/td>\n\n<\/tr>\n\n<\/table>\n\n<\/body>\n\n<\/html>\n\n"}, "geometry": { "type": "MultiPolygon", "coordinates": [ [ [ [ 103.770389070495796, 1.292487855396017 ], [ 103.770414424305599, 1.292574882101212 ], [ 103.7701466034909, 1.29263278185594 ], [ 103.770073030489101, 1.292648696824341 ], [ 103.769991818022206, 1.292739600582147 ] ] ] ] } },

... .. .. From this I need to convert each line to

01,ABC,"POLYGON((103.770389070495796, 1.292487855396017 ,  103.770414424305599, 1.292574882101212 ,  103.7701466034909, 1.29263278185594 ,  103.770073030489101, 1.292648696824341 ,  103.769991818022206, 1.292739600582147))"

and so on.

Can it be done in awk or sed?

  • 1
    First of all this is not CSV but JSON. Secondly, blindly relying on relative positioning in data you don't know is most unreliable. And finally, this is not a free script writing service and (as per the terms) you are required to show a minimum effort of solving the question on your own. – Julie Pelletier May 23 '17 at 6:43
2

Another solution using jq:

jq -j '.properties.id, ",",
    .properties.Name, ",\"POLYGON((",
    (.geometry.coordinates | flatten | map(tostring) | join(",")), "))\"\n"' < file.json

Output:

01,ABC,"POLYGON((103.770389070496,1.29248785539602,103.770414424306,1.29257488210121,103.770146603491,1.29263278185594,103.770073030489,1.29264869682434,103.769991818022,1.29273960058215))"

Edit: fiddle 1, fiddle 2

  • I tried your way but it throws an compiler error. Could you please check this snippet – John May 24 '17 at 4:19
  • Are u able to share any fiddle or pen so it will be helpful to identify my error? – John May 24 '17 at 4:35
  • @John Edited, this should now work with jq 1.5. – Satō Katsura May 24 '17 at 4:53
4

To process JSON, use a JSON tool, e.g. jq (normally available is a package in your distro):

jq -j '.properties.id, ",", .([ .geometry.coordinates | recurse | numbers | tostring] | join(",")), "))\n"' < your_file

does what you want for a single line. You may need to feed it each line separately, depending on how your file actually looks like.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.