pressMac
asked on
XLM import into access not working
I have an existing import routine that import xml. It is importing payroll data. The xml document looks like this:
It has the <?xml version="1.0" ?>
<?pcxml version="2.0" ?>
<PCXML> tags in it, and also no unclosed tags.
The import that does not work is a quicken qfx file. It has many header items and unclosed tags. THis file starts like this.
So, the question is how to make access parse this file??
My module looks like this:
Many Thanks, Press
<?xml version="1.0" ?>
<?pcxml version="2.0" ?>
<PCXML>
<Company>
<ID>55-5555555</ID>
<BusinessName>My Company, Inc.</BusinessName>
</Company>
<AccountAddRq>
<AccountAdd>
<Name>1-4000</Name>
<AccountType>OtherCurrentAsset</AccountType>
<Desc>Asset Account - Employee Deductions</Desc>
</AccountAdd>
</AccountAddRq>
<AccountAddRq>
<AccountAdd>
<Name>7-2250</Name>
<AccountType>Expense</AccountType>
<Desc>Tax Account - 7-2250</Desc>
</AccountAdd>
</AccountAddRq>
<AccountAddRq>
<AccountAdd>
<Name>7-2280</Name>
<AccountType>Expense</AccountType>
<Desc>Tax Account - 7-2280</Desc>
</AccountAdd>
</AccountAddRq>
<AccountAddRq>
<AccountAdd>
It has the <?xml version="1.0" ?>
<?pcxml version="2.0" ?>
<PCXML> tags in it, and also no unclosed tags.
The import that does not work is a quicken qfx file. It has many header items and unclosed tags. THis file starts like this.
OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE
<OFX>
<SIGNONMSGSRSV1>
<SONRS>
<STATUS>
<CODE>0
<SEVERITY>INFO
<MESSAGE>Success
</STATUS>
<DTSERVER>20131101123601.000
<LANGUAGE>ENG
<FI>
<ORG>CITIGROUP
<FID>999999
</FI>
<INTU.BID>999999<INTU.USERID>9999999999
</SONRS>
</SIGNONMSGSRSV1>
<CREDITCARDMSGSRSV1>
<CCSTMTTRNRS>
<TRNUID>0
<STATUS>
<CODE>0
<SEVERITY>INFO
<MESSAGE>Success
</STATUS>
<CCSTMTRS>
<CURDEF>USD
<CCACCTFROM>
<ACCTID>XXXXXXXXXXXX9999
</CCACCTFROM>
<BANKTRANLIST>
<DTSTART>20131003180000.000
<DTEND>20131030180000.000
<STMTTRN>
<TRNTYPE>DEBIT
<DTPOSTED>20131003180000.000
<TRNAMT>-3.02
<FITID>0483132760001
<SIC>8699
<NAME>WWW.LINKEDIN.COM MOUNTAIN
</STMTTRN>
<STMTTRN>
<TRNTYPE>DEBIT
<DTPOSTED>20131003180000.000
<TRNAMT>-14.24
<FITID>99999999999992
<SIC>7399
So, the question is how to make access parse this file??
My module looks like this:
Function fImportCITI(strXML As String)
' to import citibank credit card statments from stmt.qfx file (quicken format)
On Error GoTo PROC_ERR
Dim xmlDOM As DOMDocument
Dim xmlNodeList As IXMLDOMNodeList
Dim xmlNodeItem As IXMLDOMNode
Dim xmlNodeField As IXMLDOMNode
Dim xmlNodeSubItem As IXMLDOMNode
Dim cnn As New ADODB.Connection
Dim rstTmpCC As New ADODB.Recordset
'Dim rstTmpCCItems As New ADODB.Recordset
Dim sql As String
'Dim sqlCCItems As String
Dim PayableID As Long
Dim netCheck As Currency
Set cnn = CurrentProject.Connection
sql = "Select * from tmpCCImportsStagingData"
'sqlPL = "Select * from [tmpCCImportsStagingDataBU]"
Debug.Print Dir(strXML)
Set xmlDOM = New DOMDocument
If xmlDOM.Load(strXML) Then
Debug.Print "doc loaded"
Else
Debug.Print "nope"
End If
' Set xmlNodeItem = xmlDOM.documentElement.childNodes.Item(0)
' Debug.Print "xmlNodeItem.childNodes.length-" & xmlNodeItem.childNodes.Length
' Debug.Print "xmlNodeItem.childNodes(0).Text-" & xmlNodeItem.childNodes(0).Text
' Debug.Print "xmlNodeItem.childNodes(1).Text-" & xmlNodeItem.childNodes(1).Text
If Err.Number = 91 Then
Debug.Print "error 91"
Else
Debug.Print "no error 91"
End If
Set xmlNodeList = xmlDOM.getElementsByTagName("STMTTRN")
Debug.Print "Setup check recordset"
Many Thanks, Press
Can you have Quicken to generate a different file format, i.e. CSV?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Not really the solution i hoped for. But i wont spend more time trying to read file natively. So it is a solution of sorts.
Press
Press
Press,
I am coding a solution for importing OFX and QFX files this into Access. I will let you know when I get it done.
I am coding a solution for importing OFX and QFX files this into Access. I will let you know when I get it done.