CSV import data to channel in DIAdem

[Mike117]

Hi,

Due to limitations of VBScript language, after much digging, I figured out how to write some channel data to what I will call an 'Excel CSV'. This contains CSV lists as a 'row' and newlines at the end to signify the next row. So it has rows and columns, but does not adhere to the official CSV rfc 4180 so I call it Excel CSV since this is how Excel exports a spreadsheet when saved as CSV.

1,2,3,4\n

1,2,3,4\n

etc

Achieved like so:

Dim oMyChannelList
Set oMyChannelList = Data.GetChannels("*regex-like*_otherID/someChannel_*")
'Call oMyChannelList.Add(Data.Root.ChannelGroups("SomeIDentifier") ' for all channels in group
Call DataFileSaveSel("<filename>" & RootPathName & tmpDir & outputFile & "</filename><delimiter>,</delimiter>", "CSV", oMyChannelList)
' https://forums.ni.com/t5/DIAdem/comma-as-decimal-separator/m-p/726321?query.id=146775#M8851
' ^^ This fails if tmp folder not created.. so we use names #VBSprobs

Which exports to CSV (with commas as delimiters).

The importing into channels has been a major headache, however.

I would like to create channels within a pre-existing channel group. After setting the channelgroup as oGroupChns = Data.Root.ChannelGroups("thenameofchannelgroup").Channels

 I have tried to import from CSV in the same format it was exported ('Excel CSV') using all of the following, separately, at one point:

'Call DataFileLoadSel(RootPathName & "\test\tmp\channels_out.csv", "CSV", ImportSelection, [ImportAction])
' ^^ test row to serve as a template with fields filled in
Call DataFileLoadSel(RootPathName & "\test\tmp\channels_out.csv", "CSV", oGroupChns & "/channelNum*", "Load")
 Call DataFileLoadSel(RootPathName & "\test\tmp\channels_out.csv", "CSV", "[12]/channelNum1", "Load")
Call DataFileLoadSel(RootPathName & "\test\tmp\channels_out.csv", "CSV", "*", "Load")
 Call DataFileLoad(RootPathName & "\test\tmp\stuff.csv", "CSV", "Load")
' ^^ A simple one line test CSV, works but creates its own group, and is test data not real data

It throws an error for an exception, but leaves no clues as to what the exception is for! Not even a name. I cannot seem to reproduce this today, it just doesn't import the data into the created channels: channelNum1 through 9. There are 9 columns in the csv to represent the 9 channels.

I poured over the forums and found some code similar to this:

Dim ch0: set ch0 = DataFileLoadSel(RootPathName & "\test\tmp\test.csv", "CSV", oGroupChns & "/testNum1", "Load")(1)
ch0.Name = "one"
ch0.properties("description").value = "OVER 9000!!!"
call DataFileSelDlg(RootPathName & "\test\tmp\channels_out.csv", "CSV") 
' ^^  Works! but not automatic enough

Call DataFileLoadSel(RootPathName & "\test\tmp\channels_out.csv", "CSV", "[12]/[354]", "Register")
 Dim oMyElementList
 Set oMyElementList = DataFileLoadSel(RootPathName & "\test\tmp\channels_out.csv", "CSV", , "Load")
call ChnSum(oMyElementList, "ChannelStuff")

The DataFileSelDlg, works exactly how I expect my code to work (except that it creates a new ChannelGroup) so I am confident my CSV is ok, but it requires user intervention which is not desireable. I have tried both Load and Register ImportActions, which both seem to do the same thing in the DataFileSelDlg when pushing appropriate buttons. I don't see very many examples of CSV importing out there, and I have been looking for many hours to accomplish a task which is trivial (< 5 min) in a better documented and supported language like Python. Any help is appreciated.

TL;DR

I want to read my CSV data into an existing channelgroup and none of the above that I've tried has worked, and root causing the source of error has been challenging.

 Edit: I have tried DataFileLoad, similar to above:

