Export to CSV - putting DEVs in seperate COLUMNS

[zeldererwin]

Hi there, I've worked through the code underneath. However I need my results in a different format:

Measurment #	DEV Point 1	DEV Point 2	DEV Point 3
1	xxx	xxx	xxx
2	xxx	xxx	xxx
3	xxx	xxx	xxx
3	xxx	xxx	xxx

Is there any way I can achieve this? Currently all my DEVs are in one row with ";" in between.

Thanks a lot,

Erwin

[bfire85]

In your CMM code, don't use a semi-colon. Use a comma.

FILE/WRITELINE,FPTR,1+","+LOC1.X.DEV+","+LOC2.X.DEV+","+LOC3.X.DEV
FILE/WRITELINE,FPTR,2+","+LOC4.X.DEV+","+LOC5.X.DEV+","+LOC6.X.DEV
FILE/WRITELINE,FPTR,3+","+LOC7.X.DEV+","+LOC8.X.DEV+","+LOC9.X.DEV

[Ego Murphy]

Ha ha I typed up a huge reply and missed that he had semi-colons

[Ego Murphy]

So that thread has the writing in two/three-step process, which is kinda an extra step or two.
He has us assigning data to variables and then concatenating those variables with commas into an "all" variable value.

From that thread:

ASSIGN/V1=C1.INPUT
ASSIGN/VOD=OD_HOLE_POSITION.TP.DEV
ASSIGN/VID=ID_HOLE_POSITION.TP.DEV
ASSIGN/VRALL=V1+","+VOD+","+VID

And then writing just that "all" variable value to the output file:

FILE/WRITELINE,FPTR,VRALL

More Efficient: You can skip the all variable assignments and just stick the data-grabbing expressions directly into the writing, like:

FILE/WRITELINE,FPTR,C1.INPUT+","+OD_HOLE_POSITION.TP.DEV+","+ID_HOLE_POSITION.TP.DEV

The key point is the commas: CSV stands for Comma Separated Values. When you import a CSV to Excel, those commas become columns.

So Erwin, your writing code will be like:

FILE/WRITELINE,FPTR,VariableOfMeasNumber+","+DimensionForPnt1.DEV+","+DimensionForPnt2.DEV+","+DimensionForPnt13DEV

and the commas will separate the values into columns automatically.

There is a catch, PC-DMIS has a 62-character line limit so sometimes you do have to use variables to concatenate chunks of stuff together as the quotes, plus signs, and commas add up real quick - and having long dimension names just makes it worse.
I had to do one for some software that needed a single line of raw data for a big program. This meant that the code was writing the values of 12 variables to a single line output file - but each of those 12 was created by concatenating 20 variables together - each of which itself was created by concatenating between 5 and 15 dimensional results. It took me forever to code all that jazz and they used it twice.

[AndersI]

Don't forget that "comma separated" is actually "semicolon separated" in the parts of the world that use decimal comma. And Excel may act differently depending on your national settings in Windows, AND if PC-DMIS is running or not...

The "CSV standard" is not a can of worms, it's a whole cropland of them...

[Ego Murphy]

I did not know that - thank you Anders!

[zeldererwin]

THANKS A BUNCH!

Guys I'm really appreciating this input! Makes me very happy. In fact I only started in this company a month and a half ago, and my colleague used to copy and paste the data from every single report into one file. I said, "I bet there is a more practical solution" so I started writing the code. And you helped me enormously to help my mate. thanks!

[zeldererwin]

Hi, I've implemented the code like Ego Murphy suggested.. however it keeps overwriting the results and I want the results to end up in a table.. underneath each other.. any suggestions?

            IF/V1==0
FPTR         =DATEI/ÖFFNEN,Q:\Messtechnik\03_PROTOKOLLE\KMG\PORSCHE\J1\961_TiTo_Basis_vorne\20190430_PPE_mit Cubing\PPE.csv,SCHREIBEN
              DATEI/ZEILE_SCHREIB,FPTR,MessungNr;Merkmal 5;Merkmal 6;Merkmal 7;Merkmal 8;Merkmal 9;Merkmal 10;Merkmal 11;Merkmal 12;Merkmal 13
              DATEI/SCHLIESSEN,FPTR,BEIBEHALTEN
            END_IF/
FPTR       =DATEI/ÖFFNEN,Q:\MESSTECHNIK\03_PROTOKOLLE\KMG\PORSCHE\J1\961_TITO_BASIS_VORNE\20190430_PPE_MIT CUBING\PPE.CSV,ANHÄNGEN
            DATEI/ZEILE_SCHREIB,FPTR,PART+";"+LAGE5.T.DEV+";"+LAGE6.T.DEV+";"+LAGE7.T.DEV+";"+LAGE8.T.DEV+";"+LAGE9.T.DEV+";"+LAGE10.T.DEV+";"+LAGE11.T.DEV+";"+LAGE12.T.DEV+";"+LAGE13.T.DEV
            DATEI/SCHLIESSEN,FPTR,BEIBEHALTEN

this is what it looks like at the moment.. T04 being der 4th part, -2 meaning, second measurement.

MessungNr	Merkmal 5	Merkmal 6	Merkmal 7	Merkmal 8	Merkmal 9	Merkmal 10	Merkmal 11	Merkmal 12	Merkmal 13
T04-2	0.413	-0.511	-0.479	-0.34	-0.255	-0.398	-0.555	0.166	-0.174

thx in advance,

Erwin

[AndersI]

... however it keeps overwriting the results and I want the results to end up in a table.. underneath each other.. any suggestions?

 IF/V1==0
FPTR =DATEI/ÖFFNEN,Q:\Messtechnik\03_PROTOKOLLE\KMG\PORSCHE\J1\961_TiTo_Basis_vorne\20190430_PPE_mit Cubing\PPE.csv,[B][COLOR=#FF0000]SCHREIBEN[/COLOR][/B]
DATEI/ZEILE_SCHREIB,FPTR,MessungNr;Merkmal 5;Merkmal 6;Merkmal 7;Merkmal 8;Merkmal 9;Merkmal 10;Merkmal 11;Merkmal 12;Merkmal 13
DATEI/SCHLIESSEN,FPTR,BEIBEHALTEN
END_IF/
FPTR =DATEI/ÖFFNEN,Q:\MESSTECHNIK\03_PROTOKOLLE\KMG\PORSCHE\J1\961_TITO_BASIS_VORNE\20190430_PPE_MIT CUBING\PPE.CSV,[B][COLOR=#008080]ANHÄNGEN[/COLOR][/B]
DATEI/ZEILE_SCHREIB,FPTR,PART+";"+LAGE5.T.DEV+";"+LAGE6.T.DEV+";"+LAGE7.T.DEV+";"+LAGE8.T.DEV+";"+LAGE9.T.DEV+";"+LAGE10.T.DEV+";"+LAGE11.T.DEV+";"+LAGE12.T.DEV+";"+LAGE13.T.DEV
DATEI/SCHLIESSEN,FPTR,BEIBEHALTEN

It does exactly what you tell it to - overWRITE the file when writing the header, then APPEND-ing the data line. It should rather be like (pseudo code):

if not fileexist(your file name) then
  open the file in WRITE mode
  write header
  close file
end if

<do the measure>

open the file in APPEND mode
write the data
close file

[Ego Murphy]

Yes, flip SCHREIBEN to Anhängen and see if that works

Also make sure your code doesn't re-create the file if it exists.