Finance | Project Operations, Human Resources, ...

Read CSV with values enclosed with double quotes with comma

(1) Share

Report

Posted on by Sandeep Nathe

Hello guys,

I'm stucked in one custom csv file import process. Your help much appreciated.

following steps as below,
1. I am uploading file using temporary storage class as below

FileUploadTemporaryStorageResult fileUpload = File::GetFileFromUser() as FileUploadTemporaryStorageResult;

2. To read file content I am using ASCIIStreamIO as below

AsciiStreamIo file = AsciiStreamIo::constructForRead(fileUpload.openResult());

file.inFieldDelimiter(','); //separator
file.inRecordDelimiter('\r\n');

3. to read file content in container inside while loop

Container con = file.read();

above working fine but facing issue when csv column has value with comma.

for example we one column has value like "Test1,Test2" in this case container getting two seperate element as "Test1 and Test2".

It should consider Test1,Test2 as single element.

I tried same with CommaStreamIO, CommaTextStreamIO, TextStreamIO but still same result.

Please help me on this.

All responses (3)

Answers (0)

Suggested answer

Sandeep Nathe 7 on at

Like (0)

Report

Read CSV with values enclosed with double quotes with comma

Thanks Martin,

As per your suggested way, applied UTF8 encoding on memorystream and read with commatextstreamio, I was able to get the double quoted comma string as single element where string as input.

In my case to reading csv file which is reading in form of io stream so added code to convert file line record to string and pass this string to the code you suggested and it worked as per my expectation.

Below is the new code,

container values;
if (fileUpload && fileUpload.getUploadStatus())
{
var encoding = System.Text.Encoding::UTF8;

using (var sr = new System.IO.StreamReader(fileUpload.openResult()))
{
var text = sr.ReadLine();
text = sr.ReadLine();
While(!System.String::IsNullOrEmpty(text))
{
using (var stream = new System.IO.MemoryStream(encoding.getBytes(text.toString())))
{
CommaTextStreamIo io = CommaTextStreamIo::constructForRead(stream);
values = io.read();
text = sr.ReadLine();
}
}
}
}
Suggested answer

Martin Dráb 230,503 Most Valuable Professional on at

Like (2)

Report
Read CSV with values enclosed with double quotes with comma

F&O can handle that. For example, try this code:
str text = '"a","x,y,z","b"'; var encoding = System.Text.Encoding::UTF8; using (var stream = new System.IO.MemoryStream(encoding.GetBytes(text))) { CommaTextStreamIo io = CommaTextStreamIo::constructForRead(stream); container values = io.read(); }

You'll get a container with three values, as intended:

Using AsciiStreamIo sounds suspicious to me, because you rarely run into ASCII files these days.
Suggested answer

Jonas "Jones" Melgaard 1,983 on at

Like (1)

Report

Read CSV with values enclosed with double quotes with comma

Edit: Disregard my answer 😇
No out-of-the-box methods in F&O can handle this, unfortunately.

I haven’t tested it, but the CSV importer used for tax services implements the TextFieldParser from VisualBasic, which can handle this scenario. I’d suggest copying that method into your own class.

Method: TaxServiceTaxFeatureSetupCsvImporter.getHeaderFieldMapFromStr(str __strLine)

https://learn.microsoft.com/en-us/dotnet/api/microsoft.visualbasic.fileio.textfieldparser.hasfieldsenclosedinquotes?view=net-8.0#microsoft-visualbasic-fileio-textfieldparser-hasfieldsenclosedinquotes