Skip to Content

Register Login

Industry Online Support

Technical Forum

Navigation

Exporting the database to microsoft Excel

Created by: Shamsher at: 11/10/2005 5:38 PM (35 Replies)

Rating (1)

Thanks 1

Result pages:: |; 1 | 2 | 3 | 4; |

36 Entries

Entries per page: 10 | 20 | all

3/1/2007 1:53 PM	Rate (0)
jul's Posts: 58 Rating: (1)	Salma Hello Julian It is very simple to do this using a VBS script file. This can be triggered from either WinCC or Windows Scheduler. I will post an example next week if that is ok with you (I am out of the country at the moment and do not have access to my computer). Salma Ok thank you very much Salma !! I am waiting for your example for the next week Thanks !! Julien
jul's Posts: 58 Rating: (1)
Suggestion To thank Quote Answer

3/5/2007 11:45 AM	Rate (0)
Salma Gold Member Joined: 9/27/2005 Last visit: 11/27/2007 Posts: 1398 Rating: (151)	Hi Julien Below is an example script for your use. Please note that I have not tested this code so it may contain some typing errors. The script will export the previous 24 hours of alarm data and write it to a file in C:\ root directory. It dynamically calculates the filename based on the current date. It also exports all alarm columns using a loop but you may want to change this as a lot of the columns are probably empty. [code] dim obj_cn, obj_rs, sql_query dim filename, obj_fso, obj_stream dim col_number, record_count Set obj_cn = CreateObject("ADODB.Connection") obj_cn.ConnectionString = "Provider=WinCCOLEDBProvider.1;Catalog=CC_ExternalBrowsing;Data Source=.\WinCC" obj_cn.CursorLocation = 3 'adUseClient obj_cn.Open starttime = CStr(Format(DateAdd("d", -1, Now), "yyyy-mm-dd h:n:s")) endtime = CStr(Format(Now, "yyyy-mm-dd h:n:s")) sql_query = "ALARMVIEW:SELECT * FROM ALGVIEWENU WHERE DateTime >= '" & starttime & "' AND DateTime <= '" & endtime & "'" Set obj_rs = CreateObject("ADODB.Recordset") obj_rs.CursorLocation = 3 'adUseClient obj_rs.Open sql_query, obj_con, 2 record_count = obj_rs.Fields.Count filename = "C:\alarm_archive_" & Right("000" & Year(DateAdd("d", -1, Now)),2) & "_" & Right("000" & Month(DateAdd("d", -1, Now)),2) & "_" & Right("000" & Day(DateAdd("d", -1, Now)),2) & ".csv" Set obj_fso = CreateObject("Scripting.FileSystemObject") Set obj_stream = obj_fso.CreateTextFile(filename, True) If(record_count>0) Then obj_rs.movefirst While Not obj_rs.Eof For col_number = 0 To obj_rs.Fields.Count - 1 If col_number = 0 Then obj_stream.Write obj_rs.Fields(col_number) Else obj_stream.Write "," & obj_rs.Fields(col_number) End If Next col_number obj_rs.movenext Wend obj_rs.Close End If Set obj_rs = Nothing Set obj_con = Nothing set obj_fso = Nothing [/code] I hope it helps. Salma

Suggestion To thank Quote Answer

3/6/2007 5:13 AM	Rate (0)
Dec Gold Expert Joined: 5/19/2006 Last visit: 12/3/2024 Posts: 549 Rating: (107)	Glad to see you back Salma Dec

Suggestion To thank Quote Answer

3/7/2007 12:16 PM	Rate (0)
Salma Gold Member Joined: 9/27/2005 Last visit: 11/27/2007 Posts: 1398 Rating: (151)	Thanks Dec. I try to log in when I can but the new job means I don't have much time Salma

Suggestion To thank Quote Answer

