Reading an OFX file

[jstevens]

Can Excel import an OFX file using Power Query?

From what I have read, an XLM file can be imported and a table generated based on certain fields.

I know how to read the OFX file as a flat file and add to a worksheet various fields/records. I'm interested in a Power Query approach.

[HansV]

Does OFX CONNECTION help?

[p45cal]

Could you attach 1, maybe 2, examples of such ofx files?

[snb]

See attachment:

To be opened in Excel, using VBA:

Code: Select all

Workbooks.OpenXML "G:\OF\voorbeeld.ofx", , 2

[p45cal]

In the attached:
1. A query I put together very quickly called AFirstGo. It's on Sheet1.
2. A query lifted from https://community.fabric.microsoft.com/ ... m-p/355528 (HansV's link) with minor tweaks at the start and finish of the query. It's on Sheet2
3. Both the above queries use the named range filepathAndname which is at cell A1 of Sheet1. This will need adjusting by you to point to the file you want to query.

edit:I just noticed the ofx file sample was snb's, not the OP jsteven's, so I'd still like to see one or two of the OP's ofx files.

[jstevens]

p45cal,

Please find attached a sample OFX file. I'll look at the other suggestions when I have an opportunity.

Sample.OFX.zip

[p45cal]

The Sheet2 query in my last attachment seems to work if you point cell A1 of Sheet1 at the sample file on your system which is not in the MACOSX folder (this file seems to have incorrect contents).

[jstevens]

I was able to identify the file type of the Sample.OFX file. It was generated using a SGML (Standard Generalized Markup Language) format. Excel is unable to open the file with Power Query.

In order to resolve the challenge I converted the SGML file to a XML format with VBA. Power Query can process XML files.

The main difference with SGML and XML is the ending tag associated with each line.

Code: Select all

Example:  <TRNAMT>100.00 in SGML
          <TRNAMT>100.00</TRNAMT> in XML

Thanks to all who contributed.

[snb]

I used this macro to adapt the OFX-file to an XML-readable file:

Code: Select all

Sub M_snb()
  With CreateObject("scripting.filesystemobject")
     sn = Split(.opentextfile("G:\OF\sample.ofx").readall, vbCrLf)

     For j = 0 To UBound(sn)
        If Right(sn(j), 1) <> ">" Then sn(j) = sn(j) & Replace(Trim(Left(sn(j), InStr(sn(j), ">"))), "<", "</")
     Next

     .createtextfile("G:\OF\sample.ofx").write Join(sn, vbCrLf)
  End With
End Sub

[jstevens]

snb,

I modified your code relative to line feeds, ampersands and XML declarations.

Code: Select all

Sub M_snb()
    Dim sourceFile As String
    Dim targetFile As String
    Dim xmlDeclaration As String
    Dim ofxStartTag As String
    Dim ofxEndTag As String
    Dim fileContent As String
    Dim lines() As String
    Dim j As Long
    Dim contentWithTags As String
    
    ' Define the source and target files based on the parameters
    sourceFile = Parameters.Range("C4").Value & Parameters.Range("C3").Value
    targetFile = Parameters.Range("C6").Value

    ' Define the XML declaration and OFX tags
    xmlDeclaration = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf & "<OFX>"
    ofxEndTag = "</OFX>"
    
    ' Create a FileSystemObject
    With CreateObject("scripting.filesystemobject")
        ' Read the entire content of the source file
        fileContent = .opentextfile(sourceFile).readall
        
        ' Split the content by line
        lines = Split(fileContent, vbLf)
        
        ' Iterate through each line to adjust tags and remove "[0:GMT]" and ampersands
        For j = 0 To UBound(lines)
            If Right(lines(j), 1) <> ">" Then
                lines(j) = Replace(Replace(lines(j) & Replace(Trim(Left(lines(j), InStr(lines(j), ">"))), "<", "</"), "[0:GMT]", ""), "&", "&amp;")
            End If
        Next
        
        ' Combine the lines back into a single string with vbLf as the separator
        fileContent = Join(lines, vbLf)
        
        ' Prepend the XML declaration and OFX start tag, and append the OFX end tag
        contentWithTags = xmlDeclaration & vbLf & fileContent & vbCrLf & ofxEndTag
        
        ' Write the modified content to the target file
        .createtextfile(targetFile).write contentWithTags
    End With
