OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

How can I get JSON from excel file for Nifi using openpyxl?

  • Thread starter Thread starter Pauline Rumyantseva
  • Start date Start date
P

Pauline Rumyantseva

Guest
I am trying to get data from Excel cells. I need only particular columns and rows. I want to put the information from these cells to Nifi. For that I have to convert data I receive from Excel to Json. And I'm stuck with that. I can't use pandas, only openpyxl, because Nifi doesn't work with Jython. I need a string in json format, not json file. And I can't convert the data to json string.

So, I used that code to receive my data:

Code:
sheet = ws1



data = []

for i in range(3, 13):

        filial = sheet.cell(row=i, column=1).value

        kvartal1 = sheet.cell(row=i, column=2).value

        kvartal2 = sheet.cell(row=i, column=3).value

        kvartal3 = sheet.cell(row=i, column=4).value

        kvartal4 = sheet.cell(row=i, column=5).value



        row1 = {filial:kvartal1}

        data.append(row1)

What it gives me is that:

Code:
[{'data1': 0}, {'data2': 0}, {'data3': 0}, {'data4': 0}, {'data5': 0}, {'data6': 0}, {'data7': None}, {'data8': None}, {'data9': None}]

And so on. How can I receive the correct json string or make it correct? Json.dump doesn't work properly too. I need a string in a format like:

Code:
{"data1": 0, "data2": 0, "data3": 0}

Any suggestions? Maybe I'm getting it all wrong and there are other ways to put an excel data in Nifi.

UPD maybe I should convert excel to xml and then load it to Nifi?
<p>I am trying to get data from Excel cells. I need only particular columns and rows. I want to put the information from these cells to Nifi. For that I have to convert data I receive from Excel to Json. And I'm stuck with that. I can't use pandas, only openpyxl, because Nifi doesn't work with Jython. I need a string in json format, not json file. And I can't convert the data to json string.</p>
<p>So, I used that code to receive my data:</p>
<pre><code>

sheet = ws1



data = []

for i in range(3, 13):

filial = sheet.cell(row=i, column=1).value

kvartal1 = sheet.cell(row=i, column=2).value

kvartal2 = sheet.cell(row=i, column=3).value

kvartal3 = sheet.cell(row=i, column=4).value

kvartal4 = sheet.cell(row=i, column=5).value



row1 = {filial:kvartal1}

data.append(row1)
</code></pre>
<p>What it gives me is that:</p>
<pre><code>[{'data1': 0}, {'data2': 0}, {'data3': 0}, {'data4': 0}, {'data5': 0}, {'data6': 0}, {'data7': None}, {'data8': None}, {'data9': None}]
</code></pre>
<p>And so on.
How can I receive the correct json string or make it correct? Json.dump doesn't work properly too.
I need a string in a format like:</p>
<pre><code>{"data1": 0, "data2": 0, "data3": 0}
</code></pre>
<p>Any suggestions? Maybe I'm getting it all wrong and there are other ways to put an excel data in Nifi.</p>
<p>UPD maybe I should convert excel to xml and then load it to Nifi?</p>
 

Latest posts

J
Replies
0
Views
1
Jeff P Chacko
J
Top