How do I save data from my PLC in Excel or CSV format in TwinCAT?

Question

I have to record data like JobNo, processing time, JobID, process Number from a simulator governed by Beckhoff PLC and write this data in a form of CSV or excel file. for example if JobNo is 1 then after processing the all the data should be recorded under corresponding columns and when next job is encountered the data is recorded in next row. Following should be how the excel file should look like

Jobno	ProcessTime	JobID	ProcessNo
1	20	100	200
2	40	101	320

Till now I have followed online resources by Beckhoff and even contacted their support team, but the codes I got from them results in some sort of error. I also wrote my problems in some forums but the answers I got there were too complex and advanced. It would be helpful if you could provide a sample of file writing program Here are all the DUTs and GVLs and MAIN code I used for this purpose : DUT named ST_data

TYPE ST_data :
STRUCT
    JOB_ID          : STRING(255);
    Processing_Time : STRING(255);
    Setup_Time      : STRING(255);
    Due_Date        : STRING(255);
END_STRUCT
END_TYPE

GVL named GVL

{attribute 'qualified_only'}
VAR_GLOBAL
    MACHINE : ARRAY[1..50] OF ST_data;
    sen5 : BOOL;
    sen2 : BOOL;
    state : BOOL;
    count :INT;
    hit: BOOL;
    tdelta : ULINT;
    tstart : ULINT;
    tend : ULINT;
    setuptime : LREAL;
END_VAR

MAIN Program code Local Variables------------------------------------------------

PROGRAM MAIN
VAR

    FB_FileOpen: FB_FileOpen;
    FB_FileWrite: FB_FileWrite;
    FB_FileClose: FB_FileClose;
    
    hFile: UINT;
    sPathName: T_MaxString;
    sWriteBuffer : STRING(5000);
    sBufferTemp : STRING(1000); // Temporary string that will hold the string that needs to be added to the full string
    bBuffTemp : BOOL ; //check if the strings have been concatinated
    sFormat : STRING(255);
    
    state: INT;
    bWrite: BOOL;
    NT_GetTime: NT_GetTime;
    
    bFill: BOOL;
    i: INT;
    FB_FormatString2: FB_FormatString2;
    
    trigger: BOOL;
END_VAR

code-----------------------------------------------------------------

//counter code
//==========================================================================================
IF GVL.sen5 = FALSE AND GVL.hit = FALSE THEN
    GVL.count := GVL.count +1;
    GVL.hit := TRUE;
ELSIF GVL.sen5 = TRUE AND GVL.hit = TRUE THEN
    GVL.hit := FALSE;
END_IF

IF gvl.sen5= TRUE THEN
    GVL.tstart := F_GetSystemTime();
END_IF
IF GVL.sen2 = TRUE THEN
    GVL.tend := F_GetsystemTime();
    //trigger := TRUE; // this will trigger the file writing commands
END_IF
GVL.tdelta := GVL.tend - GVL.tstart;
GVL.setuptime := GVL.tdelta*EXPT(10,-7);
//Entering some value in array
//==========================================================================================
IF bFill THEN
    FOR i := 1 TO 50 BY 1 DO
        GVL.MACHINE[i].Due_Date :='10823'; //WORD_TO_STRING(NT_GetTime.TIMESTR.wDayOfWeek);
        GVL.MACHINE[i].JOB_ID := INT_TO_STRING(GVL.count) ;//INT_TO_STRING(i);
        GVL.MACHINE[i].Processing_Time := '125';//WORD_TO_STRING(NT_GetTime.TIMESTR.wMinute);
        GVL.MACHINE[i].Setup_Time := LREAL_TO_STRING(GVL.setuptime);//WORD_TO_STRING(NT_GetTime.TIMESTR.wSecond);
    END_FOR
    bFill := FALSE;
END_IF


//Functioin block to get local time
//===========================================
IF NT_GetTime.start AND NOT NT_GetTime.BUSY THEN
    NT_GetTime.START :=FALSE;
