473,883 Members | 2,212 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problems importing xml to access2k

First go with trying to import xml to a database and whilst i have
managed to do what i want i find that the xml files we have here at
work are gonna cause me problems. I have a function that imports any
aml file, at the moment its importing to a hardcoded (name only) table
but i can manage to work out how to take the xml file name and use that
for the table name. My problem is some of the xml files do not follow
the structural conventions that are in place at this office they should
be as below...

.....
<DATAPACKET Version="2.0">
<METADATA>
<FIELDS>
<FIELD attrname="ENG_I D" fieldtype="r8" />
<FIELD attrname="JOB_D ATE" fieldtype="date Time" />
....
Followed by all the rest of the field names and types
....
</FIELDS>
<PARAMS LCID="1033" />
</METADATA>

Then tha actual Data
<ROWDATA>
<ROW ENG_ID="45" JOB_DATE="20060 329" ACTIVITY_ID="44 " QTY="1"
DURATION="75" SENT="Y" />
....
....
</ROWDATA>
</DATAPACKET>

providing that the fields are listed in the top section my function
will create a new table and populate it with the firlds and data from
the xml.

However some xml files have missing field data in the top or bottom
section ie a fiels is listed in the <fields> section but there is
nothing in the <rowdata> and vice versa. Guys this is so my first go
at this and ive struggled to get this far can anyone point me in the
right direction of how to get round this. Plus if theres anything in
the function that could be improved tell me. I could use the lessons. (
hopes there are no KIng Black Dragons lurking to flame a dumb nub)
antway heres the function and ty in anticipation

Private Function ProcessSinglePr oblemXML(ByVal sFile As String) As
Boolean
On Error Resume Next
Dim objDomDoc As New DOMDocument
Dim objRoot As IXMLDOMNode
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim child As IXMLDOMNode
Dim c As Integer
Dim r As Integer
Dim Maxc As Integer
Dim Maxr As Integer
Dim FieldNames() As String
Dim FieldType() As String
Dim Records() As String
Dim tdfNew As TableDef
Dim strField As String
Dim strFieldType As String
Dim fldNew As Field

Set dbs = CurrentDb

sFile = cXML_File_Folde r & sFile
'If Dir(sFile) = "" Then Exit Function

objDomDoc.async = False
objDomDoc.Load sFile
Set objRoot = objDomDoc.docum entElement
'Get No of Records (sure that there is a better way)
Maxr = 0
Maxc = 0
For Each child In objRoot.childNo des(1).childNod es
Maxr = Maxr + 1
Next
For Each child In objRoot.childNo des(0).childNod es(0).childNode s
Maxc = Maxc + 1
Next
Maxc = Maxc - 1
Maxr = Maxr - 1

ReDim FieldNames(Maxc )
ReDim FieldType(Maxc)
ReDim Records(Maxc, Maxr)
'Create fieldname array
c = 0
For Each child In objRoot.childNo des(0).childNod es(0).childNode s
FieldNames(c) = child.Attribute s.getNamedItem( "attrname").nod eValue
FieldType(c) = child.Attribute s.getNamedItem( "fieldtype").no deValue
c = c + 1
Next
'Initialise rowdata array

'Add rowdata to Records Array
c = 0
Do While c <= Maxc
r = 0
For Each child In objRoot.childNo des(1).childNod es
Records(c, r) =
child.Attribute s.getNamedItem( FieldNames(c)). nodeValue
r = r + 1
Next
c = c + 1
Loop

'Create table starts here
Set dbs = CurrentDb
On Error Resume Next
DoCmd.DeleteObj ect acTable, "MyNewTable 1"
On Error GoTo 0

Set tdfNew = dbs.CreateTable Def("MyNewTable 1")
c = 0
Do While c <= Maxc

strField = FieldNames(c)
strFieldType = FieldType(c)

Select Case strFieldType
Case "r8"
Set fldNew = tdfNew.CreateFi eld(strField, dbText, 50)
Case "dateTime"
Set fldNew = tdfNew.CreateFi eld(strField, dbText, 50)
Case "string"
Set fldNew = tdfNew.CreateFi eld(strField, dbText, 50)
End Select

tdfNew.Fields.A ppend fldNew

c = c + 1
Loop
dbs.TableDefs.A ppend tdfNew

