How to read csv/excel file and get columns data

[DD_7886593]

Hello, I would like to read CSV file in the thingworx platform, I execute these java code into a service:

var params = {
    path: "TF.csv" /* STRING */,
    columnMappings: undefined /* STRING */,
    hasHeader: undefined /* BOOLEAN */,
    longitudeField: undefined /* NUMBER */,
    dateFormat: undefined /* STRING */,
    fileRepository: "SystemRepository",
    latitudeField: undefined /* NUMBER */,
    fieldDelimiter: "," /* STRING */,
    stringDelimiter: "," /* STRING */,
    dataShape: "TFData" /* DATASHAPENAME */
};
var result = Resources["CSVParserFunctions"].ReadCSVFile(params);

When I test this code lines, I got this error:

Wrapped java.lang.Exception: Unable To Convert From java.lang.String to NUMBER Cause: Unable To Convert From java.lang.String to NUMBER

Any help please !

[CarlesColl · Accepted solution]

Ta least you need columnMappings to be set ( separated by ";" ) and I don't think it's logic to set fieldDelimiter = stringDelimiter.

[DD_7886593]

Thank you Mr.Carles,
I got this error now after deleting the stringDelimiter:

Wrapped java.lang.IndexOutOfBoundsException: Index: 1, Size: 1 Cause: Index: 1, Size: 1

[CarlesColl]

Did you set column Mappings, do you have a String Delimiter( it's mandatory)

[DD_7886593]

How can I set a column Mappings ?
In my CSV file I have a delimiter and it's ','

[CarlesColl]

Column mappings are the columns that your CSV files have, for instance if you have column1,column2,column3 on your csv file, you should set columnMappings field equal to column1;column2;column3 ( the same names as fields on the datashape TFData )

[DD_7886593]

Ok, I have done that, thank you.
How can I get the data list of each columns now, pleaase !

[CarlesColl]

What does you mean?

The result it's an infotable, just iterate over it:

for each(row in result.rows) {

     var valueColumn1 = row.column1;

.,...

}

[DD_7886593]

Thank you mr.Carles,

My columns in the csv file are:

  columnMappings: 'cell_easting,cell_northing',

When I run this:

for each(row in result.rows) {
    var valueColumn1 = row.cell_easting;
}

I don't get any result.

[CarlesColl]

Well what result do you want to get, I mean where do you want to add the result?

For instance you can log the result:

logger.info("My Value"+row.cell_easting);

To see on the logs if you are reading correctly the value.s

[DD_7886593]

I would like to shows data from CSV to a mashup.
I want to develop a service that :

• import csv file
• read it
• get data from columns
• send it to a mashup
• show it

This is my code:

var params = {
    path: "TF_Juni3.csv" /* STRING */,
    columnMappings: 'cell_easting,cell_northing' /* STRING */,
    hasHeader: true /* BOOLEAN */,
    fileRepository: "SystemRepository",
    fieldDelimiter: "," /* STRING */,
    dataShape: "TFUrbanizationData" /* DATASHAPENAME */
};
var result = Resources["CSVParserFunctions"].ReadCSVFile(params);
for each(row in result.rows) {
    logger.info("My Value"+row.cell_easting);
}

[CarlesColl]

Hi, did you set Service Output value to Infotable with dataShape "TFUrbanizaationData" you get something?

[DD_7886593]

Sorry, nothing shows.

TFUrbanizationData  is a data shape with two fields 'cell_easting' ,'cell_northing'

[CarlesColl]

Can you post your code and you csv file?

[DD_7886593]

Hi Mr.Carles, I have add some lines and got this now and that's great.

The last line shows:

More results ... use mashup to see more results or look in browser 'Network' tab in development tools.

[CarlesColl]

For the empty values, as said before on the thread, String Delimiter it's mandatory, your content should look like:

"column1","column2"

"value1.1","value1.2"

"value2.1","value2.2"

instead of

column1,column2

value1.1,value1.2

value2.1,value2.2

and set stringDelimiter parameter to "\""

[DD_7886593]

This is my CSV file look like from the top:

"cell_easting","cell_northing"

"26.161","66.49312"

But I got empty table.

[CarlesColl]

Did you set stringDelimiter parameter?

[CarlesColl]

On moment I saw on one of your previous posts:

columnMappings: 'cell_easting,cell_northing'

should be:

columnMappings: 'cell_easting;cell_northing'

[DD_7886593]

Yes it is, I change that

var params = {
    path: "TF_Jun.csv" /* STRING */,
    columnMappings:'cell_easting;cell_northing',
    hasHeader: true /* BOOLEAN */,
    fileRepository: "SystemRepository",
    fieldDelimiter: "," /* STRING */,
    stringDelimiter: "\"" /* STRING */,
    dataShape: "TFUrbanizationData" /* DATASHAPENAME */
};

But the prob I got this error:

Wrapped java.lang.IndexOutOfBoundsException: Index: 1, Size: 1 Cause: Index: 1, Size: 1

[CarlesColl]

If you aren't using latest csv file importer extension, add a new line on the bottom of the file.

[DD_7886593]

Yes, the problem was I have a blank line in the last line in the csv file.

Thank you so much Mr.Carles.