Skip to main content
A
aitc-plmsupport9-Granite
9-Granite
January 18, 2023
Solved

How to map CSV to datashape with fewer columns

  • January 18, 2023
  • 1 reply
  • 1941 views

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'

 

 

 

 

 

 

 

Best answer by jensc

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

 

1 reply

J
jensc17-Peridot
17-Peridot
January 18, 2023

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

A
aitc-plmsupport9-GraniteAuthor
9-Granite
January 19, 2023

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.

J
jensc17-PeridotAnswer
17-Peridot
January 19, 2023

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

 

Terms & ConditionsCookie settingsAccessibility statement