Set tdfNew = Nothing
Set fldNew = Nothing
'End Create table works up to here
Set rst = dbs.OpenRecords et("MyNewTable1 ")
r = 0
For r = 0 To Maxr
c = 0
rst.AddNew
For c = 0 To Maxc
If Records(c, r) <> "1" Then
rst.Fields(Fiel dNames(c)) = Records(c, r)
Else
rst.Fields(Fiel dNames(c)) = "N/A"
End If

Next

rst.Update
Next
rst.Close
'If unable to read xml store a detail of why
If Err.Number = 91 Then
Set rst = dbs.OpenRecords et("FIL_IMPRT_E RRRS", dbOpenDynaset)
rst.AddNew
rst!TYP = "PRBLM"
rst!BAD_FIL_NM = Replace(sFile, "Problem", "Reject")
rst!ERRR_DAT = Now()
rst.Update
rst.Close
Set rst = Nothing

ProcessSinglePr oblemXML = False
Exit Function
Else
ProcessSinglePr oblemXML = True
End If
'On Succcess exit function
If Err.Number = 0 Then ProcessSinglePr oblemXML = True
'Clean up
Set rst = Nothing
Set dbs = Nothing
Set objDomDoc = Nothing

End Function

Apr 26 '06 #1
5 2481

Be***********@g mail.com wrote:
First go with trying to import xml to a database and whilst i have
managed to do what i want i find that the xml files we have here at
work are gonna cause me problems. I have a function that imports any
aml file, at the moment its importing to a hardcoded (name only) table
but i can manage to work out how to take the xml file name and use that
for the table name. My problem is some of the xml files do not follow
the structural conventions that are in place at this office they should
be as below...

....
<DATAPACKET Version="2.0">
<METADATA>
<FIELDS>
<FIELD attrname="ENG_I D" fieldtype="r8" />
<FIELD attrname="JOB_D ATE" fieldtype="date Time" />
...
Followed by all the rest of the field names and types
...
</FIELDS>
<PARAMS LCID="1033" />
</METADATA>

Then tha actual Data
<ROWDATA>
<ROW ENG_ID="45" JOB_DATE="20060 329" ACTIVITY_ID="44 " QTY="1"
DURATION="75" SENT="Y" />
...
...
</ROWDATA>
</DATAPACKET>

providing that the fields are listed in the top section my function
will create a new table and populate it with the firlds and data from
the xml.

However some xml files have missing field data in the top or bottom
section ie a fiels is listed in the <fields> section but there is
nothing in the <rowdata> and vice versa. Guys this is so my first go
at this and ive struggled to get this far can anyone point me in the
right direction of how to get round this. Plus if theres anything in
the function that could be improved tell me. I could use the lessons. (
hopes there are no KIng Black Dragons lurking to flame a dumb nub)
antway heres the function and ty in anticipation

Private Function ProcessSinglePr oblemXML(ByVal sFile As String) As
Boolean
On Error Resume Next
Dim objDomDoc As New DOMDocument
Dim objRoot As IXMLDOMNode
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim child As IXMLDOMNode
Dim c As Integer
Dim r As Integer
Dim Maxc As Integer
Dim Maxr As Integer
Dim FieldNames() As String
Dim FieldType() As String
Dim Records() As String
Dim tdfNew As TableDef
Dim strField As String
Dim strFieldType As String
Dim fldNew As Field

Set dbs = CurrentDb

sFile = cXML_File_Folde r & sFile
'If Dir(sFile) = "" Then Exit Function

objDomDoc.async = False
objDomDoc.Load sFile
Set objRoot = objDomDoc.docum entElement
'Get No of Records (sure that there is a better way)
Maxr = 0
Maxc = 0
For Each child In objRoot.childNo des(1).childNod es
Maxr = Maxr + 1
Next
For Each child In objRoot.childNo des(0).childNod es(0).childNode s
Maxc = Maxc + 1
Next
Maxc = Maxc - 1
Maxr = Maxr - 1

ReDim FieldNames(Maxc )
ReDim FieldType(Maxc)
ReDim Records(Maxc, Maxr)
'Create fieldname array
c = 0
For Each child In objRoot.childNo des(0).childNod es(0).childNode s
FieldNames(c) = child.Attribute s.getNamedItem( "attrname").nod eValue
FieldType(c) = child.Attribute s.getNamedItem( "fieldtype").no deValue
c = c + 1
Next
'Initialise rowdata array