3/7/2007 2:38 PM	Rate (0)
Ikkezelf Posts: 1084 Rating: (72)	hey Salma, There are plenty of experts and users with great expertise that contribute to this forum. So, don't worry when you don't have the time at the moment. with regards, Ikkezelf
Ikkezelf Posts: 1084 Rating: (72)
Suggestion To thank Quote Answer

3/7/2007 3:47 PM	Rate (0)
Danielle Posts: 1275 Rating: (123)	Yup... of course your knowledge is overwhelming and will be more than missed, but your new job must be taking a lot of time and energy, and it is your priorityright now.If you do have the time, though, we're always veryhappy to have you here!
Danielle Posts: 1275 Rating: (123)
Suggestion To thank Quote Answer

5/14/2007 10:58 AM	Rate (0)
jul's Posts: 58 Rating: (1)	Hi everybody !! I have tryed the Salma's VBscipt which is in this forum, but it'doesn't work. First, in line 9, the "Format" instruction isn't recocgnized. In line 17 the syntaxe isn't good. In line 34, the instruction isn't recocgnized I have corrected this mistake, ( but i'm not sure ...), and when the file is exported, it is empty.there is nothing in.. absolutely nothing... Anybody can help me to correct this script ? Thanks Julien
jul's Posts: 58 Rating: (1)
Suggestion To thank Quote Answer

