I have a series of text file which I have import into a table with a single column spanning the whole line in the file and using a variation of Jeff Moden's article http://www.sqlservercentral.com/articles/T-SQL/63003/
to split the sinlge column into individual columns based on a delimiter. This works fine (thanks Jeff ;-)).
The problem with one of the files that I have to import has embebed carriage return characters which results in the import line being split over several lines. For example, if I should have 10 columns it might look like this:
where the last columns are on the second line.
I need to concatentate the second row to the first row to create a row with 10 values in it so that I can split it into individual columns.
The problems is more complicated because the number of embebed carriage returns varies so that the number of rows over which each actual row is split varies and the number of columns on each row can vary as the embeded carriage returns can appear in different columns.
The solution I have at the moment is to take each row and if the number of columns (based on the number of separators) is less than expected then concatenate the next row, test and continue concatenating the next row until I get the correct number of columns (separators).
I know that this will work but is there another, set based solution?