I have to extract data from Flat files and load into a staging database
ex: I have this flat file without column header and i would like to get each row into a seperate column Text file data: This is Actual format
01008012603181700250318
02010691700001988800000000000000000000000
51X11055907014X10000002200000000220000000003671100000000001124000000002110559070145
51X61630051007X10000000005000000000500000000016100000000009999000000002616300510071
60350000002205000000000002
02010691713001988900000000000000000000000
51X08042508025X10000001000000000100000000001670800000000000806000000002080425080250
51X08040547025X10000001000000000100000000001670800000000000806000000002080405470255
60350000002000000000000002
02011511724001989000000000000000000000000
112997241682493 01
51X08066360013X10000002200000000170000000002830800000000000806000000002080663600135
53-00000050098234151 00
112997241682493 01
51X08061643013X10000001500000000150000000002500800000000000806000000002080616430130
683000000000000000000000000
60350000003200000000000002
01008022603181734250318
160206917349000002B26031802
6724-000004400000000000002
320206917340201Y180326
3301000005321500000532150000000000000000000002
Conditions: left (col,2) = 01 into one column and left(col,02) into another column ..so on I have to read through multiple flat files like this. The column names are like column01,column02,column51. In this case 01 remains the main column for any multiple rows. I will need to loop through atleast 100 files like this.
Sample output
Col01 col2 Col3
01008012603181700250318 02010691700001988800000000000000000000000 51X11055907014X10000002200000000220000000003671100000000001124000000002110559070145
Update 1: As suggested by @Tab Alleman , i have loaded all text into single column in SQL table. I have tried splitting rows by string_split function, could not be able to acheive result. Delimiter is a space, but some row data has multiple spaces. I have also tried by creating string_split function posted in stack.
ex: 01594022603181352250318 02027061352002288200000000000000000000000 112997232009865 01 51X06082082020X10000000600000000054000000000900600000000000601000000002060820820200 56-00000006012 00 112997232009865 01
If you really want the entire file to just produce one row, all you have to do is set the CRLF character (or whatever your file's row terminator character is) to be the Column delimiter character in your Flat File Connection. Every row of your file will be a column in the data flow.