End Sub

Thank you for your suggestion.

[snb]

The file you posted started with <OFX> and ended with </OFX)
Nevertheless:

Code: Select all

Sub M_snb()
' reading parameters in only 1 reading action
' no need for variables that contain invariable content
  st = Parameters.Range("C3:C6")
    
  With CreateObject("scripting.filesystemobject")
        ' replacing once instead of in every loop
        ' no need for an intermediate variable
    sn = Split(Replace(Replace(.opentextfile(sn(1, 2) & sn(1, 1)).readall, "[0:GMT]", ""), "&", "&"), vbCrLf)
        
        ' Iterate through each line to add 'missing' endtags
    For j = 0 To UBound(sn)
      If Right(sn(j), 1) <> ">" Then sn(j) = sn(j) & Replace(Trim(Left(sn(j), InStr(sn(j), ">"))), "<", "</")
    Next
        
        ' Write the modified content to the target file
    .createtextfile(sn(1, 4)).write Join(Array("<?xml version=""1.0"" encoding=""UTF-8""?>", "<OFX>", Join(sn, vbCrLf), "</OFX>"), vbCrLf)
  End With
End Sub

[jstevens]

snb,

Thanks for responding.

[jstevens]

sub,

I tried your latest post regarding the code and encounter a compile error: Sub or Function not defined.

It happens at "sn" in this line of code: Split(Replace(Replace(.opentextfile(sn

Your thoughts are appreciated.

[HansV]

I suspect that the line

Code: Select all

    st = Parameters.Range("C3:C6")

should be

Code: Select all

    sn = Parameters.Range("C3:C6")

[snb]

No but referring to st should be adapted.

Code: Select all

Sub M_snb()
' reading parameters in only 1 reading action
' no need for variables that contain invariable content
  st = Parameters.Range("C3:C6")
    
  With CreateObject("scripting.filesystemobject")
        ' replacing once instead of in every loop
        ' no need for an intermediate variable
    sn = Split(Replace(Replace(.opentextfile(st(1, 2) & st(1, 1)).readall, "[0:GMT]", ""), "&", "&"), vbCrLf)
        
        ' Iterate through each line to add 'missing' endtags
    For j = 0 To UBound(sn)
      If Right(sn(j), 1) <> ">" Then sn(j) = sn(j) & Replace(Trim(Left(sn(j), InStr(sn(j), ">"))), "<", "</")
    Next
        
        ' Write the modified content to the target file
    .createtextfile(st(1, 4)).write Join(Array("<?xml version=""1.0"" encoding=""UTF-8""?>", "<OFX>", Join(sn, vbCrLf), "</OFX>"), vbCrLf)
  End With
End Sub

[jstevens]

snb,

This line errors: Script out of range.

sn = Split(Replace(Replace(.opentextfile(st(1, 2) & st(1, 1)).readall, "[0:GMT]", ""), "&", "&amp;"), vbCrLf)

[snb]

then test:

Code: Select all

Sub M_snb()
  st = Parameters.Range("C3:C6")
  msgbox st(1,2)
  msgbox st(1,1)
  msgbox st(1,4)
End Sub

[jstevens]

snb,

I found the issue: st = Parameters.Range("C3:C6") is a single-dimensional array.

Changing the code resolved the error message.

Code: Select all

Sub M_snb()
Dim st As Variant
  st = Parameters.Range("C3:C6")
  MsgBox st(1, 1)
  MsgBox st(2, 1)
  MsgBox st(3, 1)
End Sub

[snb]

But nevertheless a 2-dimensional one, Ubound(st)=4, ubound(st,2)=1