# VBA macro to ignition dataset conversion

**URL:** <https://forum.inductiveautomation.com/t/vba-macro-to-ignition-dataset-conversion/49089>\
**Category:** Ignition\
**Tags:** ignition79\
**Created:** [July 22, 2021, 2:09pm UTC](https://forum.inductiveautomation.com/t/vba-macro-to-ignition-dataset-conversion/49089 "2021-07-22T14:09:24Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![steven.hess](https://avatars.discourse-cdn.com/v4/letter/s/2acd7d/32.png) [@steven.hess](https://forum.inductiveautomation.com/u/steven.hess)\
**Post date:** [July 22, 2021, 2:09pm UTC](https://forum.inductiveautomation.com/t/vba-macro-to-ignition-dataset-conversion/49089/1 "2021-07-22T14:09:24Z")

</div>

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

```auto
    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

```

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [July 22, 2021, 2:37pm UTC](https://forum.inductiveautomation.com/t/vba-macro-to-ignition-dataset-conversion/49089/2 "2021-07-22T14:37:31Z")

</div>

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

```auto
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 )

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [July 22, 2021, 2:38pm UTC](https://forum.inductiveautomation.com/t/vba-macro-to-ignition-dataset-conversion/49089/3 "2021-07-22T14:38:37Z")

</div>

> [@nminchin](#):
>
> Just note that running this in Perspective will execute it on the Gateway, not the client. You cannot run this on a client.

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

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [July 22, 2021, 2:38pm UTC](https://forum.inductiveautomation.com/t/vba-macro-to-ignition-dataset-conversion/49089/4 "2021-07-22T14:38:58Z")

</div>

You never know… haha

---

<div class="post-metadata">

**Author:** ![steven.hess](https://avatars.discourse-cdn.com/v4/letter/s/2acd7d/32.png) [@steven.hess](https://forum.inductiveautomation.com/u/steven.hess)\
**Post date:** [July 22, 2021, 7:34pm UTC](https://forum.inductiveautomation.com/t/vba-macro-to-ignition-dataset-conversion/49089/5 "2021-07-22T19:34:27Z")

</div>

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!

---

<div class="post-metadata">

**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\
**Post date:** [July 22, 2021, 9:17pm UTC](https://forum.inductiveautomation.com/t/vba-macro-to-ignition-dataset-conversion/49089/6 "2021-07-22T21:17:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![steven.hess](https://avatars.discourse-cdn.com/v4/letter/s/2acd7d/32.png) [@steven.hess](https://forum.inductiveautomation.com/u/steven.hess)\
**Post date:** [July 23, 2021, 2:31pm UTC](https://forum.inductiveautomation.com/t/vba-macro-to-ignition-dataset-conversion/49089/7 "2021-07-23T14:31:19Z")

</div>

Excellent Thanks! I appreciate your help