21 Replies Latest reply: May 26, 2015 11:38 AM by Jes Lee RSS

    Loading a pipe-delimited text file with a separate data file via Qlik Sense

    Jes Lee

      I need to build a relational database using a schema file and the actual data file that are both pipe delimited text files.  Does anyone know how I can go about doing this?

       

       

      Thank you,

      Jessica

        • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
          Petter Skjolden

          Could you please explain what you mean by "Relational Database" .... Is it an associative database you are thinking about?

          • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
            Jonathan Poole

            sure , when you go to load the text file, just switch the delimitter. A pipe is #127 in the ascii list of characters:

             

            Untitled.png

            • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
              Petter Skjolden

              This should work for you:

              SchemaFile:

              MAPPING LOAD

                  '@' & FieldNo AS DataFileField,

                  FieldName AS FinalFieldName

              // FROM SchemaFile.TXT (delimiter is '|');

              INLINE [

              FieldNo|FieldName

              1|TableName

              2|Attribute

              3|Value

              4|Category

              ] (delimiter is '|');

               

               

               

               

              DataFile:

              LOAD

                  *

              // FROM DataFile.TXT (delimiter is '|' , no labels);

              INLINE [

              T1|color|red|a

              T1|diameter|10 cm|b

              T1|weight|100 g|c

              T2|Color|black|d

              T2|height|16 cm|e

              T3|length|20 cm|f

              T3|weight|500 g|g

              T3|width|10 cm|h

              ] (delimiter is '|', no labels);

               

               

              RENAME FIELDS USING SchemaFile;

               

              2015-05-21 #1.PNG

                • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
                  Petter Skjolden

                  I didn't explain much - just ask if anything is unclear...

                    • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
                      Jes Lee

                      Thank you.  I will try this and let you know if I get stuck and if I'm successful!

                        • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
                          Petter Skjolden

                          The comments // is what you should remove so you load from your files instead and then comment out lines 7-13 and 20-29 ...

                            • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
                              Jes Lee

                              Oh, I don't have to manually input the schema?   Whew.

                                • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
                                  Petter Skjolden

                                  No not at all ... you just take away what I mentioned earlier... delete the lines 7-13 and 20-29 - as you see them in the screenshot above. Then you remove the two slashes // that comments the FROM lines and make sure the refer to your filenames.

                                      • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
                                        Jes Lee

                                        I'm really new to Qlik, but you have made it easier for me!  Thank you~

                                         

                                        I was wondering if you knew much about the map application.  I've searched on the community, but no one is talking about how to prepare the data to use in maps.  Please let me know if you can help.

                                         

                                         

                                        Cheers!

                                          • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
                                            Petter Skjolden

                                            Actually I am still a little bit blank in that area - so I have to learn a bit myself. But there should be plenty of other community experts that are willing to help you. If you are happy with the answers on this question you should close it and mark it as answered. Then you could post a new question regarding Qlik Sense maps.

                                             

                                            Have a nice evening or whatever it is where you are right now

                                              • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
                                                Jes Lee

                                                I keep getting this error message.

                                                 

                                                The schema file heading is as follows:  Ordinal|GUID|KeyItem|SNLxl KeyField|ProductCaption|DataType|Magnitude|Length

                                                 

                                                Do I need to modify the code? 

                                                 

                                                 

                                                Started loading data

                                                execute cmd.exe /C powershell (New-Object System.Net.WebClient).DownloadFile('ftp://fjcapital:pass4fjcapital@datafeeds.snl.com/110866/BanksUS/Regulatory/BankRegulatoryNEW_20150515_1.zip','R:\SNL_DATABASE\FTP_ZIP\BankRegulatoryNEW_20150515_1.zippass4fjcapital@datafeeds.snl.com/110866/BanksUS/Regulatory/BankRegulatoryNEW_20150515_1.zip','R:\SNL_DATABASE\FTP_ZIP\BankRegulatoryNEW_20150515_1.zip')

                                                Done execute cmd.exe /C unzip -o R:\SNL_DATABASE\FTP_ZIP\BankRegulatoryNEW_20150515_1.ZIP -d R:\SNL_DATABASE\SNL Done

                                                The following error occurred:

                                                Unknown statement

                                                The error occurred here:

                                                Ordinal|GUID|KeyItem|SNLxl KeyField|ProductCaption|DataType|Magnitude|Length SchemaFile: MAPPING LOAD '@' & FieldNo AS DataFileField, FieldName AS FinalFieldName FROM [lib://SNL/BankRegulatoryNEW_BHC_BasicFinl1_1_SCHEMA.txt] (delimiter is '|')

                                                Data has not been loaded. Please correct the error and try loading again.

                                                  • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
                                                    Petter Skjolden

                                                    It would be a little bit of a miracle if it worked without any modification

                                                     

                                                    If you can share the Schema-file and a few lines of the Data-file - it would be easier for us to help you out.

                                                      • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
                                                        Jes Lee

                                                        I can send you a part of the data, but since it's proprietary would it be alright if I sent it via message? 

                                                          • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
                                                            Petter Skjolden

                                                            no problem - but you could also just change some of the data ... I only need a few lines of data ... 4-5 should be enough

                                                              • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
                                                                Jes Lee

                                                                Hi Peter,

                                                                 

                                                                I'm not able to send you messages, but you're right, I changed a few lines of data here....   

                                                                 

                                                                Schema file: 

                                                                Ordinal|GUID|KeyItem|KeyField|ProductCaption|DataType|Magnitude|Length

                                                                0|21E730A3-6C4B-488D-8EEE-232EF1A71E7C|||Operation|char||1

                                                                1|C515F3CC-C826-4C89-82F1-652D9D1EA83E|8|125053|DataSource Period|char||6

                                                                2|BD8F30A6-C2DA-42CA-AE3C-F4522A2B9A94|0|201128|Key|varchar||14

                                                                3|5735306B-8BA6-4498-9D9C-177854204753|1425|205112| Number|bigint||

                                                                4|E42535F6-5791-4A56-915E-01E311621518|1534|205113|OldNumber|bigint||

                                                                5|5FCCCB11-AD6C-4CF0-A5C4-3B2A829431C7|855|205130|PInstitution Key|bigint||

                                                                6|8C7B135A-624A-45EF-BC51-C8F408366F6A|42418|205175|ParentID|bigint||

                                                                 

                                                                 

                                                                 

                                                                Data file:

                                                                I|MRQ|1003|1883693|11897|||03/31/2015 12:00:00 AM|30371794|16.79|15160607|18.82|49.92|21160975|0.26|71.64|4715|74843|1.00|9.25|2.47|0.23|2.25|2.08|2.85|2.43|2.40|65.72|3357161|2883001|11.05|9.83|13.07|14.39|9.74|38.38|5.86|16.37|2.48|0.10|23.32|0.00|48.13|46.42|2.60|0.46|1.67|0.88|0.17|6.55|11.45|76.01|5.73|6.82||1.43|0.69|0.50|0.99|0.94|1.30|131.19|-0.22|3.30|25.80|111.47|6.82|3.49|1.9926|2.6707|0.24|0.39|0.27|0.26|559768|2106174|0|21174|2642|628015|9166491|9794506|12069|0|12069|501889|14658718|197686||197686|14461032|708094|250804|45167|100809||377780|208337|586117|1245365|1386570|19774405|21160975||||66320|897663|963983|217896|3794868||348030|0|495790|26981542||0|4|959650|2576953|90303|-269749|30371794|26981542|14962921||||||24723|43066|67789|60724|128513||128513|1132|1419|2|43187|5206|48395|2510|3|2597|184569|2992|3187|5926|12105||12105|121|2452|2165|0|16843|167726|276|276|25886|21684|22919|3771|4634|3128|11533|0|-2300|0|0|312|312|20640|732|997|52555|84469|154958|0|4235|4235|127925|19112|0|1090|1090|65038|66128|213165|113478|38384||75094|0|170682|119110|4556|1664|20640|732||2198|15953||||||||15057||16556|5748|||2501|3301||4723|12083|1981||||||||21079|245|242|1353|0|22919|0|0|||||0|376|0|0|-2426|0|0|0|0|0|0||1230|||||||||||||||||||||||||||||1.01|9.28|8.99|9.02|72.69|65.13|108.53|1.71|0.00|601|51.35|2.47|2.29|31.75|32.33|63.49|0.65|15.85|24.03|23.17|1.30|0.15|0.58|1.24|111.66|7.98|4.57|61.70|69.67|4.75|17.53|20.42|22.98|0.08|0.45|0.26|72.61|6441.53|3215.40|4488.01|9715178|9220.000|2350012|4628749|6978761|7896039.00|14874800|685342.000|2358963.00|27643503|29867322|19998968.00|0.00|19998968|1070570.00|3382062.00|24799607||3237423.00|3329670|0|3337049|4800639|1462041|1170645|80551|144084|26172349|4.70|4.20|0.06|15186025|29960728|27781450|1386570|16084159|1442675|1442675|19718300|19774405|24881286|5324777|6201526|4815271|105133|150684|286737|76862|147216|150300|523|2946095|1211542|2422599|1386570|9642399|9817890|326643|322684|3357161|2946095|0|253776|179203|259533|216383|216383|325640|46116|||||||||||||||||||||||||||||||||||||||0|0|0|0|33546|0|0|33546|33546|||0|0|0|0|0|0|0|0|0|35253|0|0|35253|35253|||0|0|0|0|0|70039|3323585|0|3393624|3193872|0|149034|3342906|6736530|||2157985|0|0|0|0|73221|3411747|0|3484968|3232602|0|160030|3392632|6877600|||2164842|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|70039|3323585|0|3393624|3227418|0|149034|3376452|6770076|2157985|0|2157985|8928061|0|0|0|0|0|0|0|0|0|0|0|7217546|451255|0|0||0|0|0|0|13923|0|0|0|0|0|0|0|0|0|451255|0|0||0|0|0|0|13923|0|0|0|0|0|0|0|0|0|||||||0|0|0|0|0|0||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|78404|21601|0|0|0|0|0|0|21601|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|60915|5031|0|0|0|0|0|7350|12381||||0|501889|0|552121|200615|10577089|0|0|0|0|0|217896|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|6072|0|0|0|21369|0|0|27441|0|0|0|0|0|0|0|9146646|0|495019|0|134604|552121|0|10328390|0|217896|0|0|0|0|217896|13773|0|6870|0|0|0|200615|221258|0|0|0|0|0|0|0|175051|0|0|0|0|0|0|0|0|0|0|0|175051|0|0|0|33286|9.74|33091|3390252|75094|251|0|0|0|0|0|0|0|0|0|0|0|0|0|26283|54164|451255|13923|0|0|0|0||0|0|6496|0|26283|54164|451255|13923|0|0|0|0|0|0|0|0|0|0|0|0||0|0|6496|155973|0|155973|708094|708094|0|0|0|0|0|217896|217896|0|0|0|0|0|217896|217896|0|0|0|0||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||0|0|0|0|||0|0|0|0|0|0|0||||0|0|0|0|0|0|0|0|0|0|0|0|||||||61657|0|0|0|212685|676229|1245365|294794|30754|954|464082|495790|294060|0|121773|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|

                                                                I|MRQ|1004|1103524|2934|1019122|1118797|09/30/2002 12:00:00 AM|3117445|9.51|2310018|5.63|74.10|2582514|8.76|89.45|63|6994|0.90|8.98|6.12|1.74|4.38|0.85|2.90|4.92|4.41|54.65|317123|259437|10.17|7.08||||0.00|22.95|5.52|2.59|0.41|37.36|0.00|68.83|29.68|0.69|0.06|0.90|0.11|0.28|20.38|12.25|57.97|18.31|11.47|0.00|1.50|1.38|1.03|1.39|1.12|1.87|134.20|0.53|228.82|13.49|126.17|11.47|7.51|4.2800|6.8800|2.30|5.05|2.46|2.00|89470|1491|0|1491|90961|2463|388699|391162|72600|0|72600|0|2310018|43242||43242|2266776|0|46799|239|0|4336|96587|7189|103776|140796|526424|2056090|2582514|0|0|0|0|90672|90672|0|47000|4336|0||29800|2754322|46000|0|1937|184979|97505|34384|-1682|3117445|2754322|2266776||||||||||77771|0|77771|34669|5|788|3319|114|4221|0|324|0|47652|2657|4032|5236|11925|0|11925|257|634|721|0|13537|34115|6924|6924|72|2936|0||||254|0|89|0|||0|91|-146|1|3320|3609|6617|0|0|0|9016|5150|0|300|300|895|8395|22561|11247|4253|0|6994|0|34116|11248|2|0|91|-146|||||||||||||2443|||||||||||||||||||||||||5866|490|304|6052|0|254|0|0|||||0|0|0|||||||||||||||||||||||||||||||0.90|8.98|9.04|9.04|29.33|54.65|59.81|1.16|0.00|39.96|100.91|4.92|4.38|12.37|12.55|46.84|1.39|46.92|51.01|21.99|0.51|0.01|0.00|3.10|126.51|10.15|8.74|72.69|82.84|9.50|4.41|13.92|15.76|0.00|0.08|1.03||5169.89|3830.88|4282.78|394562|78073.000|||||2296305|0.000|3253.00|2772193|3113837|2069452.00|0.00|2069452|70230.00|57567.00|2197249|0|311533.00|309505|0|402132|127797|301726|480192|24256|2141|2710327|1.41|2.38|2.19|2316477|3013669|2775271|526424|1496958|296275|296275|2286239|2056090|2193762|137672|433947|1462558|31982|32221|34865|30683|30922|32221|2644|213347|472913|316368|272605|385703|391229|33808|40732|317123|213347|0|20717|17478|49460|34626|34626|40733|6994|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||90|7099|||||||||||||||||||||||||||||||0|0|0||0||||||||||||||||0|0|0|0||0||||0||0|0||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||13291|29126|0|0|2381|95998|140796||0|3391|26409|29800|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||

                                                                I|MRQ|1005|1958827|12089|||06/30/2010 12:00:00 AM|97206|-16.31|650235|-33.03|66.88|931474|-13.58|69.81|166|6054|-2.43||3.27|2.36|0.91|0.32|3.66|1.00|1.15|28.26|-18645|-19344|-1.92|-1.93|-2.54|2.54|1.94|0.00|34.09|20.14|4.25|1.80|32.87|0.00|93.15|4.83|1.97|0.01|0.05|0.00|0.00|3.85|13.23|6.87|449|38.41|0.00|16.88|17.71|19.76|26.42|19.77|1.87|6.34|8.43|0.00|18.88|93.87|38.41|4.44|4.2127|3.5923|2.38|3.40|2.44|2.35|2726|169133|0|169133|171859|3556|21616|25172|11050|0|11050|1225|649010|12186||12186|636824|0|19606|76967|667||0|74|74|28762|35860|895614|931474|0|0|0|0|0|0|0|28919||0|22167|8291|990851||0|5361|18595|-42940|635|296|972206|990851|638049||||||1811|500|6901|727|7628|0|7628|0|236|43|201|22|266|0|4|2|8136|2311|2720|404|5435|0|5435|0|284|0|161|5880|2256|0|0|0|397|0|0|0|0|0|0|171|0|0|1|1|0|-57|9|270|394|791|0|0|0|2297|742|0|13|13|6049|6062|9101|-6054|0||-6054|0|2238|33070|0|16|0|57||138|41||6||152|||||||||||507|||||||892||2129||293|0|0|0|0|0|0|0|114|||||0|0|0|0|0|0|0|0|0|0|0||0|0||||||||||||||||||||||||||||2.43||||8.69|300.03|55.35|0.92|0.00|25.24|74.04|0.99|0.90|2.48|2.59|83.47|1.25|48.83|62.30|3.23|1.31|7.92|0.00|0.00|90.38|12.68|3.69|83.13|95.81|36.80|5.25|39.77|39.02|0.57|0.01|11.28|78.34|5856.66|3917.08|5611.29|25257|11050.000|131058|513346|644404|43014.00|687418|0.000|182224.00|905949|995470|912741.00|0.00|912741|0.00|30209.00|965117||-16124.00|-15707|0|11183|52376|359737|396670|111086|92|1001327|-8.26|-4.08|3.40|650235|972132|8550|35860|64037|357735|357735|573739|895614|946700|51086|3864|474720|115169|192136|192231|109071|191570|192136|95|-18719|386438|123264|35860|24114|2172|3047|3047|-18645|-18719|0|311|29308|138945|109732|109732|3029|-6054|||||||||||||||||||||||||||||||||||||100||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1122|12912|0|14034|2437|0|0|2437|16471|0|0|0|0|0|0|0|1182|13755|0|14937|2473|0|0|2473|17410|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|1122|12912|0|14034|2437|0|0|2437|16471|0|0|0|16471|0|0|0|0|0|0|0|0|0|0|0|34182|||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0||||0|0|0|0|0|21616|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|21616|0|0|0|0|0|0|21616|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|74|-1.94|0|-18645|6054|0|0|0|0|0|0|0|0|0|0|0|0|0||||||||||||||||||||||||||||||||||||||||||||||0||||||||||||||||||||||||||||||||||||0|0|0|0|0|0|0||||0|0|0|0|0|0|0|0|0|0|0|0|||||||2791|0|0|0|3275|8053|28762|14643|423|0|7868|8291|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|

                                                                I|MRQ|108|1076132|6800|100|1070345|03/31/2008 12:00:00 AM|4795974|-5.48|3495332|-2.48|72.88|3219339|-0.28|108.57|1042|5542|0.46|4.68|5.70|2.98|2.72|1.61|2.83|2.96|2.57|64.78|463526|446672|9.66|8.08|11.25|12.38|9.44|123.87|37.51|31.55|1.10|0.10|19.62|0.00|89.87|8.38|1.31|0.01|0.43|0.00|0.00|3.26|8.81|40.72|30.01|20.45|0.00|1.78|1.78|1.49|2.04|1.49|1.29|62.93|0.24|224.25|5.70|112.11|20.45|6.52|4.9810|6.2083|3.54|3.88|3.64|3.55|74763|13335|0|13335|88098|0|821518|821518|8653|0|8653|17544|3477788|45022||45022|3432766|0|110283|9481|1857||79144|4767|83911|221863|104961|3114378|3219339|0|0|0|168000|40816|208816|0|804682||0|61857|37641|4332335|113|0|0|235480|233809|-5763|0|4795974|4332335|340310||||||21810|28571|51|6728|57109|0|57109|0|26|1407|7483|1718|10608|0|55|786|68584|11683|8091|4977|24751|0|24751|1647|8464|0|971|35833|32751|4707|4707|781|7365|0|733|0|0|733|627|917|0|0|4150|4150|419|167|59|4132|11204|19350|0|1229|1229|18528|4422|0|272|272|10802|11074|34024|14599|9057|0|5542|0|33351|15199|351|763|419|16

                                                                  • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
                                                                    Petter Skjolden

                                                                    Actually the datafile is important to get in its entirety since it seems to be quite a few columns... cant you rather attached the modified files instead?

                                                                      • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
                                                                        Jes Lee

                                                                        The file is rather large.  May I provide you with my email address so that you can email me your email address?  I can email it to you.   Thank you for your help on this, and your patience.  jlee@fjcapital.com

                                                                        • Re: Loading a pipe-delimited text file with a separate data file via Qlik Sense
                                                                          Jes Lee

                                                                          Hi Petter,

                                                                           

                                                                           

                                                                          Your code worked!  However, when I use two codes (for different files), I get an error.   I tried slightly changing the reference table name of the second load statement, but Qlik is having a hard time finding it.  However, it does work beautifully when I pull one at a time. 

                                                                           

                                                                          Am I missing something?

                                                                           

                                                                          [SCHEMA]:

                                                                          LOAD

                                                                              [Ordinal],

                                                                              '@' & ([Ordinal]+1) AS %Key,

                                                                              [GUID],

                                                                              [KeyItem],

                                                                              [SNLxl KeyField],

                                                                              [ProductCaption],

                                                                              [DataType],

                                                                              [Magnitude],

                                                                              [Length]

                                                                          FROM [lib://NEW_CBSB_Corporate_1_SCHEMA.txt]

                                                                          (txt, codepage is 1252, embedded labels, delimiter is '|', no quotes, no eof);

                                                                           

                                                                          [MAPPING_SCHEMA]:

                                                                          MAPPING LOAD

                                                                              '@' & (Ordinal+1) AS FieldNum,

                                                                              ProductCaption AS NewFieldName

                                                                          RESIDENT

                                                                              SCHEMA

                                                                          ;

                                                                           

                                                                          [CBSB_CORPORATE]:

                                                                          FIRST 1000

                                                                          LOAD

                                                                              *

                                                                          FROM [lib://NEW_CBSB_Corporate_20150525_1.txt]

                                                                          (txt, utf8, no labels, delimiter is '|', no quotes);

                                                                           

                                                                          RENAME FIELDS USING [MAPPING_SCHEMA];

                                                                                

                                                                           

                                                                           

                                                                          ///////////  ////   Data file 2

                                                                           

                                                                           

                                                                          [SCHEMA1]:

                                                                          LOAD

                                                                              [Ordinal],

                                                                              '@' & ([Ordinal]+1) AS %Key,

                                                                              [GUID],

                                                                              [KeyItem],

                                                                              [SNLxl KeyField],

                                                                              [ProductCaption],

                                                                              [DataType],

                                                                              [Magnitude],

                                                                              [Length]

                                                                          FROM [lib://NEW_CBSB_BasicFinl1_1_SCHEMA.txt]

                                                                          (txt, codepage is 1252, embedded labels, delimiter is '|', no quotes, no eof);

                                                                           

                                                                          [MAPPING_SCHEMA1]:

                                                                          MAPPING LOAD

                                                                              '@' & (Ordinal+1) AS FieldNum,

                                                                              ProductCaption AS NewFieldName

                                                                          RESIDENT

                                                                              SCHEMA1

                                                                          ;

                                                                           

                                                                          [CBSB_BasicFinl1]:

                                                                          FIRST 1000

                                                                          LOAD

                                                                              *

                                                                          FROM [lib://NEW_CBSB_BasicFinl1_20150525_1.txt]

                                                                          (txt, utf8, no labels, delimiter is '|', no quotes);

                                                                           

                                                                          RENAME FIELDS USING [MAPPING_SCHEMA1];