cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

How to read csv/excel file and get columns data

SOLVED
Highlighted
Not applicable

How to read csv/excel file and get columns data

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 !

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

Re: How to read csv/excel file and get columns data

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

View solution in original post

21 REPLIES 21
Highlighted

Re: How to read csv/excel file and get columns data

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

View solution in original post

Highlighted
Not applicable
(in response to)

Re: How to read csv/excel file and get columns data

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

Highlighted

Re: How to read csv/excel file and get columns data

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

Highlighted

Re: How to read csv/excel file and get columns data

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

Highlighted

Re: How to read csv/excel file and get columns data

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 )

Highlighted

Re: How to read csv/excel file and get columns data

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

Highlighted

Re: How to read csv/excel file and get columns data

What does you mean?

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

for each(row in result.rows) {

     var valueColumn1 = row.column1;

.,...

}

Highlighted

Re: How to read csv/excel file and get columns data

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.

Highlighted

Re: How to read csv/excel file and get columns data

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

Highlighted

Re: How to read csv/excel file and get columns data

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

Screenshot-2017-10-24 ThingWorx Composer.png

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);

}

Highlighted

Re: How to read csv/excel file and get columns data

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

Highlighted

Re: How to read csv/excel file and get columns data

Sorry, nothing shows.

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

Highlighted

Re: How to read csv/excel file and get columns data

Can you post your code and you csv file?

Highlighted

Re: How to read csv/excel file and get columns data

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.Screenshot-2017-10-24 ThingWorx Composer(1).png

Highlighted

Re: How to read csv/excel file and get columns data

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 "\""

Highlighted

Re: How to read csv/excel file and get columns data

This is my CSV file look like from the top:

                      

"cell_easting","cell_northing"

"26.161","66.49312"

But I got empty table.

Highlighted

Re: How to read csv/excel file and get columns data

Did you set stringDelimiter parameter?

Highlighted

Re: How to read csv/excel file and get columns data

On moment I saw on one of your previous posts:

columnMappings: 'cell_easting,cell_northing'

should be:

columnMappings: 'cell_easting;cell_northing'

Highlighted

Re: How to read csv/excel file and get columns data

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

Highlighted

Re: How to read csv/excel file and get columns data

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

Highlighted

Re: How to read csv/excel file and get columns data

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

Thank you so much Mr.Carles.

Announcements

Thingworx Navigate content has a new home! Click here to access the new Thingworx Navigate forum!