google bigquery - Data Quality on date field -


i'm having problems deal date fields on string format, i'm trying sanitize base. default format date string in base "%d/%m/%y", since comes csv file, there undesirable strings. i'm getting errors when try parse with:

parse_date('%d/%m/%y',trim(e.checkin))

like this, every time run new problematic char:

 illegal non-space trailing data '�' in string "25/07/201€"

what best way deal this?

core in addressing issue in identifying rows "broken" checkin field

then, can skip processing them below 

#standardsql e (   select '25/07/201€' checkin union   select '25/07/2016' ) select    checkin checking_string,    parse_date('%d/%m/%y',trim(e.checkin)) checking_date e regexp_contains(e.checkin, r'^(\d){2}/(\d){2}/(\d){4}$')

or can come logic of treating somehow specially broken values

i case, using where regexp_contains(e.checkin, r'^(\d){2}/(\d){2}/(\d){4}$') approach allows control situation

hope gives yo enough directions


Comments

Post a Comment

Popular posts from this blog

Retrieving ETA (estimated time of arrival) with Google Distance Matrix API and public transit as transport mode -

Image
is possible retrieve eta google's distancematrix api (or alternative api). fyi, eta different duration, when using public transport. following example clarify request. when leave on friday @ 8.15 i'll able catch train @ 8.30 , arrive @ 9.00 am. google says duration 30 minutes. however, when leave on saturday @ 8.15 i'll able catch first train @ 9.00 , arrive @ 9.30 am. google still says duration 30 minutes, means ignore delay. google map supports functionality there api around giving me eta instead of duration. can see, distance-matrix' response no good. clarifying question little. eta estimated time of arrival , it's clear user wants know entire duration of transport (including waiting time public transport).
Read more

android - ConstraintLayout: Realign baseline constraint in case if dependent view visibility was set to GONE -

Image
is there way realign baseline constraint textview in case if dependent view visibility set gone? my layout code: <android.support.constraint.constraintlayout android:layout_width="match_parent" android:layout_height="wrap_content"> <textview android:id="@+id/title" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="test title" android:textcolor="@color/black" android:textsize="24sp" app:layout_constraintleft_toleftof="parent" app:layout_constraintright_torightof="parent"/> <textview android:id="@+id/subtitle1" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="subtitle 1" android:textsize="11sp" app:layout_constraintb...
Read more

c# - Populating Gridview inside Listview ItemTemplate On Web User Control from Code Behind -

i have think simple question, might more complex.. i have spent few days looking answer on google , various questions on site cannot seem come right. what trying bind gridview on user control ascx page default.aspx.cs on page load event. user control markup follows: <%@ control language="c#" autoeventwireup="true" codebehind="vulnerabilityexternalip.ascx.cs" inherits="vulnerabilityassesment.controls.vulnerabilityexternalip" %> <asp:table runat="server" cellpadding="1" cellspacing="2"> <asp:tablerow> <asp:tablecell> <asp:label id="lblvulnerabilityname" runat="server" text="vulnerability name:" cssclass="itemname"></asp:label> </asp:tablecell> <asp:tablecell> <asp:label id="lblvulnerabilitynametext" runat="server" text='<%# eval("mainvulnerabilityname")...
Read more