1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
|
Imports System.Xml
Imports System.IO
Imports System.Text
Public Class OfxParserToXML
Implements IDisposable
'These are the columns inside the DataSet:
'TRNTYPE - Type of transaction DEBIT Or CREDIT
'DTPOSTED - Date of the transaction, formatted YYYYMMDDHHMMSS
'TRNAMT - Amount (negative when it Is a DEBIT)
'FITID - Transaction ID CHECKNUM - Number of the check Or transaction ID
'MEMO - Small description of the transaction; very useful when you use your debit card
Private strOfxFileName As String
Private xdocXmlData As XmlDocument
' Keep track of when the object is disposed.
Protected disposed As Boolean = False
Public Const strOFX As String = "OFX" 'OFX
Public Const strSIGNONMSGSRSV1 As String = "SIGNONMSGSRSV1" 'SERVER INFOS
Public Const strSONRS As String = "SONRS" '???
Public Const strSTATUS As String = "STATUS" 'Status
Public Const strCODE As String = "CODE" 'Code
Public Const strSEVERITY As String = "SEVERITY" 'Severity
Public Const strMESSAGE As String = "MESSAGE" 'Message
Public Const strDTSERVER As String = "DTSERVER" 'Export date server
Public Const strLANGUAGE As String = "LANGUAGE" 'Language
Public Const strBANKMSGSRSV1 As String = "BANKMSGSRSV1" 'Bank Account SVR
Public Const strSTMTTRNRS As String = "STMTTRNRS" 'Account General infos
Public Const strTRNUID As String = "TRNUID" 'ID bank id
Public Const strSTMTRS As String = "STMTRS" 'Account Infos with transactions
Public Const strCURDEF As String = "CURDEF" 'Currency
Public Const strBANKACCTFROM As String = "BANKACCTFROM" 'Account Infos
Public Const strBANKID As String = "BANKID" 'Bank ID
Public Const strACCTID As String = "ACCTID" 'Account ID
Public Const strACCTTYPE As String = "ACCTTYPE" 'Account type
Public Const strBANKTRANLIST As String = "BANKTRANLIST" 'Bank Transactions list
Public Const strDTSTART As String = "DTSTART" 'Transaction list starting date
Public Const strDTEND As String = "DTEND" 'Transaction list ending date
Public Const strSTMTTRN As String = "STMTTRN" '
Public Const strTRNTYPE As String = "TRNTYPE" 'Transaction type
Public Const strDTPOSTED As String = "DTPOSTED" 'Posting date
Public Const strTRNAMT As String = "TRNAMT" 'Transaction amount
Public Const strFITID As String = "FITID" 'transaction id
Public Const strNAME As String = "NAME" 'Desc 1
Public Const strMEMO As String = "MEMO" 'Desc 2
Public Const strCHECKNUM As String = "CHECKNUM" 'Check number
Public Const strLEDGERBAL As String = "LEDGERBAL" 'Ledger balance
Public Const strBALAMT As String = "BALAMT" 'Balance amount
Public Const strDTASOF As String = "DTASOF" 'Date of balance
Public Const strAVAILBAL As String = "AVAILBAL" 'Available balance
Public Const strCREDITCARDMSGSRSV1 As String = "CREDITCARDMSGSRSV1" 'Credit card server infos
Public Const strCCSTMTTRNRS As String = "CCSTMTTRNRS" 'Credit card General infos
Public Const strCCSTMTRS As String = "CCSTMTRS" 'Credit card Infos with transactions
Public Const strCCACCTFROM As String = "CCACCTFROM" 'Credit card number
#Region "Properties"
Public Property ofxFileName() As String
Get
Return strOfxFileName
End Get
Set(value As String)
strOfxFileName = value
Try
fileToXML()
Catch ex As Exception
Throw ex
End Try
End Set
End Property
Public ReadOnly Property xmlData As XmlDocument
Get
Return xdocXmlData
End Get
End Property
#End Region
#Region "Contructors"
Public Sub New()
Dim ofd As New System.Windows.Forms.OpenFileDialog()
With ofd
.Filter = "Open Financial exchange file (*.ofx)|*.ofx|Quicken file (*.qfx)|*.qfx|Makisoft Personnal file (*.afx)|*.afx"
.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
.Multiselect = False
.ShowDialog()
If (.FileName <> "") Then
strOfxFileName = .FileName
Else
Throw New Exception("No file specified.")
End If
End With
Try
fileToXML()
Catch ex As Exception
Throw ex
End Try
End Sub
#End Region
#Region " IDisposable Support "
' This method disposes the base object's resources.
Protected Overridable Sub Dispose(ByVal disposing As Boolean)
If Not Me.disposed Then
If disposing Then
' Insert code to free managed resources.
End If
' Insert code to free unmanaged resources.
xdocXmlData = Nothing
End If
Me.disposed = True
End Sub
' Do not change or add Overridable to these methods.
' Put cleanup code in Dispose(ByVal disposing As Boolean).
Public Sub Dispose() Implements IDisposable.Dispose
Dispose(True)
GC.SuppressFinalize(Me)
End Sub
Protected Overrides Sub Finalize()
Dispose(False)
MyBase.Finalize()
End Sub
#End Region
Private Sub fileToXML()
If (strOfxFileName <> "") Then
Dim objReader As New System.IO.StreamReader(strOfxFileName)
Dim allFile As String
Dim newAllFile As String
Dim spacesToDelete As Boolean
Dim curPosition As Integer
Dim nodeName As String
Dim nodeValue As String
Dim aNode As XmlNode
Dim aSubNode As XmlNode
Dim anAttribute As XmlAttribute
allFile = objReader.ReadToEnd()
'allFile = Replace(allFile, " ", "")
'Remove all cariage return feed line.
allFile = allFile.Replace(vbCrLf, "")
spacesToDelete = False
newAllFile = ""
For Each caractere As Char In allFile
Select Case caractere
Case ">"
spacesToDelete = True
newAllFile = newAllFile & caractere
Case "<"
spacesToDelete = False
newAllFile = newAllFile & caractere
Case Else
If (spacesToDelete) Then
If (caractere <> " ") Then
spacesToDelete = False
newAllFile = newAllFile & caractere
End If
Else
newAllFile = newAllFile & caractere
End If
End Select
Next
curPosition = newAllFile.IndexOf("<")
'MsgBox(newAllFile.Substring(curPosition, 14))
xdocXmlData = New XmlDocument()
'Add XML declaration
xdocXmlData.AppendChild(xdocXmlData.CreateXmlDeclaration("1.0", "UTF-8", Nothing))
nodeName = getNodeName(newAllFile, curPosition)
aNode = xdocXmlData.AppendChild(xdocXmlData.CreateElement(nodeName))
curPosition = getStartNextNode(newAllFile, curPosition)
While getStartNextNode(newAllFile, curPosition) <> -1
nodeValue = Trim(getNodeValue(newAllFile, curPosition))
nodeName = getNodeName(newAllFile, curPosition)
'Debug.Print("Node name : " & nodeName & ", Node value : " & nodeValue & ", Cursor position : " & curPosition)
If (nodeValue <> Nothing) Then
If ((nodeName = "MESSAGE") Or
(nodeName = "TRNTYPE") Or
(nodeName = "DTPOSTED") Or
(nodeName = "TRNAMT") Or
(nodeName = "FITID") Or
(nodeName = "NAME") Or
(nodeName = "MEMO")) Then
aSubNode = aNode.AppendChild(xdocXmlData.CreateElement(nodeName))
aSubNode.AppendChild(xdocXmlData.CreateTextNode(nodeValue))
anAttribute = xdocXmlData.CreateAttribute("columnType")
aSubNode.Attributes.Append(anAttribute)
Select Case nodeName
Case "MESSAGE", "TRNTYPE", "FITID", "NAME", "MEMO"
anAttribute.Value = "String"
Case "DTPOSTED"
anAttribute.Value = "Date"
Case "TRNAMT"
anAttribute.Value = "Double"
End Select
Else
anAttribute = xdocXmlData.CreateAttribute(nodeName)
anAttribute.Value = nodeValue
aNode.Attributes.Append(anAttribute)
End If
ElseIf (Left(nodeName, 1) = "/") Then
aNode = aNode.ParentNode
ElseIf (nodeName = "\OFX") Then
MsgBox("Attention!!!")
Else
aNode = aNode.AppendChild(xdocXmlData.CreateElement(nodeName))
End If
'If (curPosition >= 4660) Then
' MsgBox("Oups!")
'End If
'Debug.Print(curPosition)
curPosition = getStartNextNode(newAllFile, curPosition)
End While
Else
Throw New Exception("No file specified.")
End If
End Sub
Private Function getNodeName(ByVal ofxFile As String, ByVal startNodePos As Integer) As String
If (ofxFile = "") Then
Throw New Exception("Ofx file's empty.")
ElseIf (ofxFile.IndexOf(">", startNodePos) = -1) Then
Throw New Exception("Openned bracket not closed.")
Else
startNodePos = startNodePos + 1
Return ofxFile.Substring(startNodePos, ofxFile.IndexOf(">", startNodePos) - startNodePos)
End If
End Function
Private Function getStartNextNode(ByVal ofxFile As String, ByVal startNodePos As Integer) As Integer
If (ofxFile = "") Then
Throw New Exception("Ofx file's empty.")
ElseIf (ofxFile.IndexOf(">", startNodePos) = -1) Then
Throw New Exception("Openned bracket not closed.")
Else
Return ofxFile.IndexOf("<", startNodePos + 1)
End If
End Function
Private Function getNodeValue(ByVal ofxfile As String, ByVal startNodePos As Integer) As String
Dim startPos As Integer
Dim endPos As Integer
If (ofxfile = "") Then
Throw New Exception("Ofx file's empty.")
ElseIf (ofxfile.IndexOf(">", startNodePos) = -1) Then
Throw New Exception("Openned bracket not closed.")
Else
If (ofxfile.Substring(ofxfile.IndexOf(">", startNodePos) + 1, 1) <> "<") Then
startPos = ofxfile.IndexOf(">", startNodePos + 1) + 1
endPos = ofxfile.IndexOf("<", startPos) - 1
Return ofxfile.Substring(startPos, endPos - startPos + 1)
Else
Return Nothing
End If
End If
End Function
End Class |
Convertir un fichier OFX en XML
De plus, ce type de fichier étant très près du XML, j'ai donc fait une classe pour convertir en XML.
Répondre avec citation
Partager