Nested objects create empty boilerplate code

juliwe commented 6 years ago

When using nested fields like:

furtherInformation.icon	furtherInformation.description	button.title	button.link

excel-as-json creates:

{
    "furtherInformation": {},
    "button": {}
  },
  {
    "furtherInformation": {},
    "button": {}
  },
  {
    "furtherInformation": {},
    "button": {}
  },
  {
    "furtherInformation": {},
    "button": {}
  },
  {
    "furtherInformation": {},
    "button": {}
  },
  {
    "furtherInformation": {},
    "button": {}
  }

Please advise/fix this unnecessary code. 👍

stevetarver commented 6 years ago

Apologies for the dreadful response time.

I am unable to recreate this problem. I setup an excel sheet containing your values in row 1 and added the following test:

describe 'regression 28', ->

  it 'should produce an empty array when no value rows are provided', (done) ->
    options =
      sheet: 2
      isColOriented: false
      omitEmptyFields: false

    processFile RGR_SRC_XLSX, RGR28_OUT_JSON, options, (err, data) ->
      expect(err).to.be.an 'undefined'
      expect(data).to.be.an('array').with.lengthOf(0)
      console.dir(data)
      done()

succeeds and prints []

If this is still an interesting bug for you, I will need an example spreadsheet and code that reproduces the error to investigate further.

stevetarver commented 6 years ago

No steps to reproduce - can't reproduce bug from description. Closing

Trimud commented 6 years ago

I can reproduce it when I have created XLS file using Google Sheets.

Had to install Crop Sheet add-on to Google Sheets and remove all empty rows and columns.

stevetarver commented 6 years ago

Can you attach your spreadsheet and minimal code to reproduce the error? I tried to recreate your bug (regression test and spreadsheet is in this repo), but could not.

Other thoughts: Did you update to the latest version of excel-as-json?

Please describe your env: OS? Node.js version? excel-as-json version? Anything else that makes your env unique?

Bug reporting guidelines are here: https://github.com/stevetarver/excel-as-json/blob/master/README.md#bug-reports

Trimud commented 6 years ago

Hello,

I'm using Mac with MacOS High Sierra 10.13.6 Node.js - v7.10.1 excel-as-json is the latest one

Here is the file I use: lvl2.xlsx

stevetarver commented 6 years ago

Thanks @Trimud, the spreadsheet clarifies a lot - likely the same problem @juliwe was seeing.

Your spreadsheet is 'row-oriented'; key names are in row 1 and the remainder are data rows. Using processFile(), the first three rows come out as expected and you have 10 blank objects containing only keys following that.

Your excel spreadsheet has 10 rows with blank text following the data rows. You can clearly see this by adding a console.log in excel-as-json.coffee after the excel package reads the data.

    excel src, options.sheet, (err, data) ->
      console.log(data)

See output below.

The excel package I use returns data for all defined rows and columns. Spreadsheets will write files containing a table sized by right-most column and bottom-most row edited. They may have a default "smallest sheet" that defines minimum output table. They usually don't delete rows or columns without data.

This is really tedious - I should help the user out and optionally pre-scan excel package output and delete rows / cols that contain only ''.

Realistically, I will not get to this feature soon. I am leaving it open as a feature request.

In the mean time, this article provides a hint at identifying blank cells: https://helpdeskgeek.com/office-tips/delete-blank-lines-in-excel/

Open spreadsheet
Open "Go To" dialog (press F5 on my mac)
This highlights all cells that have blank values
Select only the blank rows, and then delete them and save the spreadsheet

Output from your xlsx. Note the blank rows at the bottom.

[ [ 'name',
    'categories[0].name',
    'categories[1].name',
    'categories[2].name',
    'categories[3].name',
    'categories[0].cgid',
    'categories[1].cgid',
    'categories[2].cgid',
    'categories[3].cgid',
    'categories[0].url',
    'categories[1].url',
    'categories[2].url',
    'categories[3].url' ],
  [ 'Shop by Age',
    'Birth to 2 Years',
    '2 to 3 years',
    '3 to 4 years',
    'Shop all',
    'Early_Years:Shop_by_Room:Shop_by_Room_Birth_2_years',
    'Early_Years:Shop_by_Room:Shop_by_Room_2_3_years',
    'Early_Years:Shop_by_Room:Shop_by_Room_3_4_years',
    'Early_Years:Shop_by_Room',
    '',
    '',
    '',
    '' ],
  [ 'Product Collections',
    'STEM',
    'Art Essentials',
    'PE Best Sellers',
    'Humanities Top Picks',
    '',
    '',
    '',
    '',
    'https://google.com/',
    'https://google.com/',
    'https://google.com/',
    'https://google.com/' ],
  [ 'Product Collections',
    'Pisces Top Sellers',
    'ICT Top Picks',
    'Languages Favourites',
    '',
    '',
    '',
    '',
    '',
    '',
    '',
    '',
    '' ],
  [ '', '', '', '', '', '', '', '', '', '', '', '', '' ],
  [ '', '', '', '', '', '', '', '', '', '', '', '', '' ],
  [ '', '', '', '', '', '', '', '', '', '', '', '', '' ],
  [ '', '', '', '', '', '', '', '', '', '', '', '', '' ],
  [ '', '', '', '', '', '', '', '', '', '', '', '', '' ],
  [ '', '', '', '', '', '', '', '', '', '', '', '', '' ],
  [ '', '', '', '', '', '', '', '', '', '', '', '', '' ],
  [ '', '', '', '', '', '', '', '', '', '', '', '', '' ],
  [ '', '', '', '', '', '', '', '', '', '', '', '', '' ],
  [ '', '', '', '', '', '', '', '', '', '', '', '', '' ] ]

stevetarver / excel-as-json

Nested objects create empty boilerplate code #28