Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:

A    
2    
B    
G    
-blank line here-    
8    
R    
F    
6

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:

A,2,B,G
8,R,F,6

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.

share|improve this question
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. –  Shawn Melton Jan 30 at 17:15

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks - I think that might be my only option right now –  user1873604 Jan 30 at 18:16

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?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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