ELSE
    NT_GetTime.START := TRUE;
END_IF
NT_GetTime(
    NETID:='' , 
    START:= , 
    TMOUT:= , 
    BUSY=> , 
    ERR=> , 
    ERRID=> , 
    TIMESTR=> );
//CASE STATEMENT to handle writing
//===========================================
CASE state OF
    0:
        IF bWrite THEN // switch to true or false to control the execution
            State :=5;
            bWrite := FALSE;
        END_IF
    5:// creating the file 
        sPathName := CONCAT('C:\Users\Manjot Sanghera\Desktop\CSVRECORD',WORD_TO_STRING(NT_GetTime.TIMESTR.wDay));
        sPathName := CONCAT(sPathName,'.');
        sPathName := CONCAT(sPathName,WORD_TO_STRING(NT_GetTime.TIMESTR.wHour));
        //sPathName := CONCAT(sPathName,'.');
        //sPathName := CONCAT(sPathName,WORD_TO_STRING(NT_GetTime.TIMESTR.wMinute));
        sPathName := CONCAT(sPathName,'_TestFile.csv');
        State := 10;
        FB_FileOpen.bExecute := TRUE;
        
    10:
        FB_FileOpen.bExecute := TRUE;
        IF NOT FB_FileOpen.bBusy AND NOT FB_FileOpen.bError THEN
            FB_FileOpen.bExecute := FALSE;
            State := 15;
        END_IF
    15 :
        sWriteBuffer :='Due_Date, Job_ID, Processin_Time,Setup_Time $n';
        sFormat :='%s, %s, %s, %s $n';
        FOR i := 1 TO 50 BY 1 DO // loop is required so that you can loop through each line/row/arrayelement
            FB_FormatString2(
                pFormatString:=  ADR(sFormat), 
                arg1:= F_STRING(GVL.MACHINE[i].Due_Date), 
                arg2:= F_STRING(GVL.MACHINE[i].JOB_ID), 
                arg3:= F_STRING(GVL.MACHINE[i].Processing_Time), 
                arg4:= F_STRING(GVL.MACHINE[i].Setup_Time),  
                pDstString:= ADR(sBufferTemp) , 
                nDstSize:= SIZEOF(sBufferTemp), 
                bError=> , 
                nErrId=> );
            bBuffTemp := CONCAT2(   pSrcString1 := ADR(sWriteBuffer),//main string 
                                    pSrcString2 := ADR(sBufferTemp), //String to be added
                                    pDstString  := ADR(sWriteBuffer),//destinatioin of the string
                                    nDstSize := SIZEOF(sWriteBuffer));//size of the final string
        END_FOR
        //sWriteBuffer := 'Job_ID__|__Processing_Time__|__SetupTime';
        State := 20;
        FB_FileWrite.bExecute := TRUE;
    20:
        FB_FileWrite.bExecute := TRUE;
        IF NOT FB_FileWrite.bBusy AND NOT FB_FileWrite.bError THEN
            FB_FileWrite.bExecute := FALSE;
            State := 30;
            FB_FileClose.bExecute := TRUE;
        END_IF
    30:
        IF NOT FB_FileClose.bBusy AND NOT FB_FileClose.bError THEN
            FB_FileClose.bExecute := FALSE;
            State:= 0;
        END_IF      
END_CASE

//OPEN, WRITE, CLOSE FILE
//===========================================
FB_FileOpen(
    sNetId:= '', 
    sPathName:= sPathName , 
    nMode:= FOPEN_MODEWRITE OR FOPEN_MODEPLUS, 
    ePath:= PATH_GENERIC, 
    bExecute:= , 
    tTimeout:= , 
    bBusy=> , 
    bError=> , 
    nErrId=> , 
    hFile=> hFile);
    
FB_FileWrite(
    sNetId:= '', 
    hFile:= hFile, 
    pWriteBuff:= ADR(sWriteBuffer) , 
    cbWriteLen:= SIZEOF(sWriteBuffer), 
    bExecute:= , 
    tTimeout:= , 
    bBusy=> , 
    bError=> , 
    nErrId=> , 
    cbWrite=> );
    
