Up next in 10
Node.js node-xlsx Example to Parse,Read Microsoft Excel (XLSX File) and Export to CSV File
Jan 9, 2025
Official Website:
https://freemediatools.com
Show More Show Less View Video Transcript
0:00
uh hello guys welcome to this video so
0:02
in this video we will again be looking
0:04
at a very simple module which allows you
0:06
to extract data from a Microsoft Excel
0:09
file and convert them convert that data
0:12
into a CSV file CSV files are comma
0:16
separated value files you will see
0:18
inside your machine we have a simple
0:20
Microsoft Excel file sample. XLS file
0:23
and it contains rows columns so these
0:26
are different columns right here segment
0:28
country product these are number of row
0:31
rows out there so we are actually using
0:34
sheetjs very simple module node xels
0:37
module no- excels so it's a special
0:41
specific module designed for this
0:42
purpose to actually convert your
0:44
Microsoft Excel file into a CSV file so
0:46
now if I execute this application you
0:49
will see on the left hand side a file
0:52
will be created of CSV if I just type
0:54
node index.js you will see on the left
0:57
hand side your Excel data has been
0:58
converted to CSV and it is saved in the
1:01
same directory if I open this output.
1:03
CSV file guys you will see all that
1:06
Microsoft Excel data that you see right
1:08
here has been converted into a comma
1:10
separated value file and uh these are
1:13
column names separated by comma and
1:15
these are rows separated by this is
1:18
actually the data that you see right
1:20
here has been separated by comma this is
1:23
actually a CSV file you can see
1:27
that output. CSV
1:32
so we look at this module guys it is
1:36
using sheetjs in the background node.
1:38
XLS this is actually the package which
1:41
is used guys for this
1:44
Library just go to npmjs.com just search
1:47
for this package node D
1:51
xlsx this is nodejs
1:54
XL file parer and reader this is
1:57
actually the Builder and it basically
2:00
relies on sheetjs which is a very in the
2:03
last tutorial we discussed about this
2:04
library sheetjs is a you can also use
2:08
this library on the browser side as well
2:10
to actually read write Excel files it's
2:12
a pretty powerful Library when it comes
2:15
to Excel files when you want to do
2:17
anything with Excel files sheetjs it's a
2:20
very good Library this is their official
2:22
documentation you can read more about it
2:25
but in the meantime in this specific
2:26
tutorial guys we will look at how to
2:28
convert Excel into CSV it is having
2:31
281,000 weekly downloads it's a pretty
2:35
powerful package so the command is very
2:37
simple npmi node D XLS I've already
2:40
installed this module just go to your
2:42
command line and just type npmi node XLS
2:46
so I've already installed it so I will
2:49
not install it once again so I will
2:51
simply write this code right
2:56
here and explain to you step by step how
3:00
this code is working so the very first
3:03
thing guys we will do we will basically
3:05
include this module at the very top node
3:08
D
3:09
XLS after that we also need to require
3:12
the file system module built in if you
3:15
want to save or read files nodejs has a
3:18
built-in
3:19
module which is file system after that
3:22
we also need to have our path
3:25
module which is again a built-in module
3:28
so we just need to require it
3:30
after that guys what we need to do right
3:32
here we need to give it the input file
3:36
which is Excel file path we'll use this
3:39
method path.
3:41
resolve and from the current directory
3:44
is present in the current directory the
3:46
Excel file the name of the file is
3:48
sample.
3:51
exls and then similarly we will put the
3:54
CSV file as
3:56
well this output file will automatically
3:58
get get created it which is
4:01
result. CSV so we have given the input
4:04
file we have given the output file now
4:06
we just need to make a simple
4:09
function this is custom function convert
4:13
Excel to CSV it'll provide the Excel
4:17
file and
4:18
the and the CSV
4:21
file just need to Define this function
4:27
now we will have a or Excel file and the
4:31
CSV file as two arguments it will be
4:33
Arrow function and you can see that we
4:35
getting this these two arguments which
4:37
are passed right here to this function
4:39
and now we are defining this function so
4:42
it's very easy guys first of all we need
4:44
to pass or read the
4:47
file for reading the Excel file this
4:50
actually provide us a module work sheets
4:54
from
4:55
file and this module that you see XLS it
4:58
contains this method read
5:00
file
5:03
synchronously this actually
5:06
contains this method sorry not read file
5:10
but yeah xels do Parts this is actually
5:14
a method which is available inside this
5:17
package which actually passes our Excel
5:20
file here we need to read the file right
5:23
file system and it contains read file
5:27
synchronously and here we need to
5:29
provide our Excel file
5:31
path this is our Excel file you need to
5:34
provide the path right here so now it
5:37
will read the file it will store it
5:39
inside this variable now we just need to
5:42
get information about this Excel file
5:45
you need to get the first
5:48
worksheet assuming that all the data is
5:52
present in the first sheet if you have
5:55
multiple sheets then you can access
5:57
individual indices now we can get get
5:59
access to that sheet worksheet from file
6:03
and array it will start from zero so it
6:06
is getting access to the first sheet and
6:10
it get access to the data which is what
6:12
data is present inside the worksheet now
6:15
we need to convert this worksheet to a
6:19
CSV format so how we can do that we can
6:23
actually have a method right here inside
6:27
worksheet we can use a map operator and
6:31
we can simply f for every row which is
6:34
present inside
6:37
this data we can simply say row. Jo join
6:42
and we put the comma right here because
6:43
CSV basically comma separated value it's
6:46
a abbreviation is called so you'll put a
6:49
comma right here if you want a different
6:52
symbol to be separated you will put
6:53
right here basically in CSV we put comma
6:57
and then we need to join it for every
6:59
row we need to do it in a separate line
7:02
so new line corrector that's all that we
7:04
need to do guys here and after that we
7:07
can write our data write file
7:09
synchronously CSV
7:12
file and whatever data CSV data that's
7:17
all and then we can simply put a message
7:22
on the screen that
7:24
Excel file has
7:26
been
7:28
converted to to CSV file that's all guys
7:32
very simple application we actually
7:35
required this node xels sheetjs module
7:38
at the top then we actually pass this
7:40
data read all the data of Excel file and
7:44
then pass to this function parts then we
7:46
get to the actual worksheet number get
7:48
the data and then we Loop it separate it
7:52
by comma and new line corrector and then
7:54
save it
7:56
so if I change for delete this file and
8:01
rerun the application once again so just
8:03
see on the left hand side if I run this
8:05
application a it is saying that row join
8:09
is not a
8:11
function let me see guys what is a
8:14
problem uh okay
8:18
sorry this
8:20
was row. join
8:30
dot join uh sorry we can put put this
8:35
into a single line then it will not
8:37
create problem like this you can see
8:41
that for each row we are putting comma
8:43
right here and then joining it new line
8:45
corrector
8:47
so just see on the left hand side Excel
8:51
file has been converted to CSV file and
8:53
now you can see that uh result. CSV all
8:56
that Excel columns you will see segment
8:59
country product discount band this is
9:01
separated by comma you can see that so
9:04
this is actually CSV file guys all the
9:06
data is converted you can see that you
9:09
can take any unlimited number of Excel
9:10
files and use the script to actually do
9:13
this process thank you very much for
9:15
watching this video please hit that like
9:16
button subscribe the channel and I will
9:18
be seeing you in the next video
#Programming
#Software
#Computer Science