1

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]'
  }
]
3
  • The date even doesn't looks like unix timestamp. Make sure you are fetching the right thing Commented Jan 19, 2020 at 11:19
  • I’m being pedantic again. That output isn’t JSON? See this answer for more info. Commented Jan 19, 2020 at 11:32
  • 36270 is how Excel stores the date 1999-04-20. Commented Jan 19, 2020 at 12:20

1 Answer 1

2

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)
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.