+ Reply to Thread
Results 1 to 11 of 11

Parsing Key Value pairs from inconsistent and incomplete data

  1. #1
    Registered User
    Join Date
    05-20-2019
    Location
    London
    MS-Off Ver
    2010
    Posts
    4

    Parsing Key Value pairs from inconsistent and incomplete data

    Hey all,

    I have a CSV file with the following data in rows:

    {'gender': 'male', 'age': '35', 'hometown': 'Chicago', 'car': 'Civic', 'occupation': 'actor', 'salary': '80000'}
    {'gender': 'male', 'age': '32', 'hometown': 'New York', 'occupation': 'banker', 'salary': '130000'}
    {'gender': 'female', 'age': '33', 'hometown': 'Seattle', 'occupation': 'developer'}
    {'age': '27', 'hometown': 'Detroit', 'car': 'Mustang', 'salary': '75000'}

    As you can see, there are recurring attributes (i.e. Gender, Age, Hometown, Car, Occupation, Salary), but this data is complete for every record, making it difficult / impossible to simply separate via Column-To-Test techniques.

    Is there a way to use Excel to parse this data into columns and rows?

    I'd like to use the attributes as column headers, so that when a "value" is missing, it would simply be a blank cell:

    table.jpg


    Thanks in advance!

    excel_lente!

  2. #2
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Parsing Key Value pairs from inconsistent and incomplete data

    Do you only have Excel 2010?

  3. #3
    Registered User
    Join Date
    05-20-2019
    Location
    London
    MS-Off Ver
    2010
    Posts
    4

    Re: Parsing Key Value pairs from inconsistent and incomplete data

    It's what we currently use at my company; is there some functionality in more recent Excel versions that could help with this task?

  4. #4
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Parsing Key Value pairs from inconsistent and incomplete data

    Check out the software requirements for Power Query. I'm sure that would help you.

    https://www.microsoft.com/en-au/down....aspx?id=39379

    EDIT - That's not to say you can't do what you want with 2010.
    Last edited by kersplash; 05-21-2019 at 12:49 AM.

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,178

    Re: Parsing Key Value pairs from inconsistent and incomplete data

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Read this to understand why we (and other sites like us) consider this to be important.

    As you arenew here I will add the link myself https://www.mrexcel.com/forum/excel-...lete-data.html

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: Parsing Key Value pairs from inconsistent and incomplete data

    Is that all there is in the file?

    No header row or other information?
    If posting code please use code tags, see here.

  7. #7
    Registered User
    Join Date
    05-20-2019
    Location
    London
    MS-Off Ver
    2010
    Posts
    4

    Re: Parsing Key Value pairs from inconsistent and incomplete data

    Yes, the header is "extracted information", and contains rows of structured yet inconsistent data.

    To moderators: My apologies for the cross-posted link, I will be sure to avoid this in future.

  8. #8
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    12,178

    Re: Parsing Key Value pairs from inconsistent and incomplete data

    If you have read the link provided (which you really should do) you will see that cross posting is not forbidden, but links to cross posts MUST be added.

  9. #9
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Parsing Key Value pairs from inconsistent and incomplete data

    Hi

    Suppose your header in A1:F1 and data in P2:Pn

    Use in H2:Hn the value 1 and for I2:In the formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Use this formula in A2 and copy them to Fn
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    See the file for clarification
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    05-20-2019
    Location
    London
    MS-Off Ver
    2010
    Posts
    4

    Re: Parsing Key Value pairs from inconsistent and incomplete data

    That's amazing, thanks so much, Jose!

  11. #11
    Forum Expert Josť Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Parsing Key Value pairs from inconsistent and incomplete data

    You are welcome.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. convert the sweepstakes to their palindrome pairs and identify the twin pairs.
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-19-2019, 07:59 AM
  2. [SOLVED] Parsing out data that is inconsistent in length
    By zookeepertx in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-01-2018, 02:27 PM
  3. Parsing A Number from An Inconsistent Note
    By ibillings00 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-12-2012, 11:45 PM
  4. Line up set of data when one is incomplete?
    By kelemvor in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2011, 02:11 PM
  5. Vlookup on incomplete data
    By arasan25 in forum Excel General
    Replies: 9
    Last Post: 03-31-2011, 02:16 AM
  6. Parsing inconsistent text strings into columns in an organized manner
    By jagman1990 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-17-2010, 03:48 PM
  7. appending incomplete data
    By abuchan in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-18-2010, 01:43 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1