5/14/2007 12:50 PM	Rate (0)
Akorostel Posts: 20 Rating: (2)	There is a way to export archived tag values from WinCC database by using ODK functions. Here is an example (the code is split into two parts so that it has been displayed properly): [code] #include "apdefap.h" int ExportArchiveToCSV2(SYSTEMTIME stStart, SYSTEMTIME stStop, char * ArchiveName, char * VarName, char * FileName) { /* Function exports the values of tag VarName from Tag Logging archive ArchiveName to text file FileName. Data are exported for the period from stStart to stStop. Function returns integer value representing the result of export: 0 - Export to file was successfull 2 - No data were received 4 - Can't get data from archive 5 - Can't open text file / #pragma code("pdertcli.dll") #include "pde_glob.h" #include "pdertcli.h" #pragma code() BOOL retcode, FirstPass = TRUE; CMN_ERROR error; int nIndex; PTLG_ARCHIVDATARAW pDat; PTLG_ARCHIVDATARAW g_pTlgData = NULL; DWORD g_TLGdwNumberOfData =10000, g_TLGdwFlags = 0; FILE LogFile; // Open file FileName for write LogFile = fopen(FileName, "w"); if (LogFile == NULL) { // If file was not open report an error and return printf("ExportArchiveToCSV: Can't open file %s\r\n", FileName); return 5; } printf("ExportArchiveToCSV: file succesfully created:%s\r\n", FileName); //The cycle that calls ODK function TLGGetArchivDataEx. The cycle is needed because //this function returns no more than 10000 values. If there are more values in the requested //period, we need to call this function several times. do { //Adjust maximum number of returned data records before the call g_TLGdwNumberOfData=10000; g_pTlgData = NULL; // Get fata from archive retcode = TLGGetArchivDataEx(ArchiveName, VarName, &stStart, &stStop, &g_pTlgData, &g_TLGdwNumberOfData, &g_TLGdwFlags, &error); // In case of error while getting the data if ((retcode != TRUE)\|\|(g_pTlgData == NULL)) { //Output diagnostic message printf("ExportArchiveToCSV: Error in TLGGetArchivDataEx. %s\r\n",error.szErrorText); //Free memory if (g_pTlgData != NULL) { retcode = TLGFreeMemory(g_pTlgData); if (retcode != TRUE) printf("ExportArchiveToCSV: Error in TLGFreeMemory. %s\r\n",error.szErrorText); } //Close text file fclose(LogFile); return 4; } [/code] [code] // If there were no data received if (g_TLGdwNumberOfData <= 0 ) { //Output diagnostic message printf("ExportArchiveToCSV: TLGGetArchivDataEx returned nothing.\r\n"); //Free memory if (g_pTlgData != NULL) { retcode = TLGFreeMemory(g_pTlgData); if (retcode != TRUE) printf("ExportArchiveToCSV: Error in TLGFreeMemory. %s\r\n",error.szErrorText); } if (FirstPass == FALSE) // No more points available, but we have already received some data. Everything //is ok. Exit the cycle break; else { //If it was the first call to TLGGetArchivDataEx and no data received, than report error code 2 (No data available) fclose(LogFile); return 2; } } //Initialize pointer to move through received data pDat = g_pTlgData; printf("ExportArchiveToCSV: TLGGetArchivData: Returned %d points.\r\n", g_TLGdwNumberOfData); //Move through all received data values, and output them to text file, line by line for (nIndex=0; nIndex<(int)g_TLGdwNumberOfData; nIndex++ ) { fprintf(LogFile, "%04d.%02d.%02d %02d.%02d.%02d,%7.3f,%d\r\n", pDat->stTime.wYear, pDat->stTime.wMonth, pDat->stTime.wDay, pDat->stTime.wHour, pDat->stTime.wMinute, pDat->stTime.wSecond, pDat->doValue, pDat->dwFlags); pDat++; } //If function returned 10000 points, than probably more data available. //So adjust stStart to time of last received data point and repeat the procedure. if (g_TLGdwNumberOfData >= 10000) { PrintToLogFile("ExportArchiveToCSV: More than 10000 points received, going to repeat the call\r\n"); FirstPass = FALSE; stStart = (pDat - 1)->stTime; printf("next step\r\n"); } // Free memory retcode = TLGFreeMemory(g_pTlgData); if (retcode != TRUE) printf("ExportArchiveToCSV: Error in TLGFreeMemory. %s\r\n",error.szErrorText); } while (g_TLGdwNumberOfData >= 10000); //Close file fclose(LogFile); return 0; /* Calling example: SYSTEMTIME stStart, stStop; char ArchiveName[255], VarName[255], FileName[255]; stStart.wYear=2007; stStart.wMonth=3; stStart.wDay=24; stStart.wHour=0; stStart.wMinute=0; stStart.wSecond=0; stStart.wMilliseconds=0; stStop.wYear=2007; stStop.wMonth=3; stStop.wDay=24; stStop.wHour=9; stStop.wMinute=0; stStop.wSecond=0; stStop.wMilliseconds=0; strcpy(ArchiveName, "ProcessValueArchive"); strcpy(VarName, "CS/Adsorbers/P1_A_103_2.Flt"); strcpy(FileName, "C:\\test.txt"); printf("Returned %d\r\n", ExportArchiveToCSV2 (stStart, stStop, ArchiveName, VarName, FileName)); */ } [/code]
Akorostel Posts: 20 Rating: (2)
Suggestion To thank Quote Answer

5/15/2007 9:30 AM	Rate (0)
jul's Posts: 58 Rating: (1)	Hi !! Is there another way ? I don't have the ODK command. It isn't possible with simple VB command ? I'm sure that the Salma's script can work very properly if anybody help me to correct it ! I have write where I think there is mistake... Now i still search how to correct, but if anybody have some ideas... Thanks very much. Julien.
jul's Posts: 58 Rating: (1)
Suggestion To thank Quote Answer

5/15/2007 1:41 PM	Rate (0)
Ikkezelf Posts: 1084 Rating: (72)	Hi, Are you sure there is data in the view? You are trying to show data from theis view: ALGVIEWENU between now and yesterday. Open the view in the enterprise manager and see if it returns any rows. Maybe it's empty. BTW: you don't need to have the odk to use the offered script. with regards, Ikkezelf
Ikkezelf Posts: 1084 Rating: (72)
Suggestion To thank Quote Answer

Result pages:: |; 1 | 2 | 3 | 4; |

36 Entries

Entries per page: 10 | 20 | all

|

Share this page:

Share this page on...

Follow us on