08-09-2016 02:48 PM
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
08-10-2016 02:22 AM
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.