'Add rowdata to Records Array
c = 0
Do While c <= Maxc
r = 0
For Each child In objRoot.childNo des(1).childNod es
Records(c, r) =
child.Attribute s.getNamedItem( FieldNames(c)). nodeValue
r = r + 1
Next
c = c + 1
Loop

'Create table starts here
Set dbs = CurrentDb
On Error Resume Next
DoCmd.DeleteObj ect acTable, "MyNewTable 1"
On Error GoTo 0

Set tdfNew = dbs.CreateTable Def("MyNewTable 1")
c = 0
Do While c <= Maxc

strField = FieldNames(c)
strFieldType = FieldType(c)

Select Case strFieldType
Case "r8"
Set fldNew = tdfNew.CreateFi eld(strField, dbText, 50)
Case "dateTime"
Set fldNew = tdfNew.CreateFi eld(strField, dbText, 50)
Case "string"
Set fldNew = tdfNew.CreateFi eld(strField, dbText, 50)
End Select

tdfNew.Fields.A ppend fldNew

c = c + 1
Loop
dbs.TableDefs.A ppend tdfNew

Set tdfNew = Nothing
Set fldNew = Nothing
'End Create table works up to here
Set rst = dbs.OpenRecords et("MyNewTable1 ")
r = 0
For r = 0 To Maxr
c = 0
rst.AddNew
For c = 0 To Maxc
If Records(c, r) <> "1" Then
rst.Fields(Fiel dNames(c)) = Records(c, r)
Else
rst.Fields(Fiel dNames(c)) = "N/A"
End If

Next

rst.Update
Next
rst.Close
'If unable to read xml store a detail of why
If Err.Number = 91 Then
Set rst = dbs.OpenRecords et("FIL_IMPRT_E RRRS", dbOpenDynaset)
rst.AddNew
rst!TYP = "PRBLM"
rst!BAD_FIL_NM = Replace(sFile, "Problem", "Reject")
rst!ERRR_DAT = Now()
rst.Update
rst.Close
Set rst = Nothing

ProcessSinglePr oblemXML = False
Exit Function
Else
ProcessSinglePr oblemXML = True
End If
'On Succcess exit function
If Err.Number = 0 Then ProcessSinglePr oblemXML = True
'Clean up
Set rst = Nothing
Set dbs = Nothing
Set objDomDoc = Nothing

End Function


First see the article by Danny Lesandrini:

http://www.databasejournal.com/featu...le.php/3310901

If that doesn't help I'll take a look at your code.

James A. Fortune
CD********@Fort uneJames.com

The home team flop fest (a.k.a., NBA playoffs) saddens me. But there's
a certain fairness and sense in how it's being done. It rewards good
regular season play and increases the chances of a profitable seven
game series. Teams that overcome the home team flop factor more than
make up for having a worse regular season.

May 1 '06 #2
Thanks that actually was my starting point. I think there may be 10
lines of code left from that in my code. I solved my problem by
remembering that xml is still a text file so now i parse for the
fields and parse for the @@@=@@@@ pairs compare counts and
differentiate the treatment of the xml based on that. So yea it works
but if you feel like picking the code to pieces and enligtening me on
how it could be better i would be grateful.

Many thanks

May 2 '06 #3
Be***********@g mail.com wrote:
Thanks that actually was my starting point. I think there may be 10
lines of code left from that in my code. I solved my problem by
remembering that xml is still a text file so now i parse for the
fields and parse for the @@@=@@@@ pairs compare counts and
differentiate the treatment of the xml based on that. So yea it works
but if you feel like picking the code to pieces and enligtening me on
how it could be better i would be grateful.

Many thanks


O.K. I'll take a look at your code and Danny's code. I'm not
promising anything significant or anything soon but I am interested in
figuring out the best ways to parse XML. I'll try to be gentle if your
code is rough.

James A. Fortune
CD********@Fort uneJames.com

May 3 '06 #4
rkc
Be***********@g mail.com wrote:
However some xml files have missing field data in the top or bottom
section ie a fiels is listed in the <fields> section but there is
nothing in the <rowdata> and vice versa.


See if this is any help to you. You can grab the ROW elements
attributes as a NamedNodeMap and iterate through them to get both
the baseName(field) and text(value) properties without having to
know which attributes have been included ahead of time.

