Iulian -207023 (7/15/2014)
Koen Verbeeck (7/15/2014)
Indeed, the file is formatted quite well in the sense that it is very predictable.How I would do it:
* read the file in line by line as one giant column (choose a delimiter that will never be present in the file, for example |$&|)
* use a conditional split to get rid of the lines you don't need (empty lines and header)
* implement a script transformation to parse the remaining lines. You can use the first three characters of a line to decide what to do.
* output all the columns combined into one single row. You can use STN to find out when a new row starts.
Hi Koen, how to read line by line ?
I did this outside SQL, I am sure it can be done on SQL too, I am just curious about a hint.
thanks
You can find an example here:
Creating an Asynchronous Transformation with the Script Component
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP