CODEWITHSUNDEEP
javascriptnode.jsxlsx
Austin Hallett
Austin Hallett

Reputation: 301

How to loop through excel docs in Node

My goal is to create a script within a Node project which will loop through a directory of spreadsheets, pull a value from a cell, and add this value to a new row in a new spreadsheet... I am unable to figure out the logic.

In my root directory, I have a folder name "Timesheets" and in there a folder named "2021" which is where I store all my timesheets. Each timesheet is named as the date-span for the week (ex: June14th-18th). Each timesheet has one cell which is labeled the total hours for the week (cell D12) this is the only cell I care about.

I am using node packages 'xlsx' and 'fs'. The following code will give me the names of all the files in the timesheet directory, but I am struggling to determine how to do anything with that data. I am new to Node, so any guidance would be greatly appreciated.

read-timesheets.js

const xlsx = require("xlsx");
const fs = require("fs");
const jsontoxml = require("jsontoxml");

var directory_name = "./Timesheets/2021";

let filenames = fs.readdirSync(directory_name);
  
console.log("\nFilenames in directory:");
filenames.forEach((file) => {

    console.log(file);

    //var workbook = xlsx.readFile(file);
});

the result is as follows:

Filenames in directory:
April12th-16th.xlsx
April19th-23rd.xlsx
April26th-30th.xlsx
April5th-9th.xlsx
Feb15th-19th.xlsx
Feb1st-5th.xlsx
Feb22nd-26th.xlsx
Feb8th-12th.xlsx
jan11th-15th.xlsx
jan18th-22nd.xlsx
Jan25th-29th.xlsx
jan4th-8th.xlsx
June14th-18th.xlsx
June7th-11th.xlsx
March15th-19th.xlsx
March1st-5th.xlsx
March22nd-26th.xlsx
March29th-April2nd.xlsx
March8th-12th.xlsx
May10th-14th.xlsx
May17th-21st.xlsx
May24th-28th.xlsx
May31st-June4th.xlsx
May3rd-7th.xlsx

but when I try to open the file (via the workbook variable) I receive an error stating the following:

Filenames in directory:
April12th-16th.xlsx
internal/fs/utils.js:314
    throw err;
    ^

Error: ENOENT: no such file or directory, open 'April12th-16th.xlsx'
    at Object.openSync (fs.js:498:3)
    at Object.readFileSync (fs.js:394:35)
    at read_binary (C:\dev\git\excel-parser\node_modules\xlsx\xlsx.js:2730:44)
    at readSync (C:\dev\git\excel-parser\node_modules\xlsx\xlsx.js:21018:69)
    at Object.readFileSync (C:\dev\git\excel-parser\node_modules\xlsx\xlsx.js:21046:9)
    at C:\dev\git\excel-parser\read-timesheets.js:14:25
    at Array.forEach (<anonymous>)
    at Object.<anonymous> (C:\dev\git\excel-parser\read-timesheets.js:10:11)
    at Module._compile (internal/modules/cjs/loader.js:1085:14)
    at Object.Module._extensions..js (internal/modules/cjs/loader.js:1114:10) {
  errno: -4058,
  syscall: 'open',
  code: 'ENOENT',
  path: 'April12th-16th.xlsx'
}

How can I loop through these files, pull a value, and create a new spreadsheet with this information? Ultimately, this will help me during tax season, as I work as a contractor for a company and my hours are calculated weekly based on these time sheets. The math will be simple once I am able to put the total hours on one sheet

Upvotes: 0

Views: 1501

Answers (1)

Haythem Farhat
Haythem Farhat

Reputation: 797

You have to append the directory name to the file name inside forEach because the xlsx files are not directly near the script.

Node comes with utilities to work with paths

const xlsx = require("xlsx");
const fs = require("fs");
const jsontoxml = require("jsontoxml");
const path = require('path');

var directory_name = "./Timesheets/2021";

let filenames = fs.readdirSync(directory_name);
  
console.log("\nFilenames in directory:");
filenames.forEach((file) => {
    const _file = path.resolve(directory_name, file);
    console.log(_file);

    var workbook = xlsx.readFile(_file);
});

Upvotes: 1

Related Questions