speeding up a file conversion from text file to XML format

speeding up a file conversion from text file to XML format

Post by UGV0ZXIgTm » Tue, 04 Mar 2008 05:26:02


I, Im usning vb.net ( 2005 ) & SQL Server 2005. I have been asked to try
and improve the importing of files into SQL for old legacy system. The file
are submitted to the company by FTP, decrypted. ( sample file below )

I have written a module to convert this file into an XML file which imports
to the SQL very quickly. The problem I have come up with is generating the
XML File, when files are received in with inexcess of 4000 data records (
data recorded marked for with *** for identification only in the sample ).
The client is online waiting for a report, which is not generated till the
file has been imported to the SQL. In testing with a file of 4000 records it
is taking 2 minutes + to generate the xml file and 4 seconds to import it. I
need to find a way to reduce the time it takes to generate the XML style file
or come up with an alternative. The option of upgrading the clients software
to submit XML files is not an option, so the problem remains in-house. The
XML file is generated on the FTP server itself so I cant even blame a slow
network. Any suggestions are most welcome

( below Sample file & source Code 鈥?modified to protect snesitive data )

Sample File

"111111"
"TEST"
"1"
"100"
"839"
"04/03/2008"
"001"
" XXXX@XXXXX.COM "
" XXXX@XXXXX.COM "
"111111 SERVICE Serial: 839 THIS IS A TEST"
"22222233333333 COMPANY NAME BANK REF 000000000010099" ***
"TRAILER RECORD"
"QQQQ"
"QQQQQQQQQ"
"QQQQQQQQQ"
"QQQQQQQQQ"
"QQQQQQQQQ"
"QQQQQQQQQ"
"QQQQQQQQQ"
"QQQQQQQQQ"
"QQQQQQQQQ"
"QQQQQQQQQ"
"QQQQQQQQQ"
"QQQQQQQQQ"
"QQQQQQQQQ"




BUILD XML FILE CODE

Imports System.Xml
Imports System.IO

Module XMLBuilder
Private XML_DOC As New XmlDocument
Public CURRENTFILE As String = ""
Dim ROOT As XmlElement
Public sr As StreamReader
Private iCount As Integer = 0
Private InputLine As String = ""
Public LICENCENUM As String = ""
Public PIN As String = ""
Public SERIAL As String = ""
Public ORIGACC As String = ""
Private PASSTRAILER As Boolean = False
Public LEDGERKEY As String = ""
Public lupBacsID As String = ""
Public lupOrigSC As String = ""
Public lupOrigACNO As String = ""
Public lupOrigACNA As String = ""
Public lupCountry As String = ""



Public Sub OpenFile(ByVal FileName As String)
Dim sr As New StreamReader(FileName)
End Sub


Public Function BuildXMLFile(ByVal FullFileName As String) As Boolean
'Create the Doc
Dim Root As XmlElement
' open the file and get info
CURRENTFILE = FullFileName
Dim sr As New StreamReader(CURRENTFILE)
For iCount = 1 To 10
Select Case iCount
Case 1
LICENCENUM = StripQuotes(sr.ReadLine)
Case 2
PIN = StripQuotes(sr.ReadLine)
Case 3
TRANSCOUNT = StripQuotes(sr.ReadLine)
Case 4
FILEVALUE = StripQuotes(sr.ReadLine)
Case 5
SERIAL = StripQuotes(sr.ReadLine)
Case 6
MOVEDATE = StripQuotes(sr.ReadLine)
Case 7
ORIGACC = StripQuotes(sr.ReadLine)
End Select
Next
sr.Close()

'Get Originating Account Details

Dim GetOrigCommand As New SqlClient.Sql
 
 
 

speeding up a file conversion from text file to XML format

Post by Cor Ligthe » Tue, 04 Mar 2008 14:34:27

Peter,

This creates an XML file from a database server

\\\
dim ds as new dataset
cim conn as new connection(connectionstring)
dim da as new sqldataadapter(conn)
da.fill(ds)
ds.write(ThePathOnDisk)
///
Cor

 
 
 

speeding up a file conversion from text file to XML format

Post by UGV0ZXIgTm » Tue, 04 Mar 2008 17:29:01

Thanks Steve,
I will give that a go when i get in the office and keep you posted.
 
 
 

speeding up a file conversion from text file to XML format

Post by UGV0ZXIgTm » Wed, 05 Mar 2008 01:14:01

hanks Chris,
That has given me food for thought. I cant thank you guys enough, you learn
something new on every visit here. Keep up the good work

"Chris Dunaway" wrote: