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

Community Tip - Did you know you can set a signature that will be added to all your posts? Set it here! X

How to read csv/excel file and get columns data

DD_7886593
7-Bedrock

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 !

ACCEPTED SOLUTION

Accepted Solutions

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

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

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

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

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

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 )

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

What does you mean?

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

for each(row in result.rows) {

     var valueColumn1 = row.column1;

.,...

}

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.

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

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

}

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

Sorry, nothing shows.

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

Can you post your code and you csv file?

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

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

This is my CSV file look like from the top:

                      

"cell_easting","cell_northing"

"26.161","66.49312"

But I got empty table.

Did you set stringDelimiter parameter?

On moment I saw on one of your previous posts:

columnMappings: 'cell_easting,cell_northing'

should be:

columnMappings: 'cell_easting;cell_northing'

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

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

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

Thank you so much Mr.Carles.

Announcements


Top Tags