Node.js Express Project to Upload CSV File Data in MySQL Table Using fast-csv Library in Javascript

uh Hello friends today in this tutorial I will be showing you that how basically we can upload a CSV file with containing some data inside this you will see it contains three columns name Age Country and we have got four rows of data that we want to insert inside this MySQL table here so I am using this PHP my admin and now let me delete all this data here and simply I will this will be an Express project here and this will be a web application so you can see currently the table is empty right here and now if I start my node.js application so it is hosting on localhost 5000. so if I start this application you will see basically it's the example here you can upload CSV file directly to your mySQL database you can import all the data which is present inside the CSV file and there we have choose file option so if I choose the file here so I can go to the path here where the file is located I can go to the projects directory and this CSV project and this is our file file.csv if I click this store file button you will see that data will be imported and now if I refresh the database you will see all the four records have been successfully added and we don't need to enter the ID here because ID is the primary key of the table it will be automatically inserted inside the records so we just need to provide name Age Country so you can see it has neglected this header row that we provided name Age Country and from the second row it inserted all these records out there and now let me change these records let's suppose I change these two some other names here so with the help of this you can insert multiple records out there and let me say like this you can have multiple records I out there like this so now we have these four records so now if I want to insert once again I can upload this file by going to the end point here simply choose file and again if I select this file data imported and now if I refresh the database you will see some more records should be added here you will see these four records so this is basically the structure of the table guys that we have created as you can see we are using exam control panel which comes with Apache MySQL server we have both started these servers on port number 80 and 3306 so download this software and after this go to the table and we can even create this table if I go to this let me drop this table here by going to simply type here drop table users and click on simply go your table will be deleted and now we can create a new table by clicking the new button and simply you put here users and put here ID which will be the primary key so just select here this Auto increment here and basically then we have the column for name and then we have column for age and then for country so name will be varchar so this is 256 integer 11 this is H again this will be bar chart 256. you can take any table of your choice simply click save your table will be created you will see these four columns will be there and now we just need to basically I have written all the source code in the description of this video so this is my step-by-step blog post let me show you all the source code is given this step-by-step instructions so you can go to the description and copy paste all the source code so now let's get started the libraries let me tell you which libraries we are using we are using this fast Dash CSV Library basically it's a node package for parsing the CSV files here it has got 742 000 weekly download so we are using this library for parsing the CSV and we are using malter for uploading the files to the server which is a very awesome node.js module and then we are using Express which is our web server which is the web server module and also we are using the MySQL driver library in order to connect to the mySQL database so these are the four libraries that we are using so now let me stop this server and start this application from scratch so let me delete this here and delete visual studio and here I will now go to my projects directory and here I will open a new directory here which is CSV project I will it already exists so CSV let me open this inside Visual Studio cortex writer so now we will be initializing the package.json file so npm in a dash y so this will create the package.json file so now we just need to install Express malter MySQL and the fourth one is uh fast CSV so these are the four dependencies that we need to install guys it will hardly take five seconds to install this so after this we can simply create our index.js file you can see it has now created this added these four dependencies express fast as CSV malter and my SQL now you just need to create your index.js file and here also you need to create uploads directory where you will be storing all the CSV files that should be uploaded by the user and also you need to create index.html which will be the simple form which will be shown to the user so here this will be a simple form guys so we will write this form from scratch so inside this form let me just write this form here so inside this form guys this will be a simple uh let me paste it and explain to you what is happening so this is just a static code you can uh let me just paste it here this is basically the HTML form which will be shown we are using some bootstrap here you will see this is simply a input file here and we have given this name attribute which is you can change this to anything we can change it to fight so basically it will only accept the CSV files here you will see you can put here dot symbol here so it will only accept the CSV files and then these are all bootstrap classes guys we have included the CDN for bootstrap so you can copy paste the source code in the description of the video and basically we will be making a simple post request whenever this form submits inside our express application this is the end point here slash import CSV and we are making a post request to it and now we just need to initialize our Express server here so we will be importing the express module we just need to make a simple Express app here so we will start this server on Port 5000 we will simply say that app is listening on Port 5000 and right here we after this we can simply import some body parser as well so body parser is the middleware which is needed for this application so whenever you are working with forms out there you need this body parcel middleware so this is automatically installed you need not have to install this so we can use this middleware by using app.use body parser dot URL encoded extend it to false and then we will use body password Json like this so just include these two middle Pairs and now we will be loading that file which we created index.htmls in the slash route so we will simply load this file send file we will use so directory name and index.html so you can see we will load this file here whenever you start this so let me start this application node mod index.js so app is listening you will see and now if I go to the localhost 5000 you will see basically this form will be shown to the user here and now we can simply here write that post request here also app.post which is slash import CSV request response so here we need to Simply insert to the MySQL table for that guys we just need to first of all create the connection here first of all to the mySQL database for that we just need to require the library which is the MySQL Library we installed require MySQL and after this we just need to create the connection here so right here after that you do this you can create the connection so for creating the connection guys we will create a pool variable and this method sorry module supports a method out there which is MySQL create connection and inside this basically it takes a set of object first is the host name which is localhost then we have the username which is a default username is root and then we have got the password is nothing and then we have got the database which is Excel DB so this details you need to just replace with your own details out there and after doing this guys we just need to also import a Malta library for uploading the files so we will simply import the malted Library so you just need to require the smaller library after that we just need to set the storage engine of malter so on which location you want to store the uploaded file so we have created this uploads folder now we just need to tell the malted Library where to store all these things so even simply say malter config so here we will simply create a storage object here so we'll use this Malta dot dist storage method inside this basically we provide the destination so here we will say request file call back and inside this we will call the Callback function personal and then provide the location here so we will be storing it inside the uploads directory and then we will be putting a comma here for the file name so which file name that you want to give to your uploaded file so every time we just need to set a dynamic name for the file so we'll again have request file and callback this will be a simple arrow function and here we will again call callback personal and then we will put file dot field name this is the thing we will use and then we will put a slash dash symbol followed by the Dynamic file name you can give using a date dot Now function date dot now and then you can extract the path from the file so for that you just need to require the path module so just require this path module which is a built-in module so here you can with the help of this you can extract the extension from the file name path.extension name then file dot original name that's all so basically it will give random name to your file uploaded file after this you just need to pass this so which is this storage object to our Constructor of malter so you will say malter and after this we will initialize the malter Constructor which is this one sorry just call this as upload monitor is already imported you will see that malter we are required it after this we just need to pass the storage basically it takes a source storage option option we will pass our own storage that we configured like this sorry storage so now this middleware function is ready guys or monitor now we can simply pass this middleware function right here inside our post request whenever we do this we can simply upload a single file which is upload dot single and here basically you can provide the name attribute whatever name attribute that you have given to the input element you can see I have given file here so I will simply provide here file that's it so now we simply need to console log the file path guys so whenever when you upload this file you will get your path like this request.file.path so right here if I now load this application here and you will see malter has already been declared let me see sorry one time is already declared right here you can see that so now you can see it is listening on Port 5000 so now if I upload my file here you can even only select CSV files here you will see if I select this click on store file you will see the path will be uh just displayed on the command line and if you want also check the uploads folder you will see it by file is uploaded to the uploads directory so now we have got the data that we want to insert into the MySQL table so in the next line we will be calling a external function that we need to write here which will do the task automatically for us so right here in the next line we just need to here just call this function upload CSV and here we will pass the absolute path of this file so this is present inside the uploads directory and then we can simply concatenate request.file Dot after this we have got the file name so so we will Define this function in the next line here so right here outside this you can Define this function upload CSV you will get your URI you can just call this as path so inside this function here we just need to first of all extract the data which is present inside the CSV file here you will see you just need to create a file right here name Age Country just put out sample details out there four records and after this we will simply use this module here which is fast CSV so at the very top you need to import this module here which is the CSV fast CSV module so just import this so after this you just need to and for this you also need the file system module as well so required this also so first of all we will read this file using the file system audio so file system this contains a method which is create read stream so here you just need to provide your path so you will get access to the file here after this you just need to create a array of data which is CSV data you can just call this as anything this will be empty array then we will have a simple file stream variable which will be initialized to the CSV this module contains a method here which is CSV parts and you can just concatenate these methods out there CSV dot parse and after this you it contains various events out there so whenever some data is present this event will automatically get fired here and inside this data here you will get your own data so this will be a callback function here and inside this callback function we just need to push the data inside the array so we will use the push method and here we will be pushing the data into the array here so now when the task is completed then again this unique event will be fired which is called as end and inside this we just need to insert this extracted data inside the mySQL database so if you see the structure of the data here first row contains the header row which is name Age Country this row we don't need to insert so we can now just ignore this row so for ignoring it we have a function out there inside JavaScript which is unshift shift function we just need to call this so now this will eliminate this line it will not insert this and now it will automatically goes to the second line here so after this we just need to we call this connection object that we created which is pool here it basically contains the details about your mySQL database so this contains a method out there which is get connection you will get the connection of the after inside this you we have two variables which is error and the connection so inside this callback function we will simply have a first of all if condition if any sort of error takes place we can simply console log that error if no error takes place then we just need to insert this data inside the mySQL database we will simply put a SQL query out there here so we will simply insert this data insert into the table name which is users and inside this parentheses we just need to provide the column names so in this case if you see the structure of the table here you will see this structure to be a name Age Country so these column names need to be the same here whenever you provide these here the spelling mistake needs needs to be the same here so we will simply provide here name Age Country and Then followed by the values that we want to insert and here you will just put a question mark here this is called as placeholder and after this you will call connection dot query and inside this you will pass your SQL query here and then you will pass the data inside square brackets which is CSV data column put a comma and then this will contain the error and result this field again it's a callback function and right here your records have been inserted so now let me just execute this application in the browser let me open this and one more thing inside after this you just need to call the stream dot pipe it contains a pipe method and inside this you just need to pass this file stream like this so this line is very important you just need to write outside this block so basically it's a stream here we are passing this whole file stream that we created using the pipe method so this is important so after you do this you can open this application now inside the browser here so it is saying that pool dot getconnection is not a function pull dot getconnection is not a function oh sorry this we just need I have misspelled it MySQL dot create pool this is a method that we need to use create pool so just replace this create connection with create pool here and now if you refresh your application your apps will be started here on Port 5000 so if I once again open this here localhost 5000 you will get your form here so if I show you the table here it is currently empty here so no data is present right here now if I click the choose file button click on the CSV file here click on this button so you will see nothing happens but if I now check the table here you will see all the records have been successfully transferred from the CSV file to the MySQL table so in this way you can import millions of Records to your database without manually entering it and now we can show a user a simple message on the screen that your import records have been inserted you can send a simple message records imported and also if you see we are uploading these files on the server side so after successfully imported we can delete these files for deleting it we have a simple method out there which is you can simply call this method after you do this process right here you can just call this file system module it contains a method unlink synchronization so this will delete this file from the server after it is processed so you can just pass the path here that we received so just write this line so once again if you do the same process you know let me just delete these and again execute this so just select your CSV file here click on this you will see your message records imported and if you check the table here you will see your records will be added here you can see all these records so in this way guys you can just import the records which are present inside your CSV file into mySQL database so you can go to the blog post in the description of this video step-by-step instruction is given so you can copy paste all the source code and I will be seeing you in the next video

Channel: Coding Shiksha

Views: 6,642

Keywords: coding shiksha, google docs clone

Id: Zpz7RUcVPrs

Length: 22min 1sec (1321 seconds)

Published: Wed Dec 28 2022