SQL SSIS

I want to import text documents into sql however it doesn't come in a CSV format and I cant change this.

we receive all files in the attached format. The only thing is that the amount of rows of data between each column can change. I want to insert all the lines of data into 1 column with just a space between line breaks.  Is this possible and how would I go about doing? I have basic knowledge of SQL but nothing advanced.


Contact name:	
data data data	
Company:	
company name goes in here
Address:	
address
address
address
Notes:
around 10 lines of notes could be placed into here.	

Open in new window

LVL 6
CaptainGibletsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I want to insert all the lines of data into 1 column
No problem.  Just map it to a destination that has only one column.

>with just a space between line breaks.
You'll probably want to import it as-is first, then run T-SQL to remove line breaks that goes somethign like this...

UPDATE YourTable REPLACE(column_name, char(13), ' ')

Open in new window

0
CaptainGibletsAuthor Commented:
I can have random text either before the information that I need to receive as well though. Wont that just create 1 massive line of text? so how would I find what I want and move it in to the corresponding column?
0
Barry CunneyCommented:
You could create a Script Task in SSIS and write code in this Script Task which parses the file into a CSV file, which will then be in a nice uniform format for the Data Flow task.

You could use a StreamReader, StreamWriter object in C# in the SSIS Script task, to read your file line by line an decide what to do with each piece.
Pseudo logic would be something like the following

Write a a one off header line to your new parsed file
Contact name, Company, Address, Notes

Then loop through each line in original file

Start of Loop
       If line contains : (colon) it is a column header line
       append all next lines to a string variable for the new formatted line for the parsed file
     if you hit another colon : then moving onto new column so append a , (comma delimiter to line variable)
If you hit a new record(what signifies a new record in your file?) then write the new formatted line to the new file, clear out the line variable and start constructing a new line for the formatted CSV file  
End of Loop
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.