[IQUG] [iqug] LOAD table is putting white space in first col of every row after first

Mike.Garner at glencore.co.uk Mike.Garner at glencore.co.uk
Thu Dec 14 13:36:37 MST 2017


Credit where credit is due, but let’s see ☺



From: Mumy, Mark [mailto:mark.mumy at sap.com]
Sent: 14 December 2017 20:25
To: Garner, Mike (London - GB); markdmumy at gmail.com
Cc: iqug at dssolutions.com
Subject: Re: [IQUG] [iqug] LOAD table is putting white space in first col of every row after first

I’ll take that.  ☺

Nah, I’ve just “been there done that” before.

Mark

Mark Mumy
Strategic Technology Incubation Group
Customer Innovation and Enterprise Platform |  SAP
M +1 347-820-2136 | E mark.mumy at sap.com<mailto:mark.mumy at sap.com>
My Blogs: https://blogs.sap.com/author/markmumy/

https://sap.na.pgiconnect.com/I825063
Conference tel: 18663127353,,8035340905#

From: "iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org>" <iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org>> on behalf of "Mike.Garner at glencore.co.uk<mailto:Mike.Garner at glencore.co.uk>" <Mike.Garner at glencore.co.uk<mailto:Mike.Garner at glencore.co.uk>>
Date: Thursday, December 14, 2017 at 14:20
To: "markdmumy at gmail.com<mailto:markdmumy at gmail.com>" <markdmumy at gmail.com<mailto:markdmumy at gmail.com>>
Cc: IQ Group <iqug at dssolutions.com<mailto:iqug at dssolutions.com>>
Subject: Re: [IQUG] [iqug] LOAD table is putting white space in first col of every row after first

If that's the case Mark, you are a genius, let's wait and see :-)

Sent from my Motorola 8500x

On 14 Dec 2017, at 20:18, Mark Mumy <markdmumy at gmail.com<mailto:markdmumy at gmail.com>> wrote:
You don’t have a row delimiter.  Well, you do, but it’s not what you think it is.

tu_pct_2nd_lien '|',
tu_pct_heloc '|'
)

The PIPE is your row delimiter.  The character after that, the carriage return, is being picked up as the first character for the next row/field.

This is what I did:
— remove the ‘|’ from each line.  Old syntax, I don’t like it much and it can lead to confusion
— Add this to the top of the SQL file so it picks up the carriage return properly:
set temporary option escape_character='on';
— Add these to the end of the load table:
delimited by  '|'
row delimited by '\x0a'

Rerun:
1> select deal_id from dl_new_collat_strats
 deal_id
 ------------------------------
 03FF4
 04F10

(2 rows affected)
1>
2> select char_length(deal_id) from dl_new_collat_strats
 char_length(dl_new_collat_strats.deal_id)
 -----------------------------------------
                                         5
                                         5

Mark

On Dec 14, 2017, at 14:08, Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>> wrote:

I just cut the file down to two rows to prove that the extra white space is always being introduced after the first row no matter what the first row is.

I even loaded the table with the same row twice and the 2nd row encounters the white space.

03FF4|LOANPERFORMANCE|20171001|NO CURRENT MOD|592000.0|0.0|1.0|||||||||||||||||||0.0||||||||||||||||||||||||||||1|||0|0|0|0|0|0|0|0|0||||||||12/13/2017 12:00:00 AM|12/13/2017 12:00:00 AM|3||||||||||591357.79
|||||||0|0|0|||||||||||||||||cur_mod_cut|||||||||||||||||||||||||||||||||||||||||0.0|0.0|||||0.0|0.0|0.0|||||0.0|||||||||||||||||||||||||||
03FF4|LOANPERFORMANCE|20171001|NO CURRENT MOD|592000.0|0.0|1.0|||||||||||||||||||0.0||||||||||||||||||||||||||||1|||0|0|0|0|0|0|0|0|0||||||||12/13/2017 12:00:00 AM|12/13/2017 12:00:00 AM|3||||||||||591357.79
|||||||0|0|0|||||||||||||||||cur_mod_cut|||||||||||||||||||||||||||||||||||||||||0.0|0.0|||||0.0|0.0|0.0|||||0.0|||||||||||||||||||||||||||



