Up next in 10
Node.js SheetJS Tutorial to Read,Parse Microsoft Excel XLSX Files 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:03
in this video we will actually be
0:06
converting Microsoft Excel file XLS file
0:09
to CSV file inside
0:12
nodejs and for this
0:15
particular tutorial we will actually be
0:17
using a package called as
0:21
xlsx so just go to npmjs.com and uh if
0:26
you type this package
0:28
XLS X this is actually the name of the
0:32
library and it is also called as
0:36
sheetjs sheetjs is can be used in the
0:39
browser side as well using CDN so it is
0:43
actually sheetjs it this is actually the
0:46
documentation official website of this
0:48
Library it's a very handy Library you
0:52
can read Excel file you can edit them
0:56
you can extract data from Excel file and
0:59
save it in a CSV file so all these
1:02
things at sheetjs support and we will be
1:05
using it inside our nodejs application
1:07
using this module npmi XLS you can see
1:11
that almost 2 million downloads are
1:13
there or weekly downloads it's a very
1:15
popular package in npmjs so simple
1:19
command is there uh first of all just
1:21
make the nodejs and install this package
1:24
X LSX this is the command right here I
1:28
have already actually install this
1:31
module so you will see that we have
1:35
already an Excel file which is there
1:38
sample. XLS file and if I run this uh
1:42
node GS script what it will do it will
1:45
convert this into a CSV file so you can
1:47
see it's a Microsoft Excel file that you
1:49
can see right here it contains the
1:50
header row this is a table which consist
1:54
of columns and rows and this is actually
1:57
Excel file and now if I WR this nodejs
2:01
script if I just type here nodejs node
2:05
index.js in the left hand side uh you
2:08
need to replace sample. XLS so just make
2:11
sure in the input file name you just
2:14
need to provide the input file name
2:15
which is sample.
2:18
XLS so now if I run this file just
2:22
notice on the left hand site a file will
2:24
be created you will see that Excel data
2:28
has been converted to C CV and you will
2:31
see output. CSV has been created and
2:34
that Excel file has been converted to a
2:36
comma separated value files guys you
2:38
will see
2:39
that so it's a very handy application
2:42
guys we will be building it in the
2:43
command line which allows you to do this
2:45
process so now I will simply write the
2:49
code step by step so the very first
2:51
thing we will do right here we will
2:53
actually import the package XLS we need
2:56
to require this XLS
3:00
and then we need to actually use the
3:02
file system module to actually interact
3:04
with the file system to actually save
3:06
the file so we are requiring the buil-in
3:08
module of file system we also need the
3:10
buil-in module of path as well so just
3:14
require the path module as well so after
3:17
that guys we just need to actually write
3:20
a very simple function first of all we
3:23
will be expecting our Excel file which
3:26
is present in the same directory so we
3:27
will use the path module resolve the
3:30
path right here current directory and
3:32
then we will simply say sample. XLS so
3:36
this is actually the input file that we
3:37
are giving right here this is Excel file
3:40
so we giving to the script sample. XLS
3:43
and then we give the CSV file this also
3:46
pa. resolve and in the current directory
3:49
we will make this file result. CSV so
3:52
this file will get created automatically
3:54
whenever we run this P node jscript so
3:57
for doing this we need to Simply declare
4:00
a function which will actually carry out
4:02
this process so we'll simply make this
4:04
custom function convert Excel to
4:08
CSV and we will provide the Excel file
4:11
and the CSV file has two arguments right
4:14
here and now we will simply make this
4:16
function which is convert Excel to CSV
4:19
and then it expects two
4:22
arguments CSV
4:28
file so just make sure CSV file is
4:31
passed so we are passing these two
4:33
arguments to this function and inside
4:35
this function we are getting these two
4:37
arguments right here in the Declaration
4:39
of the function so the very first thing
4:41
we will do right here we will actually
4:43
be reading the content we will be
4:45
reading the Excel file for reading it we
4:48
will be actually be using this module
4:50
XLS you will see we have required it at
4:53
the very top and this module contains a
4:56
very simple function which allows to
4:59
read the f so read file is actually a
5:01
function which is available inside sheet
5:04
JS and in order to cre the Excel file
5:08
and here we will PA the path here Excel
5:10
file right here after passing it we will
5:12
actually get access to the workbook now
5:15
we need to get the very first
5:19
worksheet so now to get the sheet name
5:22
we will simply be using this object
5:24
workbook it contains this property sheet
5:27
names it's a array you will basically
5:30
get the very first sheet by providing
5:32
zero here zero index so it will actually
5:36
get the first sheet in the Excel file
5:39
after getting this you will get declare
5:41
get the worksheet as well so inside this
5:44
workbook object there is a sheets right
5:48
again once again and here you'll pass
5:50
the sheet name so whatever the sheet
5:53
name that we got right here if I just
5:55
console log the sheet name right here
5:57
also and then also console log the
6:01
worksheet just to check if the
6:04
application is working or not so if you
6:06
just run this you will see that CSV file
6:10
is not defined you just need to change
6:12
the variable name CSV
6:16
file we will basically see guys all this
6:19
information will come right here to the
6:21
current sheet number this is all this
6:23
information we are reading it from the
6:24
Excel file using sheetjs you will see
6:27
that all this data is coming right here
6:29
so the application is correct we are
6:32
correctly getting the worksheet now we
6:34
just need to convert this worksheet all
6:37
the data which is present to a Json
6:40
array so that we can convert this into
6:43
CSV so for doing this we will declare
6:47
Json data and excels it contains a
6:50
method called as
6:53
utils do sheet
6:57
2core you can basically see all these
7:00
methods are available to us either we
7:02
can convert this data to HTML Json or we
7:05
can convert this to text txt file diff
7:09
formula HTM all these methods are there
7:11
silk so we need to Simply convert this
7:14
into a Json object we'll pass the
7:16
worksheet as first argument and the
7:19
second argument we need to pass this
7:20
object which header is equal to one this
7:24
second option we are passing because we
7:26
don't need we need the header rows to be
7:30
separated that's why we are passing
7:32
header option to
7:35
one so header columns will be processed
7:38
individually now we need to Simply get
7:41
our header row we will declare this
7:43
variable and this Json data will be
7:46
getting the first one so we can simply
7:48
console log the header
7:50
row so this will actually contain all
7:53
the
7:54
columns you will see all these columns
7:57
will be there this a segment country
8:00
product discount band unit St these are
8:03
all the columns right here that you see
8:04
in the Excel file if I simple show you
8:07
sample these are all the columns that
8:09
you see sample country product discount
8:12
band unit sold so we are actually
8:15
getting these header names right here
8:17
using this
8:20
function so after that guys we now need
8:23
to remove any empty
8:27
string or comma from header rows if
8:30
present for the specific purpose we will
8:33
actually be using a simple Loop to
8:35
actually check header row if the length
8:38
of is greater than zero then only we
8:40
will process it and for processing it
8:43
you need to Simply check that if the
8:45
header
8:51
row and if the header
8:55
row we will use the trim function
9:01
is not equal
9:03
to like this these are the conditions
9:06
right here so if these conditions are
9:08
there we need to Simply remove some
9:10
white space so we'll simply that
9:12
removing leading empty string or comma
9:17
from header
9:18
row so there is a special function which
9:21
header row do shift so this function
9:25
will actually remove
9:27
the any white space with which is there
9:30
if you run this you will basically see
9:32
no white space is available so this
9:34
condition is false so nothing is there
9:37
so after that we can
9:40
simply simply show the data rows we can
9:45
declare Json data we need to use the
9:49
slice function because it's array we'll
9:51
slice the first one and then we will map
9:54
it for each row what we need to do for
9:57
each row guys what we need to do right
9:59
here in this uh Excel file we need to
10:01
convert this into a CSV file for doing
10:04
this we will first of all check using
10:06
the type of operator if the current row
10:10
type of is equal triple equal to a
10:12
number if this is a number in that case
10:15
we need to row
10:18
shift here we are simply removing the
10:20
number right here removing the leading
10:23
number if it
10:28
exist uh and after that we just need to
10:30
return row.
10:35
join and now we need to Simply combine
10:39
all the data into a CSV
10:41
file combine the header row and data
10:44
rows into CSV format so it's good
10:48
practice guys to actually write comments
10:50
as well while writing code as well so
10:53
now what we need to do we will declare
10:55
the CSV data variable and right here
10:57
header row we will use the join function
11:01
we'll put a comma right here and then
11:03
dot dot dot data
11:07
row and then we'll again use the join
11:09
function with the new line corrector so
11:13
every column every data row will be
11:15
coming after a new line corrector we
11:18
will put this new line corrector that's
11:20
all so now we can simply read write this
11:23
data using file system module this is
11:25
actually a function available WR file
11:27
synchronously we will provide the CSV
11:30
file CSV file which is present in this
11:32
argument and then pass the data as well
11:34
CSV data so now that's all guys we can
11:38
simply console log a simple message to
11:40
the user that your Excel data has been
11:44
converted
11:47
to CSV file and
11:50
saved that's all that we need to do guys
11:53
inside this function we first of all
11:56
read the data using this sheetjs XLS
11:59
module then we get the current sheet
12:01
number then we get the current access to
12:04
the worksheet then we read the data
12:06
using this function sheet undor 2core
12:09
Json and we get this Json data and then
12:12
we convert or remove any white space
12:14
which is available and then we
12:17
actually combine all this Json data into
12:20
a CSV file separated by comma and then
12:23
we save it using the file system module
12:25
very simple application now if I simply
12:28
remove this output. CSV file once again
12:31
run this file node index.js you will see
12:34
on the left hand side a file will be
12:36
created result. CSV so you will
12:39
basically see this result. CSV file has
12:42
been created and you can actually see
12:44
all this data Excel file data that you
12:47
see Microsoft Excel file data has been
12:49
converted into a CSV file using sheet JS
12:53
module inside nodejs we have used it
12:56
step by step I showed you all the
12:58
process all the code I written step
13:00
by-step code and explain to you what it
13:02
happened thank you very much guys for
13:04
watching this video please hit that like
13:06
button subscribe the channel as well and
13:09
I will be seeing you in the next
13:10
tutorial
#Programming
#Development Tools
#Scripting Languages