FB_FileClose(
    sNetId:= '', 
    hFile:= hFile  , 
    bExecute:= , 
    tTimeout:= , 
    bBusy=> , 
    bError=> , 
    nErrId=> );

EXPECTED FUNCTION OF PROGRAM counter counts the object and fills the values of JobID, Process No, and other values in the first row of the table and works only after these values for the second job are available

Answer 1

From the comments, here is a program with a FB for writing to a file and the MAIN which implements the demo to send new data out to the file. The data is written in CSV format. You can modify what to write to the file, what file, what is the data format, ... This should suffice for something to go off of I hope.

Since you are new, here is an image of how the folder structure looks like, since I used methods. Here is a ling to more info about Object Oriented Programming with TwinCAT 3

File writing function block implementation: Variables declaration:

FUNCTION_BLOCK FB_FileWriter
VAR_INPUT
    sFilePath   : T_MaxString;
    sFileName   : T_MaxString;
    sNetId      : STRING;   // NetId of the target, leave empty for local
END_VAR
VAR
    fbFileOpen  : FB_FileOpen;
    fbFilePuts  : FB_FilePuts;
    fbFileClose : FB_FileClose;
    nFileHandle : UINT;
    
    arrBuffer   : ARRAY[0..100] OF T_MaxString;
    eFileWriteState : (IDLE, OPEN_FILE, WRITE_TO_FILE, CLOSE_FILE, ERROR);
END_VAR

Body :

CASE eFileWriteState OF
    IDLE:
        // Make sure all the file writing FBs are ready for use (trigger on execute)
        Init();
        
        // We have pending data to be written
        IF arrBuffer[0] <> '' THEN
            eFileWriteState := OPEN_FILE;
        END_IF
        
    OPEN_FILE:
        // Opens a file for writing at the end of the file (append). 
        // If the file does not exist, a new file is created.
        fbFileOpen(
            bExecute    := TRUE,
            sNetId      := sNetId,
            sPathName   := CONCAT(sFilePath, sFileName),
            nMode       := FOPEN_MODEAPPEND);
            
        IF fbFileOpen.bError THEN
            eFileWriteState := ERROR;
        ELSIF NOT fbFileOpen.bBusy THEN
            nFileHandle := fbFileOpen.hFile;
            fbFileOpen(bExecute := FALSE);
            eFileWriteState := WRITE_TO_FILE;
        END_IF
    
    WRITE_TO_FILE:
        fbFilePuts(
            bExecute    := TRUE,
            sNetId      := sNetId,
            hFile       := nFileHandle,
            sLine       := arrBuffer[0]);
        IF fbFilePuts.bError THEN
            eFileWriteState := ERROR;
        ELSIF NOT fbFileClose.bBusy THEN
            // Sucess, data was written
            // Rotate the buffer and close the file
            RotateBuffer();
            eFileWriteState := CLOSE_FILE; 
        END_IF
    
    CLOSE_FILE:
        fbFileClose(
            bExecute := TRUE,
            sNetId      := sNetId,
            hFile       := nFileHandle);
            
        IF fbFileClose.bError THEN
            eFileWriteState := ERROR;
        ELSIF NOT fbFileClose.bBusy THEN
            nFileHandle := 0;
            eFileWriteState := IDLE;
        END_IF      
    
    ERROR:
        // Error, clear the handle and go back to idle
        nFileHandle := 0;
        eFileWriteState := IDLE;
    END_CASE

Methods:

Init method to reset all the FBs:

METHOD PRIVATE Init : BOOL

fbFileClose(bExecute := FALSE);
fbFileOpen(bExecute := FALSE);
fbFilePuts(bExecute := FALSE);

Method to insert new data in to the buffer

METHOD PRIVATE InsertToBuffer
VAR_INPUT
    value   : STRING;