DEVEWD_IQ.dlouie.master.0.1> truncate table dbo.dl_new_collat_strats
(3 rows affected)
DEVEWD_IQ.dlouie.master.0.1> load table dbo.dl_new_collat_strats
DEVEWD_IQ.dlouie.master.0.2> (
DEVEWD_IQ.dlouie.master.0.3> deal_id '|',
DEVEWD_IQ.dlouie.master.0.4> source '|',
DEVEWD_IQ.dlouie.master.0.5> as_of_date '|',
DEVEWD_IQ.dlouie.master.0.6> cluster_id '|',
DEVEWD_IQ.dlouie.master.0.7> orig_bal '|',
DEVEWD_IQ.dlouie.master.0.8> cur_bal '|',
DEVEWD_IQ.dlouie.master.0.9> orig_pct '|',
DEVEWD_IQ.dlouie.master.0.10> cur_pct '|',
DEVEWD_IQ.dlouie.master.0.11> fico '|',
DEVEWD_IQ.dlouie.master.0.12> ltv '|',
DEVEWD_IQ.dlouie.master.0.13> ltv_ge_90 '|',
DEVEWD_IQ.dlouie.master.0.14> fico_le_550 '|',
DEVEWD_IQ.dlouie.master.0.15> pct_2nd_lien '|',
DEVEWD_IQ.dlouie.master.0.16> pct_investor '|',
DEVEWD_IQ.dlouie.master.0.17> pct_full_doc '|',
DEVEWD_IQ.dlouie.master.0.18> hpa '|',
DEVEWD_IQ.dlouie.master.0.19> pct_228_arm '|',
DEVEWD_IQ.dlouie.master.0.20> pct_327_arm '|',
DEVEWD_IQ.dlouie.master.0.21> pct_30y_frm '|',
DEVEWD_IQ.dlouie.master.0.22> pct_15y_frm '|',
DEVEWD_IQ.dlouie.master.0.23> pct_balloon '|',
DEVEWD_IQ.dlouie.master.0.24> pct_io '|',
DEVEWD_IQ.dlouie.master.0.25> io_months '|',
DEVEWD_IQ.dlouie.master.0.26> pct_purchase '|',
DEVEWD_IQ.dlouie.master.0.27> pct_silent_2nd '|',
DEVEWD_IQ.dlouie.master.0.28> num_loans '|',
DEVEWD_IQ.dlouie.master.0.29> cur_wac '|',
DEVEWD_IQ.dlouie.master.0.30> pct_refi '|',
DEVEWD_IQ.dlouie.master.0.31> pct_cashout '|',
DEVEWD_IQ.dlouie.master.0.32> aols '|',
DEVEWD_IQ.dlouie.master.0.33> pct_mh '|',
DEVEWD_IQ.dlouie.master.0.34> pct_single '|',
DEVEWD_IQ.dlouie.master.0.35> pct_multi '|',
DEVEWD_IQ.dlouie.master.0.36> pct_townhouse '|',
DEVEWD_IQ.dlouie.master.0.37> pct_coop '|',
DEVEWD_IQ.dlouie.master.0.38> pct_pud '|',
DEVEWD_IQ.dlouie.master.0.39> pct_condo '|',
DEVEWD_IQ.dlouie.master.0.40> pct_second '|',
DEVEWD_IQ.dlouie.master.0.41> pct_owner '|',
DEVEWD_IQ.dlouie.master.0.42> pct_lowdoc '|',
DEVEWD_IQ.dlouie.master.0.43> pct_no_doc '|',
DEVEWD_IQ.dlouie.master.0.44> pct_with_mi '|',
DEVEWD_IQ.dlouie.master.0.45> avg_mi_depth '|',
DEVEWD_IQ.dlouie.master.0.46> state_1 '|',
DEVEWD_IQ.dlouie.master.0.47> pct_state_1 '|',
DEVEWD_IQ.dlouie.master.0.48> state_2 '|',
DEVEWD_IQ.dlouie.master.0.49> pct_state_2 '|',
DEVEWD_IQ.dlouie.master.0.50> state_3 '|',
DEVEWD_IQ.dlouie.master.0.51> pct_state_3 '|',
DEVEWD_IQ.dlouie.master.0.52> state_4 '|',
DEVEWD_IQ.dlouie.master.0.53> pct_state_4 '|',
DEVEWD_IQ.dlouie.master.0.54> state_5 '|',
DEVEWD_IQ.dlouie.master.0.55> pct_state_5 '|',
DEVEWD_IQ.dlouie.master.0.56> group_num '|',
DEVEWD_IQ.dlouie.master.0.57> comb_ltv '|',
DEVEWD_IQ.dlouie.master.0.58> pct_delinq '|',
DEVEWD_IQ.dlouie.master.0.59> pct_defeased '|',
DEVEWD_IQ.dlouie.master.0.60> pct_spec_serv '|',
DEVEWD_IQ.dlouie.master.0.61> dscr_noi '|',
DEVEWD_IQ.dlouie.master.0.62> dscr_ncf '|',
DEVEWD_IQ.dlouie.master.0.63> pct_dscr_lt_1 '|',
DEVEWD_IQ.dlouie.master.0.64> pct_mature_lt_1y '|',
DEVEWD_IQ.dlouie.master.0.65> pct_mature_lt_2y '|',
DEVEWD_IQ.dlouie.master.0.66> pct_watchlist '|',
DEVEWD_IQ.dlouie.master.0.67> pct_cum_loss '|',
DEVEWD_IQ.dlouie.master.0.68> pct_fha_secure_elig '|',
DEVEWD_IQ.dlouie.master.0.69> pct_loan_mod_elig '|',
DEVEWD_IQ.dlouie.master.0.70> ltv_ge_80 '|',
DEVEWD_IQ.dlouie.master.0.71> comb_ltv_ge_80 '|',
DEVEWD_IQ.dlouie.master.0.72> comb_ltv_ge_90 '|',
DEVEWD_IQ.dlouie.master.0.73> fico_le_650 '|',
DEVEWD_IQ.dlouie.master.0.74> fico_le_600 '|',
DEVEWD_IQ.dlouie.master.0.75> modify_time '|',
DEVEWD_IQ.dlouie.master.0.76> insert_time '|',
DEVEWD_IQ.dlouie.master.0.77> cusip_hash '|',
DEVEWD_IQ.dlouie.master.0.78> cur_wam '|',
DEVEWD_IQ.dlouie.master.0.79> cur_wala '|',
DEVEWD_IQ.dlouie.master.0.80> cpr_1m '|',
DEVEWD_IQ.dlouie.master.0.81> cdr_1m '|',
DEVEWD_IQ.dlouie.master.0.82> pp_none_pct '|',
DEVEWD_IQ.dlouie.master.0.83> pp_1y_pct '|',
DEVEWD_IQ.dlouie.master.0.84> pp_2y_pct '|',
DEVEWD_IQ.dlouie.master.0.85> pp_3y_pct '|',
DEVEWD_IQ.dlouie.master.0.86> pp_5y_pct '|',
DEVEWD_IQ.dlouie.master.0.87> orig_securitized_amt '|',
DEVEWD_IQ.dlouie.master.0.88> pct_agency_refi_elig '|',
DEVEWD_IQ.dlouie.master.0.89> pct_jumbo_refi_elig '|',
DEVEWD_IQ.dlouie.master.0.90> cur_delq_roll '|',
DEVEWD_IQ.dlouie.master.0.91> delq_foreclosure_roll '|',
DEVEWD_IQ.dlouie.master.0.92> loss_severity '|',
DEVEWD_IQ.dlouie.master.0.93> dti '|',
DEVEWD_IQ.dlouie.master.0.94> pct_dscr_lt_1p2 '|',
DEVEWD_IQ.dlouie.master.0.95> pct_full_io '|',
DEVEWD_IQ.dlouie.master.0.96> appraisal_reduction_amt '|',
DEVEWD_IQ.dlouie.master.0.97> wa_negam_limit '|',
DEVEWD_IQ.dlouie.master.0.98> pct_tier_1 '|',
DEVEWD_IQ.dlouie.master.0.99> pct_tier_2 '|',
DEVEWD_IQ.dlouie.master.0.100> pct_tier_3 '|',
DEVEWD_IQ.dlouie.master.0.101> servicer_1 '|',
DEVEWD_IQ.dlouie.master.0.102> pct_servicer_1 '|',
DEVEWD_IQ.dlouie.master.0.103> servicer_2 '|',
DEVEWD_IQ.dlouie.master.0.104> pct_servicer_2 '|',
DEVEWD_IQ.dlouie.master.0.105> servicer_3 '|',
DEVEWD_IQ.dlouie.master.0.106> pct_servicer_3 '|',
DEVEWD_IQ.dlouie.master.0.107> originator_1 '|',
DEVEWD_IQ.dlouie.master.0.108> pct_originator_1 '|',
DEVEWD_IQ.dlouie.master.0.109> originator_2 '|',
DEVEWD_IQ.dlouie.master.0.110> pct_originator_2 '|',
DEVEWD_IQ.dlouie.master.0.111> originator_3 '|',
DEVEWD_IQ.dlouie.master.0.112> pct_originator_3 '|',
DEVEWD_IQ.dlouie.master.0.113> strat_type '|',
DEVEWD_IQ.dlouie.master.0.114> cur_thirty_roll '|',
DEVEWD_IQ.dlouie.master.0.115> thirty_sixty_roll '|',
DEVEWD_IQ.dlouie.master.0.116> fcreo_def_roll '|',
DEVEWD_IQ.dlouie.master.0.117> delq_curr_roll '|',
DEVEWD_IQ.dlouie.master.0.118> life_rate_cap '|',
DEVEWD_IQ.dlouie.master.0.119> life_rate_floor '|',
DEVEWD_IQ.dlouie.master.0.120> periodic_rate_cap '|',
DEVEWD_IQ.dlouie.master.0.121> periodic_rate_floor '|',
DEVEWD_IQ.dlouie.master.0.122> first_reset_cap '|',
DEVEWD_IQ.dlouie.master.0.123> pay_cap '|',
DEVEWD_IQ.dlouie.master.0.124> mos_to_roll '|',
DEVEWD_IQ.dlouie.master.0.125> gross_margin '|',
DEVEWD_IQ.dlouie.master.0.126> net_margin '|',
DEVEWD_IQ.dlouie.master.0.127> reset_index_1 '|',
DEVEWD_IQ.dlouie.master.0.128> pct_reset_index_1 '|',
DEVEWD_IQ.dlouie.master.0.129> reset_index_2 '|',
DEVEWD_IQ.dlouie.master.0.130> pct_reset_index_2 '|',
DEVEWD_IQ.dlouie.master.0.131> reset_freq '|',
DEVEWD_IQ.dlouie.master.0.132> pay_adj_freq '|',
DEVEWD_IQ.dlouie.master.0.133> pct_mod_rate '|',
DEVEWD_IQ.dlouie.master.0.134> pct_mod_freeze '|',
DEVEWD_IQ.dlouie.master.0.135> pct_mod_cap '|',
DEVEWD_IQ.dlouie.master.0.136> pct_mod_forgive '|',
DEVEWD_IQ.dlouie.master.0.137> pct_prin_mod_elig '|',
DEVEWD_IQ.dlouie.master.0.138> pct_prin_mod_haircut '|',
DEVEWD_IQ.dlouie.master.0.139> hpa_adj_cltv '|',
DEVEWD_IQ.dlouie.master.0.140> pct_h4h_elig '|',
DEVEWD_IQ.dlouie.master.0.141> pct_h4h_cut '|',
DEVEWD_IQ.dlouie.master.0.142> pct_mba_delinq '|',
DEVEWD_IQ.dlouie.master.0.143> pipeln_mm '|',
DEVEWD_IQ.dlouie.master.0.144> pct_rate_mod_amt '|',
DEVEWD_IQ.dlouie.master.0.145> pct_term_mod_elig '|',
DEVEWD_IQ.dlouie.master.0.146> pct_term_mod_amt '|',
DEVEWD_IQ.dlouie.master.0.147> tot_loss_severity '|',
DEVEWD_IQ.dlouie.master.0.148> exp_loss_severity '|',
DEVEWD_IQ.dlouie.master.0.149> mod_loss_severity '|',
DEVEWD_IQ.dlouie.master.0.150> vanscore '|',
DEVEWD_IQ.dlouie.master.0.151> vanscore_chg '|',
DEVEWD_IQ.dlouie.master.0.152> tu_dti_v3 '|',
DEVEWD_IQ.dlouie.master.0.153> tu_amscore_v3 '|',
DEVEWD_IQ.dlouie.master.0.154> heloc_max_balance '|',
DEVEWD_IQ.dlouie.master.0.155> sum_other_mtg_lien '|',
DEVEWD_IQ.dlouie.master.0.156> num_dq_6m '|',
DEVEWD_IQ.dlouie.master.0.157> num_accnt_lt30d_3m '|',
DEVEWD_IQ.dlouie.master.0.158> num_inq_6m '|',
DEVEWD_IQ.dlouie.master.0.159> pct_revolve_acct_util '|',
DEVEWD_IQ.dlouie.master.0.160> heloc_balance '|',
DEVEWD_IQ.dlouie.master.0.161> mtg_balance '|',
DEVEWD_IQ.dlouie.master.0.162> mtg_max_balance '|',
DEVEWD_IQ.dlouie.master.0.163> mtg_num_dq_2y '|',
DEVEWD_IQ.dlouie.master.0.164> num_accnt_pastdue '|',
DEVEWD_IQ.dlouie.master.0.165> recent_dq '|',
DEVEWD_IQ.dlouie.master.0.166> age_recent_inq '|',
DEVEWD_IQ.dlouie.master.0.167> sum_listed_mtg_bal '|',
DEVEWD_IQ.dlouie.master.0.168> cltv_tu '|',
DEVEWD_IQ.dlouie.master.0.169> cltv_tu_lp '|',
DEVEWD_IQ.dlouie.master.0.170> tu_implied_fico '|',
DEVEWD_IQ.dlouie.master.0.171> pct_orig_zip_curr '|',
DEVEWD_IQ.dlouie.master.0.172> pct_heloc_util '|',
DEVEWD_IQ.dlouie.master.0.173> implied_fico_3m_chg '|',
DEVEWD_IQ.dlouie.master.0.174> pct_svc_adv_cov '|',
DEVEWD_IQ.dlouie.master.0.175> pct_svc_adv '|',
DEVEWD_IQ.dlouie.master.0.176> cdr_mod '|',
DEVEWD_IQ.dlouie.master.0.177> adj_loss_severity '|',
DEVEWD_IQ.dlouie.master.0.178> tot_mod '|',
DEVEWD_IQ.dlouie.master.0.179> stop_adv_cov '|',
DEVEWD_IQ.dlouie.master.0.180> pct_converted_io '|',
DEVEWD_IQ.dlouie.master.0.181> avg_prin_reduction '|',
DEVEWD_IQ.dlouie.master.0.182> avg_rate_reduction '|',
DEVEWD_IQ.dlouie.master.0.183> pct_prin_mod '|',
DEVEWD_IQ.dlouie.master.0.184> pct_rate_mod '|',
DEVEWD_IQ.dlouie.master.0.185> pct_recap_mod '|',
DEVEWD_IQ.dlouie.master.0.186> mos_since_mod '|',
DEVEWD_IQ.dlouie.master.0.187> mod_payment_chg '|',
DEVEWD_IQ.dlouie.master.0.188> pct_high_payment_chg '|',
DEVEWD_IQ.dlouie.master.0.189> pct_med_payment_chg '|',
DEVEWD_IQ.dlouie.master.0.190> pct_low_payment_chg '|',
DEVEWD_IQ.dlouie.master.0.191> pct_no_payment_chg '|',
DEVEWD_IQ.dlouie.master.0.192> tu_pct_2nd_lien '|',
DEVEWD_IQ.dlouie.master.0.193> tu_pct_heloc '|'
DEVEWD_IQ.dlouie.master.0.194> )
DEVEWD_IQ.dlouie.master.0.195> from "/proj/EMBS/fmg/deeshah/dl_part_2_collat_strats.TXT"
DEVEWD_IQ.dlouie.master.0.196> escapes off
DEVEWD_IQ.dlouie.master.0.197> quotes off
DEVEWD_IQ.dlouie.master.0.198> NOTIFY 5000
(2 rows affected)
DEVEWD_IQ.dlouie.master.0.1> select deal_id from dl_new_collat_strats
deal_id
----------
03FF4
03FF4
(2 rows affected)
DEVEWD_IQ.dlouie.master.0.1>
DEVEWD_IQ.dlouie.master.0.2> select char_length(deal_id) from dl_new_collat_strats
char_length(dl_new_collat_strats.deal_id)
-----------------------------------------
                                        5
                                        6
