Fixed-width to XML

[jenlion]

I don't know enough about xml. Hope someone can help.

I have a folder that will receive fixed-width text files.

I need to convert these files into an xml file.

The flat file is basically like this:

12345     John      Doe     123 Elm St   Austin  TX78705   83094

And the XML file is basically like this:

<SalesOrder>
  <CustomerID>12345</CustomerID>
  <ShipToAddress>
    <Street> 123 Elm St </Street>
    <City>Austin</City>
    <State>TX</State>
    <Postal>78705</Postal>
  </ShipToAddress>
  <SalesOrderLines>
    <Item>83094</Item>
    <Qty>1</Qty>
  </SalesOrderLines
</SalesOrder>

How do I cheaply and easily convert one to the other?

We have SQL 2000 available to us but not 2005. Might consider a commercial tool (do I need an XSLT?) if not too expensive, and it once the xslt is created, the tool doesn't reside on the machine any more.

Help?

 

[tsuji]

>We have SQL 2000 available to us but not 2005. Might consider a commercial tool (do I need an XSLT?) if not too expensive, and it once the xslt is created, the tool doesn't reside on the machine any more.
I would go with a simple vbs/js script on the windows platform to do it. It is cost-free. Since you ask concerning cost as well, I would assume you do not know that much on the art of scripting. I can show you how to devise such script. But before doing that, could you clarify what fixed width is involved? Your sample line does not seem to demonstrate correctly such a fixed width.

 

[Miros]

I think he means the first 10 characters on the line are the customer number, the next 10 are the customer's first name, etc.

 

[jenlion]

Exactly. No delimiters -- first ten characters above are number, the next ten are first name, the next eight are last name, etc. Obviously this is not my actual file, just an example I typed in.

I could certainly write a little program or script that would do this. Problem is management has deemed that this must be an easy and repeatable task. We have several non-programmers in our group that they still consider 'technical' and management wants THEM to be able to do this too. No custom code. We might get excel files, csv files, more flat files, who knows what as a source, but we need that xml file out.

Thought it would be pretty simple -- it's just data -- like Oh, an xslt can do that, or something. (Now I think an xslt is something you apply to a file that is already xml, not a flat file).

Worked this weekend with Altova's MapForce and it seems to do what I need. Making the map is easy (click and drag), anyone could do it. Then it genenrates code (I did C#) and you can stick the exe on the end client's machine to repeat the task. We'll create something else to trigger looking for the file.

Management may balk at us having to buy yet another tool, though, so if there are other suggestions, I'm all ears!!

 

[harebrain]

How about using DTS to read the files into a temporary table and doing SELECT * ... FOR XML?
(Check the syntax, I'm working from old memory.)

 

[jenlion]

harebrain, I have tried that and my hope is that it would work. Trouble is that it doesn't wind up in an xml format that is usable to me -- it's random.

If I could get it to name the elements appropriately, I'd be in good shape. But it would have to be something done very, very easily, given management's requirement that non-coders be able to do it.

If you know more about getting stuff out of sql and into a particular xml format, I am all ears! Thanks

 

[harebrain]

Regrettably, I am no longer in a SQLServer shop, so I can't offer more than the original idea. Perhaps you can rework the question along this line and post in the SQLServer or DTS forum. I'm sure you'll get plenty of suggestions.

 

[tsuji]

>Exactly. No delimiters -- first ten characters above are number, the next ten are first name, the next eight are last name, etc.
This is not good enough as a specification of data format. Why do you stop at the last name? How about continue the description? This is not a chat in the pub but computer science.

 

[jenlion]

Hey, dude, that's why it's an EXAMPLE.

I don't see any need to copy in the entire flat file (complete with my customer's data).

Some of us with more than fifteen years of experience have learned to think a little more abstractly than that. CS is hardly some abstract realm. You CAN talk in a pub about a problem. "Hey, how do you get a flat file, or a delimted file, or an excel file over to xml easily? Repeatedly? And how can it be so easy that any kiddie can do it without screwing it up?" "Oh, man, we need another beer, and we'll discuss your options. 1 - shoot the customer for having stupid expectations. But assuming you want to continue making money, we'll move on to 2 - shoot management for continuing to hire people who "don't do code". Assuming you want to keep your job, to continue making money, we'll move on to 3. Have you played with Altova's stuff yet? Free for thirty days, you oughtta check it out. Wanna shoot some pool? Cool. Anyways, maybe you could set this up as a DTS package. There's SOME code involved, but the monkeys can be taught. Hey, have you seen the new stuff in SSIS in SQL 2008?"

And, the best thing is, when you're a consultant, you can bill that conversation. And the beers (they were dinner and you were on site). Trouble is, you're expected to know all things about all things, and nobody keeps everything in their head at one time. Great thing when you have a place (like this forum) to turn for a pointer in an area you don't normally work in. Responses like yours -- rude and not necessary. Guess there's irony in breaking the rule here myself, but -- if you don't have anything nice (or helpful) to say, don't say anything at all!

 

[tsuji]

>I don't see any need to copy in the entire flat file (complete with my customer's data).
I don't see neither and don't waste forum's time. All I am asking is the specification of the fixed-width format. After the last name, you can read stopping before postal code or you can read stopping after the postal code as a data entry. How can you resolve that by looking at the "example"?

You parade all kinds of software with pride as if it is your making. If only those counts, everybody should be shut up on space science outside of NASA.