I have a flat file i need to import into SQL. For a lot of boring reasons, I curently don't have access to use BIDS, only the import wizard in SSMS. The flat file looks sort of like:

-blank line here-    

Basically, a few lines, separated by an empty line, Repeat a few thousand times. The text field here doesn't show it all that well, if you have questions about that, I can try to clarify.

What i need is for each of those groups to be its own row, merged together with a delimiter. So, the data in the table would look like:


Only one column in the table, with a delimited string of data in that column.

Can this be accomplished via the wizard? If not, can it be accomplished with BIDS and a real SSIS package? That may be an option, but it will probably be a while before I have access to that, and by that time, it may be moot.

  • 1
    What about PowerShell, not thinking about it much but if I could not get access to SSIS think that would be my next path to check. Only other thing might be T-SQL query using bulk insert maybe.
    – user507
    Jan 30, 2014 at 17:15

2 Answers 2


You could use the wizard and load the data into a temp table then process it from there. That's probably your easiest option. Unfortunately while I know there is a relatively easy way to do the processing I just can't remember what it is.

The other option is going to be to use BIDS and you will probably have to use a script source to construct your input.


I would possibly run a short program that picks up the original text file and basically just runs a string replace. If the blank line is genuinely blank then search for newline (\n\r) and convert to , then search for ,, and convert to newline and then dump the result out to a new text file - or something similar.

Probably need tweaking and as I don't know the format of the file the above might not be practical, but basically convert the file to the format you want before importing?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.