(2 rows affected)

I can have the developer double check the layout.
-----Original Message-----
From: Ron Watkins [mailto:rwatkins at dssolutions.com]
Sent: Thursday, December 14, 2017 2:30 PM
To: Louie, David <David.Louie at blackrock.com<mailto:David.Louie at blackrock.com>>; iqug at dssolutions.com<mailto:iqug at dssolutions.com>
Subject: RE: [IQUG] [iqug] LOAD table is putting white space in first col of every row after first

If you are loading a 2-line file, it's possible that the file has an extra whitespace character which is not being absorbed by the load command.
I found this happens when a file has a CR/LF pair which causes the line length to be 1 longer than expected.
I would double-check the byte-length of the flat-file and ensure that it matches the column width + delimiter count + trailing delimiter and EOL.
Ron

-----Original Message-----
From: iqug-bounces at iqug.org<mailto:iqug-bounces at iqug.org> [mailto:iqug-bounces at iqug.org] On Behalf Of Louie, David
Sent: Thursday, December 14, 2017 11:57 AM
To: 'iqug at dssolutions.com<mailto:iqug at dssolutions.com>'
Subject: [IQUG] [iqug] LOAD table is putting white space in first col of every row after first

This is a strange issue.   The 2nd row in my flat file which is 5 chars (defined as varchar(10) in the table) is getting prefixed w/ a white space after load table .

