Exported field in CSV not fitting into single cell

Hi All,

I wrote a class to export the table data into csv file and it works but when i try to open exported csv file in spreadsheet some fields are fit into single cell.

For example i have exported some data from PurchLine table to csv. When i open it in spreadsheet, the some of the Name field value in PurchLine table causing the issue.

Please check below highlighted data, name value of the particular line not fitting into cell, for other rows it seem good.

Also find below code which i used to export csv file and please help me to over come this issue.

Code Starts here-------->

[SysEntryPointAttribute(false)]
public void processOperation(exportCSV _contract)
{
#File
Common tableBuffer;
DictField dictField;
DictTable dictTable;
FieldId fieldId;
FileName fileName;
TableName tableName;
date resultDate;
utcDateTime dateTimeResult;
boolean recordsExist;
int i,j,countTable,executedTable;
container line,line2;
boolean recordExist= false;
FileIOPermission permission;
Days numberDays;
MAX_TablesToExportCSV tablesToExportCSV, tablesToExportCSV2;
str nameOfTable,pathToStore;
MAX_CSVExportPath documentPath;
AsciiIo asci;
str replaceUTC;
int check;
;

countTable = 0;
executedTable = 0;
check = 0;
numberDays = _contract.parmNoofDays();
resultDate = today()-numberDays;
dateTimeResult= DateTimeUtil::newDateTime(resultDate, 0);

select firstOnly documentPath;
if(documentPath)
{
pathToStore = documentPath.CSVFilePath;
}
else
{
throw error (‘Set up file path to store file’);
}

select firstOnly tablesToExportCSV2;
if(!tablesToExportCSV2)
throw error(‘Add one or more tables in the master’);

while select tablesToExportCSV order by tablesToExportCSV.ListOfTables asc where tablesToExportCSV.Export == NoYes::Yes
{
nameOfTable = tablesToExportCSV.ListOfTables;
tableName = nameOfTable;

dictTable = new dictTable(tableName2Id(tableName));
fieldId = dictTable.fieldNext(0);
tableBuffer = dictTable.makeRecord();

select crossCompany tableBuffer where (tableBuffer.createdDateTime >= dateTimeResult) || (tableBuffer.modifiedDateTime >= dateTimeResult) ;

fileName = pathToStore + ‘\’ + tableName + ‘.csv’;
permission = new FileIOPermission(fileName, #io_write);
permission.assert();
asci = new AsciiIo(fileName ,#io_write);
asci.outFieldDelimiter(’,’);
for( i =1; i <= dictTable.fieldCnt(); i++)
{
dictField = new DictField(dictTable.id(), dictTable.fieldCnt2Id(i));
if(dictField.isSql())
{
line += [ dictField.name(i) ];
}
}
asci.write(line);
line = ConNull();
while select crossCompany tableBuffer where (tableBuffer.createdDateTime >= dateTimeResult) || (tableBuffer.modifiedDateTime >= dateTimeResult)
{
for( j =1; j <= dictTable.fieldCnt(); j++)
{
dictField = new DictField(dictTable.id(), dictTable.fieldCnt2Id(j));
if(dictField.isSql())
{
if(dictField.name(j) == ‘CREATEDDATETIME’ || dictField.name(j) == ‘MODIFIEDDATETIME’ )
{
replaceUTC = DateTimeUtil::toStr(tableBuffer.(dictField.id()));
if(replaceUTC && replaceUTC != ‘’ )
{
replaceUTC = strReplace(replaceUTC, ‘T’, ’ ');
replaceUTC = replaceUTC + ‘.270’;
}
line2 += [ replaceUTC ];
replaceUTC = ‘’;
}
else if(dictField.baseType() == Types::String)
{

line2 += [ ‘"’ + (tableBuffer.(dictField.id())) + ‘"’ ];
}
else
{
line2 += [ tableBuffer.(dictField.id()) ];
}
}
}
asci.write(line2);
line2 = ConNull();
recordsExist=true;
}
countTable++;
executedTable++;
CodeAccessPermission::revertAssert();
info(strFmt(‘Exported %1’,fileName));
}
}
info(strFmt(’%1 tables exported out of %2’, executedTable, countTable));
}

It’s not about CSV itself - CSV is a simple text format. Your problem is related to Microsoft Excel.

The reason why you don’t see full names on the other rows is that the subsequent column (TaxGroup) has a value. It’s empty on row 17, allowing the full name to be displayed.

Note that you can configure how Excel display values. For example, you can right-click the cell, choose Format Cells, switch to Alignment tab and change Horizontal property to Fill.

Hi Martin,

Thanks for the reply.

Actually row 17 has TaxGroup value as ‘Default’, it is showing in row 18 column D due to this collapse.

Sorry.

it is showing in row 19 column D due to this collapse.

Aha, I see.

Look into the actual CSV. I suspect that you’ll see that Name contains line break and it’s not correctly wrapped in quotation marks.

Hi Martin,

My problem is row 18 name value not showing in single cell like others. It is split up into row 19 columns A,B,C.

You’re still looking at the visualization in Excel, not at CSV. CSV is a text format, therefore use a text editor to see what CSV your code generated.

By the way, using AsciiIO class looks wrong to me. Try CommaTextIO class instead.

Hi Martin,

I tried CommaTextIO, but this this time all the values inserted in a single cell.

Also, we don’t want double quotes on header row(1st row)

Forget the visualization in Excel for now and focus on CSV.

When you have correct CSV, we can discuss how to work with it in Excel (e.g. how to use Data > From Text/CSV).

Martin,

I found the reason for this collapse. In the name field there are double quotes and extra commas in values. That is causing the issue. Please find below highlighted.

I have added below highlighted condition on my previous code.

Now the output looks good. Anyhow i will check with my client whether this approach(removing extra quotes,commas fom string) is ok for them or not.

If they are not convinced i will recommend them the approach you suggested(CommaTextIo) and let see… Anyhow thanks for your immediate response and solutions.

Thanks,