Vertical File into a Horizontal SQL Table

All-

I have an issue where I am trying to import a flat file that is set up vertically and I need to set up a table in SQL.  I have provided a couple of some test data.  Any direction would be appreciated, I would prefer to handle this via a DTS package to collect the file and import it directly into a table.

Thanks…
Test-Data-for-EE.txt
GPCDIADMINAsked:
Who is Participating?
 
tsnironeConnect With a Mentor Commented:
I tried to search to find a stored procedure for you that you could alter/build on but was unsuccessful :(

In a stored procedure tho, you can do just about anything, so I'd import the data into a temp-file and go (lengthy) from there.

Good luck :)
0
 
OCDanCommented:
What do you want your resultant table to look like?

p.s. that is one ugly ass flat file, whatever happened to commas :D
0
 
GPCDIADMINAuthor Commented:
I would like Columns to be

ISA
GS
ST
BEG
REF
DTM
TD5
N9
MSG
N1
N3
N4
PER
PO1
PID
MSG
TXI
CTT
SE
GE
IEA


Thanks...
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
OCDanCommented:
Someone else may have an answer but there doesn't seem to be any common delimeters to me so creating a generic script would be damn near impossible.

You could hardcode some charindex code in there but again given the lack of continuity searching for 2-4 characters would likely result in a lot of anomalous results.

Can you get that flat file changed at all? Are you sent this or are you creating this yourself?
0
 
GPCDIADMINAuthor Commented:
I am using an AS/2 connector to collect the files, I do not have control of changing the file format.
0
 
Anthony PerkinsCommented:
Ah yes, the infamous EDI format.  I would suggest you build an application using .NET to handle this.

>>I would like Columns to be <<
Unless you use very generic varchar columns allowing for the maximum length you will not be able to do this.
0
 
tsnironeCommented:
As long as the files you receive conforms to a standard where you can "divide" the input into columns (Seems the "~" is columnseparator(?) - is this X12?) I see no problem in importing this data into a table and massage it in (ie) stored procedure to your liking...
0
 
GPCDIADMINAuthor Commented:
Correct this is X12 style, I had some thoughts last night I am going to try out.  It will be lengthy but I wanted to see if anyone has done this before through a DTS or SQL in general.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.