VBA macro to ignition dataset conversion

Ignition
1

Hi, I am currently trying to get a old VB macro converted to ignition code. Does anyone have any good suggestions?

What it supposed to do:

  1. Take a txt file and import the file into excel using vba. (Code is below)

What i would like to do:

  1. Import it via a script around 3 am.
  2. I would probably import it to ignition tags on the gateway so that it can be used plantwide

Notes:
I would like to use the csv or any other plugin thats compatable with jython . The text file is also double spaced for its delimiter

    With Sheet3.QueryTables.Add(Connection:="TEXT;" & SDriveDirectory & "rodfile_m0008.txt", Destination:=Sheet3.[A1])
        
'        .Name = "OB"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
        
    End With
2

You can easily read the contents of a file in Python in Ignition:
https://docs.inductiveautomation.com/display/DOC81/system.file.readFileAsString

contents = system.file.readFileAsString('c:/file/path/name.txt')
lines = contents.splitlines()
## completely untested

Just note that running this in Perspective will execute it on the Gateway, not the client. You cannot run this on a client. (Irrelevant for v7.9, thanks @Kevin.Herron )

3

This is tagged ignition79, might not be an issue here.

4

You never know… haha

5

Thanks for the replies. So the good thing is that i don’t need to run it on a client. It will be strictly gateway. I will pull the text files from a network drive then sort it out with a script from a script. Looking at nminchins code. It looks like its looking like the way I want. I’m assuming that there would be a way to convert the string to a dataset then?

Thanks!

6

Yep. For that, instead of using that code, have a look at examples of using the csv python library (just google).
You’ll need the headers as a list and the data for the headers for each row as a list of lists. Then you can use system.dataset.toDataSet to smoosh it all together into a dataset

7

Excellent Thanks! I appreciate your help