# Converting CSV to mysql causes error with blank csv columns
**URL:** https://discourse.nodered.org/t/converting-csv-to-mysql-causes-error-with-blank-csv-columns/50632
**Category:** General
**Created:** [4 September 2021 22:57 UTC](https://discourse.nodered.org/t/converting-csv-to-mysql-causes-error-with-blank-csv-columns/50632 "2021-09-04T22:57:58Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@HarryPottar](https://discourse.nodered.org/u/HarryPottar)
#### Post date: [4 September 2021 22:57 UTC](https://discourse.nodered.org/t/converting-csv-to-mysql-causes-error-with-blank-csv-columns/50632/1 "2021-09-04T22:57:58Z")
I know this is an old thread, but it has helped me a lot.
I have a large csv file, reading it in and writing to MySQL.
The flow posted works except when I get to empty values, if I don’t set include empty values in csv node all works except I get wrong values when empty string encountered. If I set include empty string node red crashes assuming running out of memory.
Thanks harry
---
### Author:  [@zenofmud](https://discourse.nodered.org/u/zenofmud)
#### Post date: [5 September 2021 11:22 UTC](https://discourse.nodered.org/t/converting-csv-to-mysql-causes-error-with-blank-csv-columns/50632/2 "2021-09-05T11:22:29Z")
I've moved this from [Memory issues processing and writing a large file - #6](https://discourse.nodered.org/t/memory-issues-processing-and-writing-a-large-file/17356/6) since that topic was over 2 years old and close it.
can you provide a simple version of your flow causing the issue with some sample data?
---
### Author:  [@HarryPottar](https://discourse.nodered.org/u/HarryPottar)
#### Post date: [6 September 2021 02:10 UTC](https://discourse.nodered.org/t/converting-csv-to-mysql-causes-error-with-blank-csv-columns/50632/3 "2021-09-06T02:10:54Z")
I think I found the solution;
FYI, I wanted to paste more graphics & links but as a new user I was limited to 2.
For context the original [thread](https://discourse.nodered.org/t/memory-issues-processing-and-writing-a-large-file/17356) eoliverio flow was a huge help.
**eoliverio flow**
```auto
[{"id":"df914794.c9f6e8","type":"file in","z":"2f489ad1.76f266","name":"","filename":"D:\\\\input.csv","format":"lines","chunk":false,"sendError":false,"encoding":"utf8","x":250,"y":460,"wires":[["328ac413.acc99c"]]},{"id":"8df35fa2.dbc47","type":"inject","z":"2f489ad1.76f266","name":"","topic":"","payload":"","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":190,"y":380,"wires":[["66f0794c.570df8"]]},{"id":"570fcc0b.84ea84","type":"xml","z":"2f489ad1.76f266","name":"","property":"payload","attr":"","chr":"","x":510,"y":640,"wires":[["6c9c1e41.ae2d6"]]},{"id":"799a9147.53447","type":"file","z":"2f489ad1.76f266","name":"","filename":"D:\\\\output.xml","appendNewline":true,"createDir":false,"overwriteFile":"false","encoding":"none","x":920,"y":640,"wires":[["7108c578.37529c"]]},{"id":"6c9c1e41.ae2d6","type":"function","z":"2f489ad1.76f266","name":"remove XML header","func":"var xmlheader = '';\nmsg.payload = msg.payload.replace(xmlheader, '');\n\nreturn msg;","outputs":1,"noerr":0,"x":700,"y":640,"wires":[["799a9147.53447"]]},{"id":"a36b9cf8.344aa","type":"function","z":"2f489ad1.76f266","name":"... processing ...","func":"// do stuff using objects from CSV data\n\nvar payload = {\n 'data' : msg.payload\n}\nmsg.payload = payload;\n\nreturn msg;","outputs":1,"noerr":0,"x":340,"y":640,"wires":[["570fcc0b.84ea84"]]},{"id":"328ac413.acc99c","type":"function","z":"2f489ad1.76f266","name":"prepend columns","func":"if (msg.parts.index === 0) {\n flow.set('columns', msg.payload);\n return;\n}\n\nmsg.payload = flow.get('columns') + '\\r\\n' + msg.payload;\n\nreturn msg;","outputs":1,"noerr":0,"x":450,"y":460,"wires":[["74db8414.0aee8c"]]},{"id":"73a1bfc5.0dbdf","type":"change","z":"2f489ad1.76f266","name":"parts -> temp","rules":[{"t":"set","p":"temp","pt":"msg","to":"parts","tot":"msg"},{"t":"delete","p":"parts","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":290,"y":560,"wires":[["51b5ec4f.137694"]]},{"id":"526fe89.1624a18","type":"change","z":"2f489ad1.76f266","name":"temp -> parts","rules":[{"t":"set","p":"parts","pt":"msg","to":"temp","tot":"msg"},{"t":"delete","p":"temp","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":610,"y":560,"wires":[["a36b9cf8.344aa"]]},{"id":"51b5ec4f.137694","type":"csv","z":"2f489ad1.76f266","name":"","sep":",","hdrin":true,"hdrout":"","multi":"one","ret":"\\n","temp":"","skip":"0","strings":false,"x":450,"y":560,"wires":[["526fe89.1624a18"]]},{"id":"66f0794c.570df8","type":"change","z":"2f489ad1.76f266","name":"add XML header","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":360,"y":380,"wires":[["a8f14a7e.c64508"]]},{"id":"a8f14a7e.c64508","type":"file","z":"2f489ad1.76f266","name":"","filename":"D:\\\\output.xml","appendNewline":true,"createDir":false,"overwriteFile":"true","encoding":"none","x":560,"y":380,"wires":[["df914794.c9f6e8"]]},{"id":"cd5bad53.87abc","type":"file","z":"2f489ad1.76f266","name":"","filename":"D:\\\\output.xml","appendNewline":false,"createDir":false,"overwriteFile":"false","encoding":"utf8","x":840,"y":720,"wires":[[]]},{"id":"2bb5a351.28925c","type":"change","z":"2f489ad1.76f266","name":"add XML footer","rules":[{"t":"set","p":"payload","pt":"msg","to":"","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":640,"y":720,"wires":[["cd5bad53.87abc"]]},{"id":"7108c578.37529c","type":"switch","z":"2f489ad1.76f266","name":"msg.complete is not null","property":"complete","propertyType":"msg","rules":[{"t":"nnull"}],"checkall":"true","repair":false,"outputs":1,"x":410,"y":720,"wires":[["2bb5a351.28925c"]]},{"id":"450cfa3.d6f2a04","type":"comment","z":"2f489ad1.76f266","name":"save and restore msg.parts because CSV node uses it","info":"","x":420,"y":520,"wires":[]},{"id":"74db8414.0aee8c","type":"function","z":"2f489ad1.76f266","name":"set msg.complete to last item","func":"if (msg.parts.index + 1 === msg.parts.count) {\n msg.complete = true;\n}\n\nreturn msg;","outputs":1,"noerr":0,"x":700,"y":460,"wires":[["73a1bfc5.0dbdf"]]}]
```
**Now my issue;**
I had huge csv files, it was a parts catalogue, made up of 3 different csv files.
All csv files were million plus rows and some had 829 columns.
I needed to build a database so I could combine the data for each csv for a relational look up later.
**Some Facts:**
The csv files are from a big industry leading appliance manufacture, the csv files where "as is" you either used them or you did not. No going back asking for different format.
I had to use Node-Red client insisting on this.
Node-Red was being used with a webhook to provide part-number lookup responses to the clients system.
This example file had 829 columns, I only needed two, the Part Number and the Brand.
Issue, with the csv module, I needed to enable "include null values" however because you end up with 829 columns it would crash Node-Red.
**So I made a CSV Clean up flow.**
First node is a Watch (Hotfolder); inside that folder is a **Complete** , **Error** folder and a nested Watch folder call **Process**.
After the flow is done it drops the cleaned up csv in the **"Process"** folder and my csv import routine runs based on **eoliverio flow**
The next few nodes are simple enough; we check the Watch node trigger to see if the **type** is a file and that the file ends in .csv.
If it does not we simply delete and ignore.
If it is a csv file, we delay for 5 seconds for some settlement time then we assemble a unix command.
I used **cut** , which allows you to very quickly (within seconds) extract the column data we need from the 829 columns.
**cut** is a unix shell command, using **cut** you can extract single columns, columns ranges and multiple column ranges in a single command line.
Great article on **cut** [https://linuxconfig.org/how-to-remove-columns-from-csv-based-on-column-number-using-bash-shell](https://linuxconfig.org/how-to-remove-columns-from-csv-based-on-column-number-using-bash-shell) (couldn't past link due to newbie status)
```auto
msg.cmd = "cut -d, -f1,6 \"" + msg.filename + "\" > \"" + msg.topic + "Process/`date +%F-%H-%M-%S`-" + msg.file + "\""
return msg;
```
Above I'm cutting out column 1 (part number) and column 6 (brand), I then move the new cut file into the **Process** **Watched** folder prepending the file with a human readable timestamp.
The csv flow picks this up and processes.
The the **exec** node which runs the actual command is then called, then it checks for success or failure. **exec** will return either 0 (success) or 1 (failure) in the **return code** output in msg.payload.code.
On 0 (success) I move the input file to the **Completed** folder and 1 (failure) I move to the **Error** folder.
This now works and my csv processes into mysql.
Flow to play with:
```auto
[{"id":"2c987f95bab60ccf","type":"tab","label":"CSV Clean up","disabled":false,"info":""},{"id":"1d3a3a94f6ce82aa","type":"watch","z":"2c987f95bab60ccf","name":"","files":"/data/holley/attributes/","recursive":"","x":240,"y":400,"wires":[["cd2840948068f4ff"]]},{"id":"c33cec292428de38","type":"exec","z":"2c987f95bab60ccf","command":"","addpay":"cmd","append":"","useSpawn":"false","timer":"5","winHide":false,"oldrc":false,"name":"","x":1230,"y":380,"wires":[[],[],["de96a7edef080f01"]]},{"id":"5c7bca037e46374e","type":"delay","z":"2c987f95bab60ccf","name":"","pauseType":"delay","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"x":800,"y":380,"wires":[["295414a778019deb"]]},{"id":"cd2840948068f4ff","type":"switch","z":"2c987f95bab60ccf","name":"Test if File","property":"type","propertyType":"msg","rules":[{"t":"eq","v":"file","vt":"str"},{"t":"empty"}],"checkall":"true","repair":false,"outputs":2,"x":450,"y":400,"wires":[["95a2ba0415fe404f"],[]]},{"id":"295414a778019deb","type":"function","z":"2c987f95bab60ccf","name":"Assemble Exec Command","func":"\nmsg.cmd = \"cut -d, -f1,6 \\\"\" + msg.filename + \"\\\" > \\\"\" + msg.topic + \"Process/`date +%F-%H-%M-%S`-\" + msg.file + \"\\\"\"\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1020,"y":380,"wires":[["c33cec292428de38"]]},{"id":"68f29a8b3d06e1be","type":"comment","z":"2c987f95bab60ccf","name":"Attributes Hotfolder Clean up csv","info":"","x":250,"y":360,"wires":[]},{"id":"95a2ba0415fe404f","type":"switch","z":"2c987f95bab60ccf","name":"Check if csv","property":"file","propertyType":"msg","rules":[{"t":"regex","v":"\\.csv$","vt":"str","case":true},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":610,"y":400,"wires":[["5c7bca037e46374e"],["2901f4601b324b22"]]},{"id":"2901f4601b324b22","type":"delay","z":"2c987f95bab60ccf","name":"","pauseType":"delay","timeout":"5","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":false,"x":720,"y":480,"wires":[["294874ee45e85c1f"]]},{"id":"294874ee45e85c1f","type":"exec","z":"2c987f95bab60ccf","command":"rm -f","addpay":"filename","append":"","useSpawn":"false","timer":"3","winHide":false,"oldrc":false,"name":"Not a csv so Delete File","x":930,"y":480,"wires":[[],[],[]]},{"id":"de96a7edef080f01","type":"switch","z":"2c987f95bab60ccf","name":"Success of failure","property":"payload.code","propertyType":"msg","rules":[{"t":"eq","v":"0","vt":"str"},{"t":"else"}],"checkall":"true","repair":false,"outputs":2,"x":1410,"y":380,"wires":[["f7ec4799c732374f"],["85a63e782436ae54"]]},{"id":"f7ec4799c732374f","type":"function","z":"2c987f95bab60ccf","name":"Assemble Exec Command","func":"msg.cmd = \"mv \\\"\" + msg.filename + \"\\\" \\\"\" + msg.topic + \"Complete/\" + msg.file + \"\\\"\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1500,"y":320,"wires":[["9c3f9bc7e1889577"]]},{"id":"9c3f9bc7e1889577","type":"exec","z":"2c987f95bab60ccf","command":"","addpay":"cmd","append":"","useSpawn":"false","timer":"5","winHide":false,"oldrc":false,"name":"Move file to Complete folder","x":1780,"y":320,"wires":[[],[],[]]},{"id":"85a63e782436ae54","type":"function","z":"2c987f95bab60ccf","name":"Assemble Exec Command","func":"msg.cmd = \"mv \\\"\" + msg.filename + \"\\\" \\\"\" + msg.topic + \"Error/\" + msg.file + \"\\\"\";\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":1500,"y":460,"wires":[["7d6fe9a2b5853d92"]]},{"id":"7d6fe9a2b5853d92","type":"exec","z":"2c987f95bab60ccf","command":"","addpay":"cmd","append":"","useSpawn":"false","timer":"5","winHide":false,"oldrc":false,"name":"Move file to Error folder","x":1770,"y":460,"wires":[[],[],[]]},{"id":"37c0570de931f406","type":"comment","z":"2c987f95bab60ccf","name":"Check if file and ends in .csv","info":"","x":540,"y":360,"wires":[]},{"id":"deb5c39413d2c098","type":"comment","z":"2c987f95bab60ccf","name":"fix csv and rename file","info":"","x":1020,"y":340,"wires":[]},{"id":"fd4948ff9b51e71c","type":"comment","z":"2c987f95bab60ccf","name":"Move file in to csv processing hotfolder","info":"","x":1630,"y":280,"wires":[]},{"id":"20dcd686cb6233ab","type":"comment","z":"2c987f95bab60ccf","name":"Move file in to csv error folder","info":"","x":1680,"y":420,"wires":[]},{"id":"a354bb5dd1325b8b","type":"comment","z":"2c987f95bab60ccf","name":"Not a csv just delete and ignore","info":"","x":870,"y":440,"wires":[]}]
```
**Things to do;**
Monitor the completed folder so I only store the last 5 csv files incase I need to look at them
Monitor the error folder and send email to admin of failure.
I plan to add bash script and call a script to break the csv rows up into say 10K at a time.
Example: Still testing but this should cut the big csv into 9,999 records preserving the header row at the top of each one (so 9,999 records + 1 header = 10,000 rows)
```auto
cat bigFile.csv | parallel --header : --pipe -N9999 'cat >file_{#}.csv'
```
I hope this helps someone trying to do similar things with Node-Red
Harry
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [20 September 2021 02:11 UTC](https://discourse.nodered.org/t/converting-csv-to-mysql-causes-error-with-blank-csv-columns/50632/4 "2021-09-20T02:11:25Z")
This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.