CODEWITHSUNDEEP
javascriptnode.jsjsonexcelxlsx
Ekansh Jain
Ekansh Jain

Reputation: 110

Unable To Parse Date when converting excel to json array

I have to create an api for file upload which is an xlsx file. I have sucessfully uploaded file using multer and able to open it I need to parse the file and convert it into array json object. My code is this:-

let workbook = xlsx.readFile(`uploads/${req.file.filename}`)
    let worksheet = workbook.Sheets['Sheet1']
    let headers = {}
    let data = []
    for (z in worksheet) {
        if (z[0] === '!') continue
        //parse out the column, row, and value
        let tt = 0
        for (let i = 0; i < z.length; i++) {
            if (!isNaN(z[i])) {
                tt = i
                break
            }
            tt = i
        }
        let col = z.substring(0, tt)
        let row = parseInt(z.substring(tt))
        let value = worksheet[z].v
        //store header names
        if (row == 1 && value) {
            headers[col] = value
            continue
        }
        if (!data[row]) data[row] = {}
        data[row][headers[col]] = value
    }
    // drop those first two rows which are empty
    data.shift()
    data.shift()
    console.log(data)
    const studentList = []
    data.map(object => {
        if (object['Year of Enrollment']) object['Year of Enrollment'] = parseInt(object['Year of Enrollment'])
        if (object['Semester']) object['Semester'] = parseInt(object['Semester'])
        if (object['Aadhar Number']) object['Aadhar Number'] = parseInt(object['Aadhar Number'])
        if (object['Phone Number']) object['Phone Number'] = parseInt(object['Phone Number'])
        if (object['Alternate Phone Number']) object['Alternate Phone Number'] = parseInt(object['Alternate Phone Number'])
        let obj = {
            "student_first_name": object['First Name'],
            "student_last_name": object['Last Name'],
            "student_branch": object['Branch'],
            "student_year": object['Year of Enrollment'],
            "student_semester": object['Semester'],
            "student_gender": object['Gender'],
            "student_email": object['Email'],
            "student_address": object['Address'],
            "student_linkedin_profile": object['Linkedin Profile'],
            "student_github_profile": object['Github Profile'],
            "student_aadhar_number": object['Aadhar Number'],
            "student_phone_number": object['Phone Number'],
            "student_date_of_birth": object['Date of Birth'],
            college_id: req.body.college_id
        }
        if (object['Alternate Email']) obj.student_alternate_email = object['Alternate Email']
        if (object['Alternate Phone Number']) obj.student_alternate_phone_number = object['Alternate Phone Number']
        studentList.push(obj)
    })
    console.log(studentList)

In the output I am getting this and dont know what happened to date of birth:-

[
  {
    student_first_name: 'Ekansh',
    student_last_name: 'Jain',
    student_branch: 'CSE',
    student_year: 2017,
    student_semester: 6,
    student_gender: 'male',
    student_email: '[email protected]',
    student_address: 'Udfr',
    student_linkedin_profile: 'http://linkedin.com/ekansh',
    student_github_profile: 'http://github.com/ekansh',
    student_aadhar_number: 123456789011,
    student_phone_number: 7894561230,
    student_date_of_birth: 36270,
    college_id: 'college-f9NsLo7Ww6oTIlICElG5GZD5JHKTxqbF',
    student_alternate_email: '[email protected]'
  }
]

In the excel file, in Date Of Birth Field, it is 20-04-1999 Can someone help me so that the output would look like this:-

[
  {
    student_first_name: 'Ekansh',
    student_last_name: 'Jain',
    student_branch: 'CSE',
    student_year: 2017,
    student_semester: 6,
    student_gender: 'male',
    student_email: '[email protected]',
    student_address: 'Udfr',
    student_linkedin_profile: 'http://linkedin.com/ekansh',
    student_github_profile: 'http://github.com/ekansh',
    student_aadhar_number: 123456789011,
    student_phone_number: 7894561230,
    student_date_of_birth: "1999-04-20",
    college_id: 'college-f9NsLo7Ww6oTIlICElG5GZD5JHKTxqbF',
    student_alternate_email: '[email protected]'
  }
]

Upvotes: 1

Views: 1466

Answers (1)

Ekansh Jain
Ekansh Jain

Reputation: 110

Just pass cellDates true as option when reading xlsx file (in 1st line) like this code:-

let workbook = xlsx.readFile(`uploads/${req.file.filename}`, { cellDates: true })
    let worksheet = workbook.Sheets['Sheet1']
    let headers = {}
    let data = []
    for (z in worksheet) {
        if (z[0] === '!') continue
        //parse out the column, row, and value
        let tt = 0
        for (let i = 0; i < z.length; i++) {
            if (!isNaN(z[i])) {
                tt = i
                break
            }
            tt = i
        }
        let col = z.substring(0, tt)
        let row = parseInt(z.substring(tt))
        let value = worksheet[z].v
        //store header names
        if (row == 1 && value) {
            headers[col] = value
            continue
        }
        if (!data[row]) data[row] = {}
        data[row][headers[col]] = value
    }
    // drop those first two rows which are empty
    data.shift()
    data.shift()
    console.log(data)
    const studentList = []
    data.map(object => {
        if (object['Year of Enrollment']) object['Year of Enrollment'] = parseInt(object['Year of Enrollment'])
        if (object['Semester']) object['Semester'] = parseInt(object['Semester'])
        if (object['Aadhar Number']) object['Aadhar Number'] = parseInt(object['Aadhar Number'])
        if (object['Phone Number']) object['Phone Number'] = parseInt(object['Phone Number'])
        if (object['Alternate Phone Number']) object['Alternate Phone Number'] = parseInt(object['Alternate Phone Number'])
        let obj = {
            "student_first_name": object['First Name'],
            "student_last_name": object['Last Name'],
            "student_branch": object['Branch'],
            "student_year": object['Year of Enrollment'],
            "student_semester": object['Semester'],
            "student_gender": object['Gender'],
            "student_email": object['Email'],
            "student_address": object['Address'],
            "student_linkedin_profile": object['Linkedin Profile'],
            "student_github_profile": object['Github Profile'],
            "student_aadhar_number": object['Aadhar Number'],
            "student_phone_number": object['Phone Number'],
            "student_date_of_birth": object['Date of Birth'],
            college_id: req.body.college_id
        }
        if (object['Alternate Email']) obj.student_alternate_email = object['Alternate Email']
        if (object['Alternate Phone Number']) obj.student_alternate_phone_number = object['Alternate Phone Number']
        studentList.push(obj)
    })
    console.log(studentList)

Upvotes: 2

Related Questions