Tilde Delimited to AS400

Can we take a tilde delimited file and feed it to AS400 as is, or does the file need to be converted to fixed width? I am told by the AS400 engineer that the file needs to be converted to fixed width.

The environment is made up of SQL Server 2005, Visual Studio 2005, and AS400. The tilde delimited file is given by an outside source as is. We are trying to take this file and get it to AS400 to process purchase orders.

Below is a sample of a file we need to feed into the AS400. Original thought was to convert to a format which SQL Server can read, break up into different tables by group, i.e. PROCESSPO, PO, and LINE, so we can properly define columns. Then create files, in this case 3, for the developer feed into the AS400. There will always be 5 different types of records but I am only providing 3 record types because this is all the data I am given to work with.

PROCESSPO~TP-4186~CARQUEST~ALLTRADE~1.2~2011-04-06T13:24:45.0开发者_开发百科00-07:00  
PO~TP-4186~TP-4186~2011-04-06T13:24:45.000-07:00~TR-13824~TR-13824~1~0~RAL~Raleigh DC~2635 Millbrook Road~~~Raleigh~NC~27604~US~555-555-5555~177200~DCS Raleigh Store~2635 Millbrook Road~~~Raleigh~NC~27604~US~555-123-4567~  
LINE~TP-4186~1~TP-4186~430937~TLS~1.0~EA~FEDGRD~ALLT_LB~Alltrade Tools LLC~1431 Via Plata~~~Long Beach~CA~90810~US~310-522-9008~

Since I don't know AS400 I don't know how it will take in this data but I have spoken to different resources who say it can accept Excel spreadsheets but not sure how it will read the data because they have only worked with spreadsheets having set number of columns across the board. I also pinged the Stackoverflow community for this same reason.

AFTER SPEAKING WITH THE AS400 DEVELOPER IT WAS DECIDED THAT ONE FIXED WIDTH FILE WAS HIS IDEAL SOLUTION BUT THAT IT WAS NOT A REQUIREMENT, MORE OF A DESIRE THAN ANYTHING ELSE. I CREATED MULTIPLE FILES AND AM FEEDING THE DATA DIRECTLY INTO THE AS400 FROM VISUAL STUDIO 2005. THANKS TO ALL FOR YOU FEEDBACK.

Update: Completely changing answer to match new question.

As a single file, no the IBM i cannot deal with that file. An upload can only go into one table. As mentioned in another answer, you can upload 5 individual files for each record type, but why write a program to do that when your program can just parse everything and directly add it to the database. This program can be easily written in any language including RPG.

One thought, this looks like an EDI document. Is this true? If so, there are software packages that can automatically parse this and dump into tables. I am researching EDI myself right now.

An AS400 can handle a tilde-delimited file, but only if the AS400 programmer wrote the code to do so. I think your AS400 engineer is really telling you that they have no intention of modifying their code to accommodate tilde-delimitation, so it's up to you to translate the file into a fixed-width format.

I'm watching the stream of comments accumulating under Mike Wills's answer and thinking that Stack Overflow, cool as it is, is probably not the best medium for that conversation. ;)

Still, data interfacing is one of those tasks that is pretty much always achievable, so it would be nice to get closer to a solution here. (I expect to edit this answer if it too starts to collect comments.)

[Edit: Making use of additional comments and sample data added to original question]

Since it looks like your various records are incompatible formats, I would go with your proposed plan of splitting the received tilde-delimited input into separate tables or files, one for each record type. Then it should be easy enough to get that data into the AS/400.

At this point, I'm not sure what the exact problem is. It was good that you added so much information to your question, and your basic approach seems sound to me. If you can give more specifics about what your AS/400 person needs from you, that might be helpful. (For example, are there already appropriate files on the AS/400 that are ready to accept the data?)

Note that the FTP server on an AS400 is really smart and can convert incoming flat files into AS/400 records as part of the FTP upload process.

I would therefore suggest investigating a conversion of the tilde-delimited file to a form understood by the FTP server (probably by replacing tilde with tabs).