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

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

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

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

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)

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 ','

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 )

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 !

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;

.,...

}

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.

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

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

}

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?

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'

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

Can you post your code and you csv file?

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

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

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.

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

Did you set stringDelimiter parameter?

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'

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

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.

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

Check out the upcoming Expert Session: Understanding ThingWorx Navigate Licensing in Community "Customer Events" section.