Unix/Linux Go Back    


UNIX for Dummies Questions & Answers This forum is closed for new posts. Please post beginner questions to learn unix and learn linux in this forum UNIX for Beginners Questions & Answers

Converting unstructured data to structured data

UNIX for Dummies Questions & Answers


 
 
Thread Tools Search this Thread Display Modes
    #1  
Old Unix and Linux 06-05-2016
naveen.kuppili naveen.kuppili is offline
Registered User
 
Join Date: May 2009
Last Activity: 7 June 2016, 12:21 AM EDT
Posts: 20
Thanks: 3
Thanked 0 Times in 0 Posts
Converting unstructured data to structured data

Hi,

Can someone help in converting the below unstructured data to a CSV format please.


Code:
{
  "branchId" : "BNSFGDJNSJG-73264HB-132131BNHJFSDG",
  "branchName" : "NEWYORK-SSDF",
  "branchProductId" : "72Y5HFHSF7H3RUNAWEF",
  "PreferenceId" : "BASDBVcbzcYHcb",
  "emailId" : "hjbasf7hh4aBSDFU@mymail.com",
  "firstName" : "FNM",
  "lastName" : "LNM",
  "middleName" : "",
  "userId" : "LSDFJDSJMDFG238487ybsgfd"
}

Output required:


Code:
branchId,branchName,branchProductId,PreferenceId,emailId,firstName,lastName,middleName,userId
"BNSFGDJNSJG-73264HB-132131BNHJFSDG","NEWYORK-SSDF","72Y5HFHSF7H3RUNAWEF","BASDBVcbzcYHcb", "hjbasf7hh4aBSDFU@mymail.com", "FNM","LNM","","LSDFJDSJMDFG238487ybsgfd"

Thanks in advance
Naveen

Last edited by Don Cragun; 06-06-2016 at 12:29 AM.. Reason: Add CODE tags.
Sponsored Links
    #2  
Old Unix and Linux 06-05-2016
Aia's Unix or Linux Image
Aia Aia is offline
Registered User
 
Join Date: May 2008
Last Activity: 18 October 2017, 9:58 AM EDT
Posts: 1,645
Thanks: 46
Thanked 626 Times in 587 Posts
Would this work?

Code:
perl -ne '@c=/(\w+)"\s+:\s+("[^"]+")/ and push @h, $c[0] and push @r, $c[1]; END{$"=","; print "@h\n@r\n"}' naveen.file

Output:

Code:
branchId,branchName,branchProductId,PreferenceId,emailId,firstName,lastName,userId
"BNSFGDJNSJG-73264HB-132131BNHJFSDG","NEWYORK-SSDF","72Y5HFHSF7H3RUNAWEF","BASDBVcbzcYHcb","hjbasf7hh4aBSDFU@mymail.com","FNM","LNM","LSDFJDSJMDFG238487ybsgfd"

The Following User Says Thank You to Aia For This Useful Post:
naveen.kuppili (06-05-2016)
Sponsored Links
    #3  
Old Unix and Linux 06-05-2016
naveen.kuppili naveen.kuppili is offline
Registered User
 
Join Date: May 2009
Last Activity: 7 June 2016, 12:21 AM EDT
Posts: 20
Thanks: 3
Thanked 0 Times in 0 Posts
Quote:
Originally Posted by Aia View Post
Would this work?

Code:
perl -ne '@c=/(\w+)"\s+:\s+("[^"]+")/ and push @h, $c[0] and push @r, $c[1]; END{$"=","; print "@h\n@r\n"}' naveen.file

Output:

Code:
branchId,branchName,branchProductId,PreferenceId,emailId,firstName,lastName,userId
"BNSFGDJNSJG-73264HB-132131BNHJFSDG","NEWYORK-SSDF","72Y5HFHSF7H3RUNAWEF","BASDBVcbzcYHcb","hjbasf7hh4aBSDFU@mymail.com","FNM","LNM","LSDFJDSJMDFG238487ybsgfd"

Works like a treat, thanks Aia Linux but why is the "middleName" field is missed ? is that because it doesn't contain any value.
    #4  
Old Unix and Linux 06-06-2016
junior-helper junior-helper is offline
Registered User
 
Join Date: Aug 2014
Last Activity: 28 June 2017, 4:04 PM EDT
Posts: 344
Thanks: 37
Thanked 126 Times in 114 Posts

Code:
#!/bin/bash

header=(); values=()

while IFS=":" read var val
do
  if [[ "$var" =~ "\"" ]]; then

  var="${var//\"/}"; var="${var% }"
   [[ "${#header[@]}" -eq "0" ]] && header=("$var") || header=("${header[@]}","$var")

  val="${val# }"; val="${val%,}"
   [[ "${#values[@]}" -eq "0" ]] && values=("$val") || values=("${values[@]}","$val")

  fi

done <inputfile

printf "%s\n%s\n" "${header[@]}" "${values[@]}"

The Following User Says Thank You to junior-helper For This Useful Post:
naveen.kuppili (06-06-2016)
Sponsored Links
    #5  
Old Unix and Linux 06-06-2016
naveen.kuppili naveen.kuppili is offline
Registered User
 
Join Date: May 2009
Last Activity: 7 June 2016, 12:21 AM EDT
Posts: 20
Thanks: 3
Thanked 0 Times in 0 Posts
Quote:
Originally Posted by junior-helper View Post
Code:
#!/bin/bash

