Solved: Loading a pipe-delimited text file with a separate... - Page 2 - Qlik Community

jleefjcapital · ‎2015-05-21

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

petter · ‎2015-05-21

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.

jleefjcapital · ‎2015-05-21

Thank you!

jleefjcapital · ‎2015-05-21

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!

petter · ‎2015-05-21

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

jleefjcapital · ‎2015-05-21

I keep getting this error message.

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_2015051...pass4fjcapital@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:

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

petter · ‎2015-05-21

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.

jleefjcapital · ‎2015-05-21

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

petter · ‎2015-05-21

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

jleefjcapital · ‎2015-05-21

Hi Peter,

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

Schema file:

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

petter · ‎2015-05-22

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?

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