Showing results 1 to 5 of 5

Thread: import data from csv to sql

  1. #1
    Join Date
    01.10.2015
    Posts
    6

    Default import data from csv to sql

    Hello,

    Some time ago, we had a zenon supervisor version 7.20 running and archiving data. The data was stored as csv files.
    Later, we changed to sql evacuation.
    Now, we are running 7.60 and the customer ask us to insert thee data that is in those csv.
    the data in the csv is like this: 

    01/01/2016;0:46:00.0;B0002V.Health;0.0;SPONT 

    The new project is new, has his own new variables that correspond to the old ones (same IED, but diferent variable name). So i created in the new project an archive structure similar to the old one, with evacuation to sql. then, a c# program that, with the variables table from both projects, generates a csv file in the Zenon format. This file has lines like this:

    62;0;1451606400;0;413712270;4325376;0;

    Then i bulk insert the file in the sql table (10.000.000 records).
    I did this twice. In the first one, i used the same old project with a new archive for importing, and i could view the data in a report viewer type screen.
    The second one, i created a brand new project witch archive with the same configuration and inserted the data, but i can't see the data in a report viewer screen.
    ¿Why this happens?, ¿Perhaps Zenon needs to create some new data to access the old one?.
    Aditionally, when generating the zenon format record, i use as status the string '4325376'. This has worked for me before, but i do not really knowwhat statuses to use (i saw this in the records created by a normal zenon evacuation, so i used it).
    ¿What is the recommended method for inserting the data from external sources in the historic sql table?, ¿There is a manual that i can read?

    I hope i have explained myself, thank you in advance



  2. #2
    Join Date
    28.02.2013
    Posts
    133

    Default Re: import data from csv to sql

    Hello Redjon,

    a zenon archive that is evacuated to a SQL DB basically needs two tables.

    The first one, [PROJECTNAME]_VARIABLES, containing the variable names and IDs:

        [VARIABLE] [int] NOT NULL,
        [NAME] [varchar](128) NULL,
        [GUID] [varchar](36) NOT NULL

    The second one, [PROJECTNAME]_[ARCHIVESHORTNAME], containing the datapoint themself:

        [VARIABLE] [int] NOT NULL,
        [CALCULATION] [int] NOT NULL,
        [TIMESTAMP_S] [int] NOT NULL,
        [TIMESTAMP_MS] [int] NOT NULL,
        [VALUE] [float] NULL,
        [STATUS] [int] NULL,
        [GUID] [varchar](36) NOT NULL,
        [STRVALUE] [varchar](1) NULL,

    So first you need to make sure to update the variable IDs in the data table from old to new IDS.

    (All the table definitons can also be found in the documentation in the chapter historian-> additional information)

    Every Variable in zenon is saved with 64 status bits. So the value is the decimal repressentationof those 64 bit (4325376 -> 00000000000000000000000000000000000000000100001000 00000000000000), which means stuts bit 17 (SPONT) and bit 22 (T_INTERN) are set at this record.

    Best regards,
    Felix

  3. #3
    Join Date
    01.10.2015
    Posts
    6

    Default Re: import data from csv to sql

    Now i Know the status bits, thank you.

    But i have this problem:
    Here you can see two archive tables:


    And the first variable selected in the variables table:


    I generated a reportviewer screen in the same project. if i select the second archive (C0, the one in the right), i get data.
    If i change the SS function and select the first archive (QT, the one in the left), i get th "NO DATA TO SHOW" message.1

    Some diferences: in each case, the source is a csv file, but there are some diferences in insertion.
    - C0 wa inserted by reading the file and making an insert per line, ignoring strvalue in the sql sentence, so it generated a null value. Very slow.
    - QT was inserted by using the BULK insert function. it didn't generate the null in the table, generated a blank. 
    I tried to show a report with QT dat, no result. 
    I noticed the NULL in strvalue difference, so i updated the table to set strvalue to null. No data show either

    ¿Why is this happening?, ¿How can i fix it?


    Attached Thumbnails Attached Thumbnails SQLMS_Screen.jpg   SQLMS_Screen_B.jpg  

  4. #4
    Join Date
    01.10.2015
    Posts
    6

    Beitrag Re: import data from csv to sql

    Ok, i solved it,

    I didn't notice i made some change in the csv creation and the GUID field had the value "0" instead of an empty string. i changed it and now works,

    Thanks for your interest, i wont forget this again Lächeln

  5. #5
    Join Date
    28.02.2013
    Posts
    133

    Default Re: import data from csv to sql

    Great! Thanks for sharing your solution!

    Best regards,
    Felix

Similar Threads

  1. Data import from other database system
    By nicolasr75 in forum VSTA
    Replies: 10
    Last Post: 28th February 2019, 07:37
  2. Import DATA mit IEC61850
    By tsit in forum zenon Energy Edition
    Replies: 6
    Last Post: 12th February 2016, 15:26
  3. Import DATA mit IEC61850
    By tsit in forum Drivers
    Replies: 6
    Last Post: 12th February 2016, 15:26
  4. Import Tagname from .csv file
    By jaroslav.kral in forum VBA
    Replies: 2
    Last Post: 1st July 2011, 13:18
  5. Automatic import of report data into Excel
    By jegloco in forum zenon Supervisor
    Replies: 1
    Last Post: 11th January 2011, 16:33

Tags for this Thread

Posting Rules

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •