# Excel file to mySQL

**URL:** <https://discourse.nodered.org/t/excel-file-to-mysql/30283>\
**Category:** General\
**Created:** [18 July 2020 05:40 UTC](https://discourse.nodered.org/t/excel-file-to-mysql/30283 "2020-07-18T05:40:11Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![AK51](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ak51/32/8438_2.png) [@AK51](https://discourse.nodered.org/u/AK51)\
**Post date:** [18 July 2020 05:40 UTC](https://discourse.nodered.org/t/excel-file-to-mysql/30283/1 "2020-07-18T05:40:11Z")

</div>

Hi, I have an excel file (1000 rows), I can use spreadsheet-in to convert the data in one whole json. Is it feasible to insert 1000 rows into the mysql table?  
If not, I am looking for a method to read the excel one row at the time, but no luck.  
Also, is there a way to convert excel to csv? If yes, I think I can read csv line by line. Then, I can insert each row to mySQL.

Thanks.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [18 July 2020 10:10 UTC](https://discourse.nodered.org/t/excel-file-to-mysql/30283/2 "2020-07-18T10:10:02Z")

</div>

> [@AK51](#):
>
> Is it feasible to insert 1000 rows into the mysql table?

Maybe not the most efficient way but yes, you could feed the converted data through a split node to generate individual messages then through a function or template node to generate a SQL insert statement, then send that (in msg.topic) to a mysql node.

---

<div class="post-metadata">

**Author:** ![janvda](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/janvda/32/234_2.png) [@janvda](https://discourse.nodered.org/u/janvda)\
**Post date:** [18 July 2020 11:48 UTC](https://discourse.nodered.org/t/excel-file-to-mysql/30283/3 "2020-07-18T11:48:48Z")

</div>

> [@AK51](#):
>
> Is it feasible to insert 1000 rows into the mysql table?

I have not yet done so, but I would expect that it is feasible to insert 1000 rows in a single mysql statement.

To do so, I would direct the json output of the `node-red-contrib-spreadsheet-in` to a change node that has a jsonata query that is creating your [multiple record sql insert statement](https://www.mysqltutorial.org/mysql-insert-multiple-rows/)

Take care that `node-red-contrib-spreadsheet-in` might produce a "sparse array" in case the excel has blank cells and a "sparse array" is not proper json (so this might give unexpected results when using jsonata query). You can convert it to proper json by directing the output to 2 json nodes in series. For more details see also this topic: [Weird behaviour when processing an array of arrays having null values](https://discourse.nodered.org/t/weird-behaviour-when-processing-an-array-of-arrays-having-null-values/28615)

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [18 July 2020 12:12 UTC](https://discourse.nodered.org/t/excel-file-to-mysql/30283/4 "2020-07-18T12:12:14Z")

</div>

Much better to feed a bulk insert if you can . If not, use a prepared statement as that will be vastly more efficient.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [18 July 2020 13:21 UTC](https://discourse.nodered.org/t/excel-file-to-mysql/30283/5 "2020-07-18T13:21:41Z")

</div>

Agreed - but i am not familiar with the capabilities of the MySQL node.

If it offers bulk insert or stored proc + params then definitely go with that.

My offering was an "off the top of my head - this should work" kinda lazy solution 🙂

---

<div class="post-metadata">

**Author:** ![AK51](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/ak51/32/8438_2.png) [@AK51](https://discourse.nodered.org/u/AK51)\
**Post date:** [18 July 2020 15:46 UTC](https://discourse.nodered.org/t/excel-file-to-mysql/30283/6 "2020-07-18T15:46:14Z")

</div>

Thx, Insert 1000 row to mySQL works.  
I have figured it out the easy way.  
Use spreadsheet-in to get the json, use json-to-csv convrter block to save the data in csv.  
It is easier to clean up and setup the topic for mySQL insert.  
Thx

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [16 September 2020 15:46 UTC](https://discourse.nodered.org/t/excel-file-to-mysql/30283/7 "2020-09-16T15:46:30Z")

</div>

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.