How to create new lines from field values

[gcheak]

Hello,

I am curious if this is possible with TextPipe.

I have a csv file with the following structure:

"Name","Number","Cost","Start Date","Tickets Remaining"
"SUPER DUPER BINGO", "#817","$2.00","3/24/2006","$1,000 - 40;$250 - 147;$245 - 61;$200 - 82;$195 - 92;$150 - 108;$50 - 10828"

Take a look at the last field "Tickets Remaining". That field contains a variable number of values delimited by a semicolon.

My goal is a new file with a line for every one of those values having the following structure:

"SUPER DUPER BINGO", "#817","$2.00","3/24/2006","$1,000 - 40"
"SUPER DUPER BINGO", "#817","$2.00","3/24/2006","$250 - 147"
"SUPER DUPER BINGO", "#817","$2.00","3/24/2006","$200 - 82"
...

Is this possible with TextPipe?

Greg

[DataMystic Support]

Sure Greg - see the sample filter Report Extraction/split out embedded fields into multiple records.fll

It looks like this:

Code: Select all

|--Input from file(s)
|   
|--Convert End of Lines - Auto to DOS
|     [X] Remove bad EOL
|   
|--Restrict fields:Comma-delimited field 2 .. field 9
|  |  [ ] Process fields individually
|  |    [ ] Exclude delimiter
|  |      [ ] Exclude quotes (if present)
|  |  Delimiter Type: 0
|  |  Custom delimiter: 
|  |  [ ] Has Header
|  |
|  +--Remove all
|      
|--Restrict fields:Comma-delimited field 11 .. field 11
|  |  [ ] Process fields individually
|  |    [ ] Exclude delimiter
|  |      [ ] Exclude quotes (if present)
|  |  Delimiter Type: 0
|  |  Custom delimiter: 
|  |  [ ] Has Header
|  |
|  +--Remove all
|      
|--Map
|     000 [\000]
|     001 [\001]
|     002 [\002]
|     003 [\003]
|     004 [\004]
|     005 [\005]
|     006 [\006]
|     014 [\014]
|     015 [\015]
|     016 [\016]
|     017 [\017]
|     018 [\018]
|     019 [\019]
|     020 [\020]
|     021 [\021]
|     022 [\022]
|     023 [\023]
|     024 [\024]
|     025 [\025]
|     026 [\026]
|     028 [\028]
|     029 [\029]
|     030 [\030]
|     031 [\031]
|     127 [\127]
|     128 [\128]
|     129 [\129]
|     130 [\130]
|     131 [\131]
|     132 [\132]
|     133 [\133]
|     134 [\134]
|     135 [\135]
|     136 [\136]
|     137 [\137]
|     138 [\138]
|     139 [\139]
|     140 [\140]
|     141 [\141]
|     142 [\142]
|     143 [\143]
|     144 [\144]
|     145 [\145]
|     146 [\146]
|     147 [\147]
|     148 [\148]
|     149 [\149]
|     150 [\150]
|     151 [\151]
|     152 [\152]
|     153 [\153]
|     154 [\154]
|     155 [\155]
|     156 [\156]
|     157 [\157]
|     158 [\158]
|     159 [\159]
|     160 [\160]
|     161 [\161]
|     162 [\162]
|     163 [\163]
|     164 [\164]
|     165 [\165]
|     166 [\166]
|     167 [\167]
|     168 [\168]
|     169 [\169]
|     170 [\170]
|     171 [\171]
|     172 [\172]
|     173 [\173]
|     174 [\174]
|     175 [\175]
|     176 [\176]
|     177 [\177]
|     178 [\178]
|     179 [\179]
|     180 [\180]
|     181 [\181]
|     182 [\182]
|     183 [\183]
|     184 [\184]
|     185 [\185]
|     186 [\186]
|     187 [\187]
|     188 [\188]
|     189 [\189]
|     190 [\190]
|     191 [\191]
|     192 [\192]
|     193 [\193]
|     194 [\194]
|     195 [\195]
|     196 [\196]
|     197 [\197]
|     198 [\198]
|     199 [\199]
|     200 [\200]
|     201 [\201]
|     202 [\202]
|     203 [\203]
|     204 [\204]
|     205 [\205]
|     206 [\206]
|     207 [\207]
|     208 [\208]
|     209 [\209]
|     210 [\210]
|     211 [\211]
|     212 [\212]
|     213 [\213]
|     214 [\214]
|     215 [\215]
|     216 [\216]
|     217 [\217]
|     218 [\218]
|     219 [\219]
|     220 [\220]
|     221 [\221]
|     222 [\222]
|     223 [\223]
|     224 [\224]
|     225 [\225]
|     226 [\226]
|     227 [\227]
|     228 [\228]
|     229 [\229]
|     230 [\230]
|     231 [\231]
|     232 [\232]
|     233 [\233]
|     234 [\234]
|     235 [\235]
|     236 [\236]
|     237 [\237]
|     238 [\238]
|     239 [\239]
|     240 [\240]
|     241 [\241]
|     242 [\242]
|     243 [\243]
|     244 [\244]
|     245 [\245]
|     246 [\246]
|     247 [\247]
|     248 [\248]
|     249 [\249]
|     250 [\250]
|     251 [\251]
|     252 [\252]
|     253 [,]
|     254 [\254]
|     255 [\255]
|   
|--VBScript script  Timeout: 10000 milliseconds
|       dim i
|       dim m
|       dim s
|     'Called for every line in the file
|     'EOL contains the end of line characters (Unix, DOS or Mac) that must be
|     'appended to each line
|     function processLine(line, EOL)
|       s=""
|       m=split(line, ",", -1, 1)
|         for i=1 to UBound(m, 1)
|           if m(i) <> "" then
|             s = s & m(0) & "," & m(i) & EOL
|           end if
|         next 
|         processLine = s
|     end function
|     
|     
|     'Called at the start of a processing job -
|     'perform one-time initialisation here
|     sub startJob()
|     end sub
|     
|     
|     'Called at the end of a processing job -
|     'destroy any declared objects here
|     sub endJob()
|       'do nothing
|     end sub
|     
|     
|     'Called before each file is opened -
|     'perform per-file initialisation here
|     function startFile()
|       startFile = ""
|     end function
|     
|     
|     'Called before each file is closed -
|     'flush all pending file output here
|     function endFile()
|       endFile = ""
|     end function
|   
+--Output to file(s)