1

For example, I have created an Excel file named “records.xlsx”

My Excel Data

Car Name   Price   Count
Honda       100     1
Tata        150     10
GMR         200      5

And I am importing it like this

import excel2json

excel2json.convert_from_file('records.xlsx')

Which is converting my excel file to following JSON:

[
    {
        "Car Name": "Honda",
        "Price": "100",
        "Count": "1"
    },
    {
        "Car Name": "Tata",
        "Price": "150",
        "Count": "10"
    },
    {
        "Car Name": "GMR",
        "Price": "150",
        "Count": "5"
    }
]

I don't want the Price column in my resulting JSON. How can I convert this Excel file to JSON directly such that only columns Car Name and Count will appear in resulting JSON?

Alexander Rossa
  • 1,900
  • 1
  • 22
  • 37
Newton8989
  • 300
  • 1
  • 4
  • 22
  • Does this answer your question? [How can I quickly and easily convert spreadsheet data to JSON?](https://stackoverflow.com/questions/19187085/how-can-i-quickly-and-easily-convert-spreadsheet-data-to-json) – snack_overflow Dec 18 '19 at 11:29
  • no actually am asking for suggestions in python script – Newton8989 Dec 18 '19 at 11:31
  • https://stackoverflow.com/a/19167546/495157 - this answers the dropping part if you map whole thing to begin with – JGFMK Dec 18 '19 at 12:10
  • @JGFMK Thanks for this but Alexander Rossa Already suggested this to me and i don't want to convert whole excel to json.(kindly please read comments on this answer thanks ) – Newton8989 Dec 18 '19 at 12:26

1 Answers1

1

The library that you are using doesn't seem to provide option to exclude column when loading it. I think the easiest thing for you to do would be to filter the unwanted column after you load the JSON.

your_json = excel2json.convert_from_file('records.xlsx')
for object in your_json:
    del object['Price']

You will be left with your_json without the Price entries.

Alexander Rossa
  • 1,900
  • 1
  • 22
  • 37
  • Hi am working with cloud functions so short on memory thats why i don't want to convert whole sheet to json in the first place itself is there any other method to convert excel sheet to json for specific columns. – Newton8989 Dec 18 '19 at 11:42
  • I guess you could try to open the file using something like `xlrd` and try to parse it into JSON yourself but I am not sure of the memory savings that would give you. Had a quick look for a tutorial for that, [this one](https://medium.com/coinmonks/parsing-a-spreadsheet-into-a-json-file-using-python-6118f5c70bd3) could give you an idea. Scroll down to "Parsing an Excel file" header. – Alexander Rossa Dec 18 '19 at 11:50