<GetRowData>
Sub GetRowData(ByVa l sFile As String)
Dim proc As New MSXML.DOMDocume nt
Dim rows As MSXML.IXMLDOMNo deList
Dim row As MSXML.IXMLDOMNo de
Dim attribs As MSXML.IXMLDOMNa medNodeMap
Dim n As MSXML.IXMLDOMNo de

proc.async = False
proc.Load (sFile)
Set rows = proc.getElement sByTagName("ROW ")

For Each row In rows
Set attribs = row.Attributes
For Each n In attribs
Debug.Print n.baseName; "="; n.Text
Next
Debug.Print
Next

End Sub
</GetRowData>
May 4 '06 #5
Lol if u do do it when ur in a good mood or it may be hard to be
gentle. I also want to be able to parse xml. Go figure a successful
company sends data from 1 databse to another via xml then into a
spreadsheet by hand then back to a database god only knows how they are
still in business. Everytime someone creates a product or engineer or
any kind of list for an app or database the randomly pick new unique
id's so nothing talks to each other. But good news i cud be here for
years lol

:)

May 4 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2655
by: ekyungchung | last post by:
I did install the python 2.4 and nltk. I am trying to follow the tutorial, but I kept getting error messages about importing corpus as follows =================================================== >>> from nltk.corpus import gutenberg Traceback (most recent call last): File "<pyshell#3>", line 1, in -toplevel- from nltk.corpus import gutenberg
0
1461
by: Lauren Quantrell | last post by:
I'm hoping someone can tell me if I'm doing the correct thing here or if there is a better way to do this with Access2K connecting to SQL Server 2K: Dim myConn As String myConn = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=myCatagolName;DATA SOURCE=myServerName;Network=DBMSSOCN" CurrentProject.OpenConnection myConn
10
2159
by: Blake | last post by:
I have created an Access2K front end application that connects to a SQLServer2K backend. I use this vba code to create the connection from the Access app: Dim strConnect As String 'make sure all previous connections are closed: CurrentProject.OpenConnection "Provider=" 'create new connection string to server: strConnect = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST
0
2610
by: Lauren Quantrell | last post by:
I'm trying to drop a file from Windows Explorer (or desktop, etc.) onto a field in Access2K and capture the full file path. I found an posting below that says this is possible but I cannot simutate it. Can anyone help? Thanks ************************** previous post: Message 1 in thread
3
1803
by: TWJohnson | last post by:
Can anyone tell me how to run the Access2k wizards from code in the Runtime enviroment? I need to do Address labels..perhaps there is another way to approach to this besides the wizards. Can anyone help? Thanks, Terry
0
1525
by: Lauren Quantrell | last post by:
I'm hoping someone can tell me if I'm doing the correct thing here or if there is a better way to do this with Access2K connecting to SQL Server 2K: Dim myConn As String myConn = "PROVIDER=SQLOLEDB.1;INTEGRATED SECURITY=SSPI;PERSIST SECURITY INFO=FALSE;INITIAL CATALOG=myCatagolName;DATA SOURCE=myServerName;Network=DBMSSOCN" CurrentProject.OpenConnection myConn
26
2685
by: jamesbeswick | last post by:
I've been using Access since version 97 and I've migrated to 2003. I've noticed a substantial number of strange ActiveX/OLE and code corruption problems when writing databases. The only solution I've found is to create a new database and import all the objects from the corrupted file. Has anyone else found a solution to this annoying problem? I cannot find anything useful on Microsoft's website and I haven't seen any posts about the...
13
1571
by: Lauren Quantrell | last post by:
Is there a primer somewhere that can tell me code that works perfectly well in Access2K that won't work in 2003? I have been forced to migrate a large 2K app to 2003 and am finding a lot of things that don't work. For example: .... .OnClick = "Forms!myFormName.MyFunctionName()" doesn't work in 2003.
3
3008
by: scoots987 | last post by:
What do others do if you need to import excel files into SQL Server? My main problems are 1) zipcode formatting issues. If the column is a mix of zip and zip+4, I have problems retrieving all zipcodes. 2) If the last column contains NULL no information is imported. All this with using the Management console using Import data in SQL Server 2005. I am simply trying to import the data into NEW databases.
0
11160
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10766
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10863
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10422
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7136
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5807
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6007
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4230
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3241
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.