Importing XML files

[Guest]

H

I have several (175) XML files that I am going to need to import every day into a access database. When I currently import the XML files Access creates 4 different tables but I need to import the data into just one table. Just to add to the difficulty each XLM file does not contain a identifier in the data just in the filename itself. I also only need certain fields from the import and not all

So what I need to do is only import specific records from the XML file, import it into a different table than specified in the XML file itself and also import the filename into the same table at the same time

I am using Access 2003 and any help is appriciate

Thanks

 

[Joe Fallon]

You are going to have to set up an entire process for one of these files (I
assume they are all the same.)
This process would include importing to 4 temp tables.
Then using append queries to move the data tot he real table.
Then purging the temp tables and importing the next file and repeating.
You can list all the files in a table and then loop through the table to
import them.


How to Import all Files in a Folder:

Private Sub btnImportAllFiles_Click()
'procedure to import all files in a directory and delete them.
'assumes they are all the correct format for an ASCII delimited import.
Dim strfile As String

ChDir ("c:\MyFiles")
strfile = Dir("FileName*.*")
Do While Len(strfile) > 0
DoCmd.TransferText acImportDelim, "ImportSpecName", "AccessTableName",
"c:\MyFiles\" & strfile, True
'delete the file (consider moving it to an Archive folder instead.)
Kill "c:\MyFiles\" & strfile
strfile = Dir
Loop

End Sub

===========================================

How to Add Directory File Names to an Access Table:

Create a table named tblDirectory with 2 fields:
FileName (Text 250)
FileDate (Date/Time)

Call the code below by pressing Ctrl-G to open the debug window and type:
GetFiles("c:\windows\")

Paste this code into a regular module:

Sub GetFiles(strPath As String)
Dim rs As Recordset
Dim strFile As String, strDate As Date

'clear out existing data
CurrentDb.Execute "Delete * From tblDirectory", dbFailOnError

'open a recordset
Set rs = CurrentDb.OpenRecordset("tblDirectory", dbOpenDynaset)

'get the first filename
strFile = Dir(strPath, vbNormal)
'Loop through the balance of files
Do
'check to see if you have a filename
If strFile = "" Then
GoTo ExitHere
End If
strDate = FileDateTime(strPath & strFile)
rs.AddNew
'to save the full path using strPath & strFile
'save only the filename
rs!FileName = strFile
rs!FileDate = strDate
rs.Update

'try for next filename
strFile = Dir()
Loop

ExitHere:
Set rs = Nothing
MsgBox ("Directory list is complete.")
End Sub

========================================
--
Joe Fallon
Access MVP

Hi

I have several (175) XML files that I am going to need to import every day

into a access database. When I currently import the XML files Access
creates 4 different tables but I need to import the data into just one
table.
Just to add to the difficulty each XLM file does not contain a identifier in
the data just in the filename itself. I also only need certain fields from
the import and not all.

So what I need to do is only import specific records from the XML file,

import it into a different table than specified in the XML file itself and
also import the filename into the same table at the same time.

 

[Guest]

Jo

Thanks for tha

Simo