Solved

Vertical File into a Horizontal SQL Table

Posted on 2012-03-26
8
333 Views
Last Modified: 2012-03-30
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
0
Comment
Question by:GPCDIADMIN
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 9

Expert Comment

by:OCDan
Comment Utility
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
 

Author Comment

by:GPCDIADMIN
Comment Utility
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
 
LVL 9

Expert Comment

by:OCDan
Comment Utility
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
 

Author Comment

by:GPCDIADMIN
Comment Utility
I am using an AS/2 connector to collect the files, I do not have control of changing the file format.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 3

Expert Comment

by:tsnirone
Comment Utility
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
 

Author Comment

by:GPCDIADMIN
Comment Utility
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
 
LVL 3

Accepted Solution

by:
tsnirone earned 500 total points
Comment Utility
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now