Excel Data Plug-in Wizard - Data format

[Scott.Longe]

Hi,

I am trying to determine if DIAdem is suitable for my application but I am having some trouble importing data. What is the correct way to import an excel spreashseet such as the one attached so that I can filter and search for particular cases in the Data Finder and add that data to the Data Portal? For example, I would like to be able to search for a particular Serial Number, company, or any number of properites and add that data to the Data Portal. In the File Structure wizard step, sould I set the top row as Group and the remining rows are Channels? I suspect that the format of this spreasheet is not aligned with what the Excel Data Plug-in Wizard expects.

Thank you

[AndreasK]

Your format looks like this.

To make it seachable you have to do some preprocessing.
The Wizard creates a dataplugin but you can also write one on your own.
The following code might be an example to start a solution.

Option Explicit

sub ReadStore(Workbook)

  '''''''''''''''''''''''''''''''''''''''''
  '' Check if its my format
  if not Workbook.Sheets.Exists("data") then RaiseError

  dim sheet : set sheet = Workbook.Sheets("data")
  if "SerialNum" <> sheet.GetCellValue(1,1) or _
    "COMPANY" <> sheet.GetCellValue(1,2) or _
    "LotNumber" <> sheet.GetCellValue(1,3) or _
    "Package" <> sheet.GetCellValue(1,4) or _
    "ProjectName" <> sheet.GetCellValue(1,5) or _
    "EndDate" <> sheet.GetCellValue(1,6) or _
    "StartDate" <> sheet.GetCellValue(1,7) then RaiseError
  ''
  '''''''''''''''''''''''''''''''''''''''''
  
  dim i : For i = 2 to sheet.MaxPosition.Row ' loop over data rows
    '''''''''''''''''''''''''''''''''''''''''
    '' Prepare target group
    dim uniqueGroupName : uniqueGroupName = sheet.GetCellValue(i,1) & _
      "_" & sheet.GetCellValue(i,2) & _
      "_" & sheet.GetCellValue(i,3) & _
      "_" & sheet.GetCellValue(i,4) & _
      "_" & sheet.GetCellValue(i,5)
    
    dim grp
    if root.ChannelGroups.Exists(uniqueGroupName) then
      set grp = root.ChannelGroups(uniqueGroupName)
    else
      set grp = root.ChannelGroups.Add(uniqueGroupName)
      call grp.Properties.Add("SerialNum", sheet.GetCellValue(i,1))
      call grp.Properties.Add("COMPANY", sheet.GetCellValue(i,2))
      call grp.Properties.Add("LotNumber", sheet.GetCellValue(i,3))
      call grp.Properties.Add("Package", sheet.GetCellValue(i,4))
      call grp.Properties.Add("ProjectName", sheet.GetCellValue(i,5))
      call grp.Channels.Add(sheet.GetCellValue(1,6), eTime)
      call grp.Channels.Add(sheet.GetCellValue(1,7), eTime)
      dim k : for k = 8 to sheet.MaxPosition.Column
        call grp.Channels.Add(sheet.GetCellValue(1,k), eR64)
      Next
    end if
    ''
    '''''''''''''''''''''''''''''''''''''''''
    
    dim yPos : yPos = grp.Channels(1).Size + 1
    grp.Channels(1).Values(yPos) = sheet.GetCellValue(i,6)
    grp.Channels(2).Values(yPos) = sheet.GetCellValue(i,7)
    dim j : For j = 8 to sheet.MaxPosition.Column
      grp.Channels(j - 5).Values(yPos) = sheet.GetCellValue(i,j)
    Next
  Next

end sub

I also attach the DataPlugin Uri.