Skip to main content

Qlik

cancel
Turn on suggestions
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Showing results for 
Search instead for 
Did you mean: 
jleefjcapital
Creator II
‎2015-05-21 12:43 PM

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

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

5,651 Views
21 Replies
petter
Partner - Champion III
‎2015-05-21 04:07 PM

In response to jleefjcapital

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.

1,176 Views
jleefjcapital
Creator II
‎2015-05-21 04:56 PM
Author

In response to petter

Thank you!

1,176 Views
0 Likes
jleefjcapital
Creator II
‎2015-05-21 04:58 PM
Author

In response to jleefjcapital

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!

1,180 Views
0 Likes
petter
Partner - Champion III
‎2015-05-21 05:04 PM

In response to jleefjcapital

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

1,180 Views
jleefjcapital
Creator II
‎2015-05-21 05:30 PM
Author

In response to petter

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_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:

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.

1,180 Views
0 Likes
petter
Partner - Champion III
‎2015-05-21 05:51 PM

In response to jleefjcapital

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.

1,180 Views
0 Likes
jleefjcapital
Creator II
‎2015-05-21 06:07 PM
Author

In response to petter

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

1,180 Views
0 Likes
petter
Partner - Champion III
‎2015-05-21 06:23 PM

In response to jleefjcapital

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

1,180 Views
0 Likes
jleefjcapital
Creator II
‎2015-05-21 06:29 PM
Author

In response to petter

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

1,180 Views
0 Likes
petter
Partner - Champion III
‎2015-05-22 01:57 AM

In response to jleefjcapital

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?

1,180 Views
0 Likes
Top