Call DataFileLoad(RootPathName & "\test\tmp\stuff.csv", "CSV", "Load")

but with my actual data and it works, but creates its own channelgroup. I can copy/rename these in a very convoluted way, but programatically.  I am seeking a more simple solution to read from CSV and write directly into the channels I have created in my oGroupChns ChannelGroup object.

Also, here is a useful link for attempts at error resolution in VBScript&colon;

http://zone.ni.com/reference/en-XX/help/370859J-01/vbs/general/vbs_onerror/

But was not useful in finding my unknown exception described earlier, and is nowhere near as specific or nice as Python.

[AndreasK · Accepted solution]

The issue about CSV is that Microsoft defines it as "Character Seperated Values" instead of the "Comma Seperated Values" like defined in the RFC. It also uses \r\n instead of \n.

Why does this matter?

"Excel CSV" like you called it depends on the reagion yu are living in.

• Having dot as decimal point you will normally get comma as seperator
• Having comma as decimal point like germany you get semikolon as seperator and dot as thousand seperator

Because of this behavior exchanging data using CSV becomes an issue in the moment you change platform or locale settings.

Data exchange becomes an real issue if you have to exchange international.

This means the CSV export needs to be configurable on load and write side.

If you think about data exchange please also think about using "TDM" as format because there is an excel addin, installed with DIAdem or downloadable stand alone, which will avoid such exchange issues.

---

Loading:

Normally DataFileLoadSel does not create a group if you select channels. (Exception, data portal is empty)

If you select groups it will create groups.

In your case CSV only has one group so the command will load channels into the active group.

It will never load into predefined channels as long as you do not use the mode append.

This script will create two groups and fill channels of a single csv into the two groups using DataFileLoadSel.

Option Explicit

data.Root.Clear
dim grpO1 : set grpO1 = data.Root.ChannelGroups.Add("target1")
dim grpO2 : set grpO2 = data.Root.ChannelGroups.Add("target2")

dim loadedChannels, loadedChannel 

grpO1.Activate
set loadedChannels = DataFileLoadSel(DataReadPath & "Example.csv","CSV","[1]/[1,3,6]","LoadImmediately")
for each loadedChannel in loadedChannels
  ' do with the loaded channels whatever you want
  loadedChannel.Properties("description").Value = "Loaded first time"
Next

grpO2.Activate
set loadedChannels = DataFileLoadSel(DataReadPath & "Example.csv","CSV","[1]/[2,4,5]","LoadImmediately")
for each loadedChannel in loadedChannels
  ' do with the loaded channels whatever you want
  loadedChannel.Properties("description").Value = "Loaded second time"
Next

resulting in

portal view

what I think is what you wanted.

---

Be aware that there is a read only programming API called DataPlugins which will allow you to organize your data before starting loading it.

---

If you find something that works interactively you can normally use the Macro Recoder to determine how the script wuld look like.

Pressing Ctrl+Shift+C in a dialog will put its settings into clipboard.

[Mike117]

Thank you for the insight into CSV conventions outside of the RFC, another reason why CSV is not ideal, however, it is usually portable between (programming) languages and environments. Language/locales throw more monkey wrenches into it.

I am using CSV instead of TDM for exactly that -- importing/conversions in other programming languages.

I will try playing with append, I tried load and register already. I will also explore DataPlugins, though that seemed like overkill for what I was doing. CSV did not seem that complicated and I thought I could do it with builtin functions.

I forgot about the macro recorder, thanks! I will definitely explore using that a bit more. I'll do some experimenting and mark this as resolved if your suggestions fix my issues. I should have given DIAdem some more restarts too during my testing. Thank you!

[AndreasK]

There is also an C dll to read write tdm/tdms files to be used in other programming environments.

You do not have to use DataPlugins but they make live easy.

In additional they enable the content of your files to be searchable/indexable in NI DataFinder and TDM Server.

[Mike117]

I will definitely check out the C dll for a longer term solution, but your answer helped and I finally finished my csv reading/writing. Thanks!