No errors in load table and both rows are loaded.

Anyone ever come across this?

Thanks
David



DEVEWD_IQ.dlouie.master.0.198> NOTIFY 5000
(2 rows affected)
DEVEWD_IQ.dlouie.master.0.1> select deal_id from dl_new_collat_strats deal_id
----------
03FF4
04F10
(2 rows affected)
DEVEWD_IQ.dlouie.master.0.1>
DEVEWD_IQ.dlouie.master.0.2> select char_length(deal_id) from dl_new_collat_strats
char_length(dl_new_collat_strats.deal_id)
-----------------------------------------
                                        5
                                        6
(2 rows affected)
2017/12/14 13:50:16  NOTICE                                          Worst error code raised during execution was 0 (SCRIPT_SUCCESS)
admcdba001{dlouie}:/u1/dlouie/collat_backfeed=>



This message may contain information that is confidential or privileged. If you are not the intended recipient, please advise the sender immediately and delete this message. See http://www.blackrock.com/corporate/en-us/compliance/email-disclaimers for further information.  Please refer to http://www.blackrock.com/corporate/en-us/compliance/privacy-policy for more information about BlackRock's Privacy Policy.
For a list of BlackRock's office addresses worldwide, see http://www.blackrock.com/corporate/en-us/about-us/contacts-locations.

(c) 2017 BlackRock, Inc. All rights reserved.



For a list of BlackRock's office addresses worldwide, see http://www.blackrock.com/corporate/en-us/about-us/contacts-locations.

© 2017 BlackRock, Inc. All rights reserved.
_______________________________________________
IQUG mailing list
IQUG at iqug.org<mailto:IQUG at iqug.org>
http://iqug.org/mailman/listinfo/iqug

_______________________________________________
IQUG mailing list
IQUG at iqug.org<mailto:IQUG at iqug.org>
http://iqug.org/mailman/listinfo/iqug

*********************************************************************************

LEGAL DISCLAIMER. The contents of this electronic communication and any attached documents are strictly confidential and they may not be used or disclosed by someone who is not a named recipient.

If you have received this electronic communication in error please notify the sender by replying to this electronic communication inserting the word "misdirected" as the subject and delete this communication from your system.

*********************************************************************************
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://iqug.org/pipermail/iqug/attachments/20171214/11c959a8/attachment-0001.html>


More information about the IQUG mailing list