header=(); values=()

while IFS=":" read var val
do
  if [[ "$var" =~ "\"" ]]; then

  var="${var//\"/}"; var="${var% }"
   [[ "${#header[@]}" -eq "0" ]] && header=("$var") || header=("${header[@]}","$var")

  val="${val# }"; val="${val%,}"
   [[ "${#values[@]}" -eq "0" ]] && values=("$val") || values=("${values[@]}","$val")

  fi

done <inputfile

printf "%s\n%s\n" "${header[@]}" "${values[@]}"

Thanks junior-helper. It works Linux
Sponsored Links
    #6  
Old Unix and Linux 06-06-2016
Aia's Unix or Linux Image
Aia Aia is offline
Registered User
 
Join Date: May 2008
Last Activity: 18 October 2017, 9:58 AM EDT
Posts: 1,645
Thanks: 46
Thanked 626 Times in 587 Posts
Quote:
Originally Posted by naveen.kuppili View Post
Works like a treat, thanks Aia Linux but why is the "middleName" field is missed ? is that because it doesn't contain any value.
It is because I did not pay enough attention. Apologies.
Please, try the following:

Code:
perl -ne '@c=/(\w+)"\s+:\s+("[^"]*")/ and push @h, $c[0] and push @r, $c[1]; END{$"=","; print "@h\n@r\n"}' naveen.file


Output:


Code:
branchId,branchName,branchProductId,PreferenceId,emailId,firstName,lastName,middleName,userId
"BNSFGDJNSJG-73264HB-132131BNHJFSDG","NEWYORK-SSDF","72Y5HFHSF7H3RUNAWEF","BASDBVcbzcYHcb","hjbasf7hh4aBSDFU@mymail.com","FNM","LNM","","LSDFJDSJMDFG238487ybsgfd"


Last edited by Aia; 06-06-2016 at 12:43 AM..
The Following User Says Thank You to Aia For This Useful Post:
naveen.kuppili (06-07-2016)
Sponsored Links
    #7  
Old Unix and Linux 06-07-2016
naveen.kuppili naveen.kuppili is offline
Registered User
 
Join Date: May 2009
Last Activity: 7 June 2016, 12:21 AM EDT
Posts: 20
Thanks: 3
Thanked 0 Times in 0 Posts
Quote:
Originally Posted by Aia View Post
It is because I did not pay enough attention. Apologies.
Please, try the following:

Code:
perl -ne '@c=/(\w+)"\s+:\s+("[^"]*")/ and push @h, $c[0] and push @r, $c[1]; END{$"=","; print "@h\n@r\n"}' naveen.file


Output:


Code:
branchId,branchName,branchProductId,PreferenceId,emailId,firstName,lastName,middleName,userId
"BNSFGDJNSJG-73264HB-132131BNHJFSDG","NEWYORK-SSDF","72Y5HFHSF7H3RUNAWEF","BASDBVcbzcYHcb","hjbasf7hh4aBSDFU@mymail.com","FNM","LNM","","LSDFJDSJMDFG238487ybsgfd"

Thanks Aia. Also, if we have two or more sets of data instead of one as mentioned below


Code:
{
  "branchId" : "BNSFGDJNSJG-73264HB-132131BNHJFSDG",
  "branchName" : "NEWYORK-SSDF",
  "branchProductId" : "72Y5HFHSF7H3RUNAWEF",
  "PreferenceId" : "BASDBVcbzcYHcb",
  "emailId" : "hjbasf7hh4aBSDFU@mymail.com",
  "firstName" : "FNM",
  "lastName" : "LNM",
  "middleName" : "",
  "userId" : "LSDFJDSJMDFG238487ybsgfd"
}

{
  "branchId" : "BNSFGDJNSJG-73264HB-132131BNHJFSDG",
  "branchName" : "NEWYORK-SSDF",
  "branchProductId" : "72Y5HFHSF7H3RUNAWEF",
  "userId" : "LSDFJDSJMDFG238487ybsgfd"
}

And what changes are required to get the output as below


Code:
branchId,branchName,branchProductId,PreferenceId,emailId,firstName,lastName,middleName,userId
"BNSFGDJNSJG-73264HB-132131BNHJFSDG","NEWYORK-SSDF","72Y5HFHSF7H3RUNAWEF","BASDBVcbzcYHcb", "hjbasf7hh4aBSDFU@mymail.com", "FNM","LNM","","LSDFJDSJMDFG238487ybsgfd"

branchId,branchName,branchProductId,userId
"BNSFGDJNSJG-73264HB-132131BNHJFSDG","NEWYORK-SSDF","72Y5HFHSF7H3RUNAWEF","LSDFJDSJMDFG238487ybsgfd"

Sponsored Links
 

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Linux More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
[SOLVED] Converting data from one format to the other newbie83 Shell Programming and Scripting 9 12-11-2012 02:36 PM
Converting variable space width data into CSV data in bash vharsha Shell Programming and Scripting 4 04-25-2012 04:41 AM
Help with Converting UTF-8 data to Unicode arunbs Shell Programming and Scripting 2 01-12-2012 05:28 PM
Extract data based on match against one column data from a long list data patrick87 Shell Programming and Scripting 12 11-17-2009 03:27 AM
converting a tabular format data to comma seperated data in KSH Hemamalini UNIX for Dummies Questions & Answers 2 06-16-2008 04:37 AM



All times are GMT -4. The time now is 06:30 AM.