How to map CSV to datashape with fewer columns

[aitc-plmsupport]

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'

[jensc]

Hello,

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

My datashape:

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:

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

[aitc-plmsupport]

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.

[jensc · Accepted solution]

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:

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:

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

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