Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Tools for converting XML to Flatfile ?

Status
Not open for further replies.
frontside

frontside

Technical User
Sep 26, 2002
85
SE
Hi,

I need to convert a XML file into a flatfile structure that I could use for importing into our business system (orderimport).
Are there any good mapping Tools for doing this or whats the most common way of handling conversions like this?
I´ve looked into Altova Mapforce but havent found much good Reviews.

The flatfile needs to be converted like this(small example).

XML file
<MessageHeader>
<SenderID>1000</SenderID>​
<RecieverID>1212</RecieverID>​
</MessageHeader>
<Orderline>
<OrderNo>3434</OrderNo>​
<OrderRow>10</OrderRow>​
<ArticleNo>12439-2</ArticleNo>​
</Orderline>

Flat File

<HEAD>
1000;1212;
<ROW>
3434;10;12439-2

<MessageHeader> needs to convert to <HEAD>
<Orderline> needs to convert to <ROW>
the rest of the fields needs to be split with ;


Any Ideas/suggestions?

//Mike
 
Sort by date Sort by votes
XSLT.

Tutorial available at In particular, you will want to use <xsl:eek:utput method="text" encoding="utf-8"/>.

You will find an example for doing something quite similar in this thread: thread426-1723716. Look at the posts beginning with 26 Dec 13 16:41.

In the same thread, in the two posts beginning at 14 Jan 14 16:06, you will find a simple JScript that can use the Microsoft MSXML.DLL already present on your Windows machine. No extra cost at all.

My own opinion of the Altova product is that it has a lot of complexity but the complexity seems to add very little value. I use Stylus Studio which has the ability in its lowest cost offering to debug an XSLT. This particular project, however, is relatively easy, and if you build your solution a bit at a time testing with the script found in the referenced thread, you will probably not need any other development tool beyond Notepad.

Tom Morrison
Hill Country Software
 
Upvote 0 Downvote
If that's all and it's just a file, I'd consider just notepad and replace

<MessageHeader> -> <HEAD>
<SenderID> ->""
</SenderID> ->;
<RecieverID> ->""
</RecieverID> ->;
</MessageHeader> ->""
<Orderline> -> <ROW>
<OrderNo> ->""
</OrderNo> ->;
<OrderRow> ->""
</OrderRow> ->;
<ArticleNo> ->
</ArticleNo> ->;
</Orderline> ->""

Cheers,
Dian
 
Upvote 0 Downvote
You can consider this as your last resort in cases when you got a malformed XML file and cannot use XML DOM to traverse it. This script manipulates an XML file as a text file using vbscript.

What I did here was saved the data you provided as sample.xml and create an Output.txt with the structure you specified.
Code: 
Set XMLFile = oFSO.OpenTextFile("sample.xml",1)
Set OutFile = oFSO.OpenTextFile("Output.txt",2,True)

Set r = New RegExp
	With r
		.Pattern = ".+?\>(.+)?\<.+"
	End With
	
text = Split(XMLFile.ReadAll,vbCrLf)	

For i = 0 To UBound(text)
	If InStr(text(i),"<MessageHeader>") Then
		Call WriteFlatFile(i + 1, "<HEAD>", "</MessageHeader>")
	End If
	If InStr(text(i),"<Orderline>") Then
		Call WriteFlatFile(i + 1, "<ROW>","</Orderline>")
	End If
Next

Sub WriteFlatFile(n,Header,Terminate)
	OutFile.Write Header & vbCrLf
	For x = n To UBound(text)
		If InStr(text(x),Terminate) Then
			Exit For
		End If
		h = h & r.Replace(text(x),"$1") & ";"
	Next
	OutFile.Write h & vbCrLf
End Sub

-Jake Speed
 
Upvote 0 Downvote
My apologies [hammer], I realized that the code was truncated and the first line which is the most important is missing. Here is the complete code.
Code: 
Set oFSO = CreateObject("Scripting.FileSystemObject")

Set XMLFile = oFSO.OpenTextFile("sample.xml",1)
Set OutFile = oFSO.OpenTextFile("Output.txt",2,True)

Set r = New RegExp
	With r
		.Pattern = ".+?\>(.+)?\<.+"
	End With
	
text = Split(XMLFile.ReadAll,vbCrLf)	

For i = 0 To UBound(text)
	If InStr(text(i),"<MessageHeader>") Then
		Call WriteFlatFile(i + 1, "<HEAD>", "</MessageHeader>")
	End If
	If InStr(text(i),"<Orderline>") Then
		Call WriteFlatFile(i + 1, "<ROW>","</Orderline>")
	End If
Next

Sub WriteFlatFile(n,Header,Terminate)
	OutFile.Write Header & vbCrLf
	For x = n To UBound(text)
		If InStr(text(x),Terminate) Then
			Exit For
		End If
		h = h & r.Replace(text(x),"$1") & ";"
	Next
	OutFile.Write h & vbCrLf
End Sub
 
Upvote 0 Downvote
Hi, thanks for the replies and since I´m a newbie on both XML and programming I have to do some research :)

I will se if the script would be a good way to go or if a dedicated program suits my needs better since I´m no expert in programming.

The Project I´m doing this research for is a EDI import with around 30 to 100 files Daily.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

smak1
  • Locked
  • Question
add structure to XML
Replies
0
Views
296
smak1
smak1
Bijan Borazjani
  • Locked
  • Question
XML to PDF
Replies
0
Views
348
Bijan Borazjani
Bijan Borazjani
cdr_greg
  • Locked
  • Question
Display XML Line Breaks in Microsoft Word Content Controls
Replies
0
Views
400
cdr_greg
cdr_greg
P
  • Locked
  • Question
How to re-use a recursive template
Replies
1
Views
349
pdrapeau
P
198709km
  • Locked
  • Question
Grouping XML key value using XSLT
Replies
1
Views
322
atlopes
atlopes

Part and Inventory Search

Sponsor

Back
Top