Excel type attachment

[Rj27]

Hi,
i am generating a file of type xslx using glidesysattachment. 
The values in each cell are coming from the table.
But for fields having comma separated values, the values are getting separated into different cell based on comma. It is expected to have them in one cell itself.
How can i control that ? i am not generaqting csv type...it's xlsx.

Eg : From user table, for location field value is in format street,city,country. the excel is generated with street under one cell, city in one and country in one cell.

Sample code :

for (var iter = 0; iter < userData.length; iter++) {
var fileName = "User_data";
fileName += '.xlsx';
data = userData[iter];
data = data.substring('0', data.length - 1);
var grAttachment = new GlideSysAttachment();
grAttachment.write(grR, fileName, 'application/xlsx', data);

[Aniket Chavan]

Hello @Rj27 ,

If you want to ensure that fields with comma-separated values are kept in a single cell when opening the generated Excel file, you'll need to modify the way you are formatting the data before writing it to the attachment.

You can use double quotes to enclose values that contain commas, which is a common approach in CSV files. Even though you are generating an xlsx file, this formatting can still be effective.

Please give a try to the script below and let me know how it works for you.

for (var iter = 0; iter < userData.length; iter++) {
    var fileName = "User_data";
    fileName += '.xlsx';
    var data = userData[iter];

    // Assuming data is a comma-separated string like "street,city,country"
    var values = data.split(',');

    // Enclose values containing commas in double quotes
    for (var i = 0; i < values.length; i++) {
        if (values[i].includes(',')) {
            values[i] = '"' + values[i] + '"';
        }
    }

    // Join values with commas and create a single string
    data = values.join(',');

    var grAttachment = new GlideSysAttachment();
    grAttachment.write(grR, fileName, 'application/xlsx', data);
}

This modification checks each value in the comma-separated string. If a value contains a comma, it is enclosed in double quotes. The values are then joined back into a single string with commas between them. This way, when the Excel file is opened, the values with commas will be treated as a single cell.

Let me know your views on this and Mark ✅Correct if this solves your query and also mark 👍Helpful if you find my response worthy based on the impact.

Thanks,

Aniket

[Rj27]

Hello Aniket,
Tried above but it did not work.
My actual excel is expected in something similar format below:
Header : Name, Email,Id,Location
Expected : Test User, test@test.com, 123, Street,city,country >all in one cell
Actual : Test User, test@test.com, 123, Street,city,country > 3 different cells

[Aniket Chavan]

Hello @Rj27 ,

I see, thanks for providing additional details. If you want to have a specific column ("Location") with comma-separated values to be treated as a single cell in Excel, you need to format it accordingly in the xlsx file.

One way to achieve this is by using Excel's formula to concatenate the values in the "Location" column into a single cell. However, keep in mind that this approach will add an extra column to your Excel file with the concatenated values.

Here's an updated code:

for (var iter = 0; iter < userData.length; iter++) {
    var fileName = "User_data";
    fileName += '.xlsx';
    var data = userData[iter];

    // Assuming data is a comma-separated string like "Test User,test@test.com,123,Street,city,country"
    var values = data.split(',');

    // Enclose the Location values in double quotes
    var locationValues = values.slice(3); // Assuming "Location" is at index 3
    var locationString = '"' + locationValues.join(',') + '"';
    
    // Replace the Location values in the original array with the concatenated string
    values.splice(3, locationValues.length, locationString);

    data = values.join(',');

    var grAttachment = new GlideSysAttachment();
    grAttachment.write(grR, fileName, 'application/xlsx', data);
}

This modification specifically addresses the "Location" column. It extracts the values from the "Location" column, concatenates them with commas, and then replaces the original values in the array. The resulting Excel file should have a separate column with the concatenated values for the "Location" field.