Getting blank rows of record when I am using ReadCSVFiles service of CSVParserFuntions for reading data from CSV files

[RohitKumar_Jais]

Hi,

Please find attachments,

I am using ReadCSVFiles service of CSVParserFuntions for reading data from CSV files. CSV file contains 3 row of records actually (You can see in attached EmpDEtails.csv file).

but when I execute this service I didn't get any record as output but I got 3 empty rows. I don't understand why It's happening.

Please check script code below and let me know what I am missing so that I can fix it and get csv data and load it into grid.

Script Code:

var params = {   
    path: 'EmpDetails.csv' /* STRING */,
columnMappings: 'FirstName,LastName,DOB' /* STRING */,
    hasHeader: true /* BOOLEAN */,
//longitudeField: undefined /* NUMBER */,
//dateFormat: undefined /* STRING */,*
fileRepository: 'EDURepository' /* THINGNAME */,
//latitudeField: undefined /* NUMBER */,
fieldDelimiter: ',' /* STRING */,
//stringDelimiter: '¨' /* STRING */,
dataShape: 'EmpDetailsDataShape' /* DATASHAPENAME */
};
// result: INFOTABLE
var result = Resources["CSVParserFunctions"].ReadCSVFile(params);
logger.warn('result:'+result);

var tableLength = result.rows.length;
logger.warn('tableLength: '+tableLength);
for (var x = 0; x < tableLength; x++) {
var row = result.rows;
    logger.warn('row: '+row);
logger.warn('FirstName: '+row.FirstName+'  LastName: '+row.LastName+'  DOB: '+row.DOB);
}

Please do the needful asap.

From,

Rohit Kumar Jaiswal

[CarlesColl]

Hi Rohit,

Some things:

• columnMappings --> Separator it's ";" instead of ","
• stringDelimiter --> It's mandatory, by default "\"", I mean csv content should have stringDelimiter for each value
• If you are using an old version of the CSV extension then you need a line feed at the end of the file

Your code should like:

var result = Resources["CSVParserFunctions"].ReadCSVFile({
path: "EmpDetails.csv",
columnMappings: "FirstName;LastName;DOB",
hasHeader: true /* BOOLEAN */,
//longitudeField: undefined /* NUMBER */,
//dateFormat: undefined /* STRING */,
fileRepository: "EDURepository" /* THINGNAME */,
latitudeField: undefined /* NUMBER */,
fieldDelimiter: "," /* STRING */,
stringDelimiter: "\"",
dataShape: "EmpDetailsDataShape"
});

and your csv file:

"FirstName","LastName","DOB"
"Vasu","tada","22jun"
"Rohit","jais","16jul"
"Tamal","sarkar","12jul"

[RohitKumar_Jais]

Hi Carles Coll,

As suggested by you please check my below script

var params = {  

    path: "EmpDetails.csv" /* STRING */,

columnMappings: "FirstName;LastName;DOB" /* STRING */,

    hasHeader: true /* BOOLEAN */,

//longitudeField: undefined /* NUMBER */,

//dateFormat: undefined /* STRING */,

fileRepository: "EDURepository" /* THINGNAME */,

latitudeField: undefined /* NUMBER */,

fieldDelimiter: "," /* STRING */,

stringDelimiter: "\"" /* STRING */,

dataShape: "EmpDetailsDataShape" /* DATASHAPENAME */

};

// result: INFOTABLE

var result = Resources["CSVParserFunctions"].ReadCSVFile(params);

and CSV file:

¨FirstName¨,¨LastName¨,¨DOB¨

¨Vasu¨,¨tada¨,¨22jun¨

¨Rohit¨,¨jais¨,¨16jul¨

¨Tamal¨,¨sarkar¨,¨12jul¨

I am getting output as follows:

I unable to understand why I am getting ? character as a prefix and postfix for each field value.

Please let me know how can I fix it.

From,

Rohit kumar Jaiswal

[CarlesColl]

It maybe a codification issue UTF-8 and alike, why are you using ¨ instead of " ? you may try with single quote '

[RohitKumar_Jais]

I don't understand of your sentence "why are you using ¨ instead of " ? you may try with single quote ' ". Please explain it little more.

If I am having below content in CSV file

FirstName,LastName,DOB

Vasu,tada,22-Jun

Rohit,jais,16-Jul

Tamal,sarkar,12-Jul

and when comment this line ' stringDelimiter: "\"" ,   in script code then I got following output as expected in proper way:

so what is the meaning of default value of stringDelimiter in this context. If I did anything wrong then please let me know.

From

Rohit Kumar Jaiswal

[CarlesColl]

I think they corrected few things from this extension, for what I see it works well now, I'm right?

[RohitKumar_Jais]

Yes Carles Coll. It's working fine now.

Now I have another requirement like How to read csv/excel file that reside on directory path instead Thingworx File repository directory so that I can fetch data from files that reside anywhere may be on any network path.

Example:

In below example you can see I want to read a csv file 'EmpDetails.csv' that reside under path 'C:/Users/NG6F868/Desktop' instead Thingworx File Repository  'EDURepository' but I am getting error 'Wrapped com.thingworx.common.exceptions.InvalidRequestException: File Repository [C:/Users/NG6F868/Desktop] Does Not Exist Cause: File Repository [C:/Users/NG6F868/Desktop] Does Not Exist' when I test the service.

var params = {   
    path: ¨EmpDetails.csv¨ /* STRING */,
columnMappings: ¨FirstName;LastName;DOB¨ /* STRING */,
    hasHeader: true /* BOOLEAN */,
//longitudeField: undefined /* NUMBER */,
//dateFormat: undefined /* STRING */,*
    fileRepository: ¨C:/Users/NG6F868/Desktop¨,
//fileRepository: ¨EDURepository¨ /* THINGNAME */,
//latitudeField: undefined /* NUMBER */,
fieldDelimiter: ¨,¨ /* STRING */,
//stringDelimiter: undefined /* STRING */,
dataShape: ¨EmpDetailsDataShape.csv¨ /* DATASHAPENAME */
};

// result: INFOTABLE
var result = Resources["CSVParserFunctions"].ReadCSVFile(params);

Is there any way to fix this issue and fulfil my requirements?

Please do the needful as soon as possible.

Thank you in advance.

From,

Rohit Kumar Jaiswal

[CarlesColl]

There is not OOTB feature to read a file from a Network Drive.

You can either build a Java extension which give you access to the network drive, or you can create a Remote File Repository EDGE thing which connects to ThingWorx.

[RohitKumar_Jais]

How can I create Remote File Repository EDGE thing. Could you please help me to develop this functionality?

[CarlesColl]

Hi Rohit,

Please check documentation, it's nothing that can be written in two sentences.

[RohitKumar_Jais]

okay Carles. I check in documentation of How to develop extension.

Hello,

I have a CSV file that contain data as below screenshot (opened in Microsoft Excel).

I want to read these data through ReadCSVFiles service of CSVParserFuntions? I have a doubt because columnMappings is one parameter of ReadCSVFiles service where we mention all column names but here below sample data is in different format some rows have different column header and others have different. You can see in attached Image.

How can accomplish this task in this way or is there alternative way we have in Thingworx platform? please let me know.

Thanks in advance.

From,

Rohit Kumar Jaiswal

[CarlesColl]

Hi Rohit,

CSV Extension it's not your tool for this kind of multi-format csv files, you will have parse the file by hand using FileRepository TXT services ( LoadText for instance )

[RohitKumar_Jais]

Thank you so much