END_VAR
VAR
    nBufferIndex    : INT;
END_VAR

FOR nBufferIndex := 0 TO 100 BY 1 DO
    IF arrBuffer[nBufferIndex] = '' THEN
        // We found the free spot in the buffer, insert the value to this place
        arrBuffer[nBufferIndex] := value;
        EXIT;
    END_IF
END_FOR

Method to rotate the data in the buffer once the current data slice has been written.

METHOD PRIVATE RotateBuffer

VAR
    nIndex  : int := 0;
END_VAR

FOR nIndex := 0 TO 99 BY 1 DO
    // We can exit when we reached the empty slot in the buffer
    IF arrBuffer[nIndex] = '' THEN
        EXIT;
    END_IF
    // FIFO, removing first element in the array copying the next, etc...
    arrBuffer[nIndex] := arrBuffer[nIndex+1];
END_FOR

Public method to be called to write to the destination file

// Write to the destination file
METHOD WriteToFile : BOOL
VAR_INPUT
    message : T_MaxString;
END_VAR

InsertToBuffer(value := message);

Strucutre definition:

TYPE ST_MyData :
STRUCT
    Id          : DINT;
    Value       : DINT;
    Timestamp   : T_MaxString; 
END_STRUCT
END_TYPE

Main variables decleration:

PROGRAM MAIN
VAR
    fbWriteFile     : FB_FileWriter;
    
    arrTestData     : ARRAY[0..10] OF ST_MyData;
    nIteration      : INT;
    bStartNewJob    : BOOL;
    fbFormatString  : FB_FormatString;
    
    fbGetCurrentTaskIndex   : GETCURTASKINDEX;
END_VAR

VAR CONSTANT
    cHeader : STRING := 'ID;VALUE;TIMESTAMP';
END_VAR

Body:

fbWriteFile(
    sFileName   := 'Test.txt',
    sFilePath   := 'C:\',
    sNetId      := '');
    
// Handle this differently, but just creating the header on first PLC cycle for demo purpose
fbGetCurrentTaskIndex();
IF _TaskInfo[fbGetCurrentTaskIndex.index].FirstCycle THEN
    fbWriteFile.WriteToFile(message := cHeader);
END_IF
    
IF bStartNewJob THEN
    // Making up some fake data to store it
    arrTestData[nIteration].Id := nIteration;                   // just some made up id
    arrTestData[nIteration].Value := 5*nIteration;              // just some made up value
    arrTestData[nIteration].Timestamp := '2023.27.04_10:00';    // just some fake timestamp
    
    // Create the full line by using the FB_FormatString, way easier than CONCAT
    fbFormatString(
        sFormat := '%d;%d;%s$n',
        arg1    := F_DINT(arrTestData[nIteration].Id),
        arg2    := F_DINT(arrTestData[nIteration].Value),
        arg3    := F_STRING(arrTestData[nIteration].Timestamp));

    // Send the created line to the file writer
    fbWriteFile.WriteToFile(message := fbFormatString.sOut);

    // Increasing the counter for new, different data
    IF nIteration <10 THEN
        nIteration := nIteration + 1;
    ELSE
        nIteration := 0;
    END_IF
    
    // And finally, clear the trigger
    bStartNewJob := FALSE;      
END_IF

End result.

Initial state after first cycle, the header is created:

Answer 2

As an alternative you can try this CSV writer from benhar. You can see how to use it from the example:

PROGRAM ExampleApplication
VAR
    // application
    fileName : STRING := 'C:\myLog.csv';
    targetLogCount : UDINT := 10000; // how many records will be record in the logging session.
    loggingState: (IDLE, INITIALISE, CREATE_HEADER, CYCLIC_LOG_VALUES, COMPLETE_LOGGING) := IDLE;
    maxiumumBufferUsage : UDINT; // you must check this value to make sure it does not exceed GVL_ByteBufferConstants.FILE_WRITE_BUFFER_SIZE
    
    {attribute 'hidden'}
    fileBasedByteBuffer : FileBasedByteBuffer;
    {attribute 'hidden'}
    csvDocumentWriter : CsvByteDocumentWriter(FileBasedByteBuffer);
    {attribute 'hidden'}
    xfcChannel: INT;
    // example data
    SensorName : STRING := 'Sensor1';
    SerialNumber : INT := 123;
    SensorActive : BOOL;
    SensorValue : ARRAY [0..49] OF INT := [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50];
    
    bDutDefined: BOOL;
