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

Community Tip - Need to share some code when posting a question or reply? Make sure to use the "Insert code sample" menu option. Learn more! X

How to map CSV to datashape with fewer columns

aitc-plmsupport
8-Gravel

How to map CSV to datashape with fewer columns

Hi Guys,

 

I have a csv file with 15 columns out of it want to read only 10. I have a datashape with 10 columns and i need help to map the datashape columns with the csv columns.

 

columnMappings: 'dscolumn1;dscolumn2;dscolumn3;dscolumn4'

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hello,

 

Absolutely.

These are from my CSV file

DMC,Machine,Pallet,MachineTimestamp,AdapterplateID,NCprogram,NCprogramCycleTime,ToolID,WashTimestamp,GuideSeatAssistanceStatus,GuideSeatPositions,DeburringStroke,DeburringTimestamp

 

Your naming have to be the exact same in your datashape as you have in your CSV file (or at least that has been my experience).

 

So if we pretend these are your CSV columns as well, and you want to use the second column (Machine) with your first datashapes field definition, then just name your field definition to "Machine".

It is the same if you want to use the fourth column (MachineTimestamp) from the CSV file as your second column, just name your second datashape field definition as "MachineTimestamp".

And also lets pretend that you want the 7th column (NCprogramCycleTime) as your third column, just name your third datashape field definition as "NCprogramCycleTime".

 

What is important is your column mappings:

jensc_0-1674114385917.png

 

It needs to contain all of the columns up until the last one you want, as you can see, I leave the ones that I don't want like "DMC", "Pallet" etc.

But I do get the correct values in my infotable from column 2, 4 and 7:

jensc_1-1674114477313.png

Below is the raw data from my CSV, each "column" seperated by a comma:

jensc_3-1674114561196.png

You can see, my frist value ending in 001# does not come in my infotable resuls. Instead, my second value (3) comes.

And so on until the 7th value (12) ends up in the third field definition of my datashape.

 

I can't really explain what happens in the background with this, perhaps there is some information about the function; 

Resources["CSVParserFunctions"].ReadCSVFile(params)
Somwehere that you can find.
 
Or if someone from PTC can explain how it works or if there is a better way to do it.
 
Hope this helps you,
 
Regards,
Jens

 

View solution in original post

3 REPLIES 3

Hello,

 

Lets say my datashape; PPA.AllData contains: "DMC" and "Machine" properties/columns.

My datashape:

jensc_1-1674034070937.png

 

But my CSV file contains the four columns as in the code below.

When I run the following code:

 

var params = {
  path: "/File2.csv" /* STRING */,
  columnMappings:"DMC;Machine;Pallet;MachineTimestamp" /* STRING */,
  hasHeader: true /* BOOLEAN */,
  longitudeField: undefined /* NUMBER */,
  dateFormat: undefined /* STRING */,
  fileRepository: "IntegrationTestRepo" /* THINGNAME */,
  latitudeField: undefined /* NUMBER */,
  fieldDelimiter: "," /* STRING */,
  stringDelimiter: undefined /* STRING */,
  dataShape: "PPA.AllData" /* DATASHAPENAME */,
};
let csvFile = Resources["CSVParserFunctions"].ReadCSVFile(params); // result: INFOTABLE
result = csvFile;

 

 I only get the columns that are in my datashape in the result:

jensc_0-1674033978494.png

And so you don't even really need to have all of the columns in the "columnMapping". 

Just put the ones you have in your datashape.

 

Regards,

Jens

Hi @jensc 

 

Can you please paste your csv headers for more understanding?

And I want to map my first datashape column with my csv's second column, my second datashape column with my csv's fourth column.... like that I have unwanted columns in between in the exported csv file, at last the csv column count is not fixed. In future there can be additional columns (new columns) possibly.

Hello,

 

Absolutely.

These are from my CSV file

DMC,Machine,Pallet,MachineTimestamp,AdapterplateID,NCprogram,NCprogramCycleTime,ToolID,WashTimestamp,GuideSeatAssistanceStatus,GuideSeatPositions,DeburringStroke,DeburringTimestamp

 

Your naming have to be the exact same in your datashape as you have in your CSV file (or at least that has been my experience).

 

So if we pretend these are your CSV columns as well, and you want to use the second column (Machine) with your first datashapes field definition, then just name your field definition to "Machine".

It is the same if you want to use the fourth column (MachineTimestamp) from the CSV file as your second column, just name your second datashape field definition as "MachineTimestamp".

And also lets pretend that you want the 7th column (NCprogramCycleTime) as your third column, just name your third datashape field definition as "NCprogramCycleTime".

 

What is important is your column mappings:

jensc_0-1674114385917.png

 

It needs to contain all of the columns up until the last one you want, as you can see, I leave the ones that I don't want like "DMC", "Pallet" etc.

But I do get the correct values in my infotable from column 2, 4 and 7:

jensc_1-1674114477313.png

Below is the raw data from my CSV, each "column" seperated by a comma:

jensc_3-1674114561196.png

You can see, my frist value ending in 001# does not come in my infotable resuls. Instead, my second value (3) comes.

And so on until the 7th value (12) ends up in the third field definition of my datashape.

 

I can't really explain what happens in the background with this, perhaps there is some information about the function; 

Resources["CSVParserFunctions"].ReadCSVFile(params)
Somwehere that you can find.
 
Or if someone from PTC can explain how it works or if there is a better way to do it.
 
Hope this helps you,
 
Regards,
Jens

 

Top Tags