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 name goes in here
around 10 lines of notes could be placed into here.	

Open in new window

Who is Participating?
Barry CunneyConnect With a Mentor Commented:
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
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

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?
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.