INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Tools for converting XML to Flatfile ?

Tools for converting XML to Flatfile ?

(OP)
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

RE: Tools for converting XML to Flatfile ?

XSLT.

Tutorial available at http://www.w3schools.com/xsl/. In particular, you will want to use <xsl:output method="text" encoding="utf-8"/>.

You will find an example for doing something quite similar in this thread: thread426-1723716: Using 1 XSLT on Multiple (millions of) XML Files - Batch. 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

RE: Tools for converting XML to Flatfile ?

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

RE: Tools for converting XML to Flatfile ?

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 --> vbscript

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

RE: Tools for converting XML to Flatfile ?

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 --> vbscript

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 

RE: Tools for converting XML to Flatfile ?

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close