END_VAR

//  Set logging state to INITIALISE to run the example code. 
CASE loggingState OF
        
    IDLE: 
        // The code will wait here.  You must manually set the loggingState to INITIALISE using the online write values.
    
    INITIALISE:
    
        maxiumumBufferUsage := 0; // this example tracks the maximum buffer usage.  The default buffer size is 200KB.
        
        // The csv document requires a buffer to save the data to.  
        // Therefore before starting to write csv records we must enable the buffer by calling start, plus the file name. 
        
        IF fileBasedByteBuffer.Start(fileName) THEN
            loggingState := CREATE_HEADER;
        END_IF
        
    CREATE_HEADER: 
        
        // This section creates your header information.
        // The information will be stored as bytes.  This example also shows the conversion of an int to string. 
        csvDocumentWriter
            .StartRecord()
            .AddStringLiteral('Sensor Name')
            .AddString(SensorName)
            .EndRecord();   
        csvDocumentWriter
            .StartRecord()
            .AddStringLiteral('Sensor Serial Number')
            .AddInt(SerialNumber)
            .EndRecord();
        csvDocumentWriter
            .StartRecord()
            .AddStringLiteral('Column 1')
            .AddStringLiteral('Column 2')
            .AddStringLiteral('Column 3')
            .AddStringLiteral('Column 4')
            .AddInt(SerialNumber)
            .EndRecord();
        loggingState := CYCLIC_LOG_VALUES;
                
    CYCLIC_LOG_VALUES:
            
        // This is the logging section of the code.  You can either make a single record per cycle, or 
        // record multiples based on the array given by our XFC terminals.  In this example I save 50 XFC values plus a few other details
        // Remember, the data is stored as binary.  Use xxx_TO_STRING converters to provide strings. 
        FOR xfcChannel := 0 TO 49 DO   
                csvDocumentWriter
                    .StartRecord()
                    .AddBool(SensorActive)
                    .AddBoolLiteral(TRUE)
                    .AddInt(SensorValue[xfcChannel]) 
                    .AddIntLiteral(0)
                    .AddStringLiteral('Logging')
                    .EndRecord();
                    
        END_FOR
        
        // This controls how many records are written.  You can either use a timer countdown or a record count.  
        // in this example I check the records created and once I have over 300 (including the header) then I complete.
        IF csvDocumentWriter.RecordCount > targetLogCount THEN
            loggingState := COMPLETE_LOGGING;
        END_IF
        
    COMPLETE_LOGGING: 
        
        // You must stop the file based buffer to close and release the file.  Once the buffer has stopped we can then finish
        // by resetting the csvDocument.  This resets the internal counters.
        fileBasedByteBuffer.Stop();
        
        IF fileBasedByteBuffer.IsStopped THEN
            csvDocumentWriter.Reset();
            loggingState := IDLE;
        END_IF
    
END_CASE
// You must cyclic call this fileBasedByteBuffer to give it procesing time.  There is no requirement to call the csvDocumentWriter as this is 
// all event based. 
fileBasedByteBuffer();
// maxiumumBufferUsage must remain under GVL_ByteBufferConstants.FILE_WRITE_BUFFER_SIZE.  If it exceeds then you will drop values.  In this instance
// make GVL_ByteBufferConstants.FILE_WRITE_BUFFER_SIZE larger. 
IF fileBasedByteBuffer.Size > maxiumumBufferUsage THEN
    maxiumumBufferUsage := fileBasedByteBuffer.Size;
END_IF