-1

I hope you can help me out on this:

Issue now is that the imput changed and I need to use the content of row #1 as headers instead of the initial ones. My current imput looks like this: 

       header1      Header2    Header3     Header4    
       Date         Ldap1      Ldap2       Ldap3       
       2020-04-01   Shift A    Shift B     Shift C 
       2020-04-02   Shift A    Shift B     Shift C
       2020-04-03   Shift A    Shift B     Shift C

I need to drop the headers1,2,3,4 and select Date, Ldap1, Ldap2, Ldap3 as the new ones.

I am working with standard SQL but could not find anything till now in the references.

Anyone has a idea? Desirable ouput looks like this:

       Date         Ldap1      Ldap2       Ldap3       
       2020-04-01   Shift A    Shift B     Shift C 
       2020-04-02   Shift A    Shift B     Shift C
       2020-04-03   Shift A    Shift B     Shift C

Important:please, assume that the source or imput cannot be altered meaning that the headers1, headers2 etc will always come as the imput.

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
Diego Arnaldo Penayo
  • 11
  • 4

1 Answers1

1

I feel that your input is CSV file in GCS and you are loading it into BigQuery table

So, you just need to set "Header rows to skip" which is number of rows at the top of a CSV file that BigQuery will skip when loading the data - you can easily do this in BigQuery UI

enter image description here

Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230
  • Hi Mikhail, thanks for the reply. Actually I am not using GCS so I do not have this option. I need to do it within the code in standard SQL so I can unpivot them correctly. I am also investigating this Thanks again! – Diego Arnaldo Penayo May 10 '20 at 18:15
  • that's strange! so how you ended up with such a non-sense table - it is 100% looks like was loaded from somewhere w/o handling properly headers! – Mikhail Berlyant May 10 '20 at 18:18
  • Ok. so my answer is still relevant for source as CSV from Drive. you can treet you Spreadsheet as such OR if you want to treat it as spreadsheet - just properly set linked ranges – Mikhail Berlyant May 10 '20 at 18:27
  • Ok the imputs come from a google sheets file which I load into a table. When I load them I had to write the name of column, data type and column letter like: `( "header1","STRING","A") `. Issue is that the content of "header1" can change (from header1 to header2 or 3 etc) so I've added a row above "header1", A1, where I wrote the formula ROW() which gives the cells' row number. By doing this I can make the loading process "dynamic" as the imput: `( "1","STRING","A") will never change as A1 in google sheets will always have the row() as 1. Challenge is to consider cell A2 as header, not A1 – Diego Arnaldo Penayo May 10 '20 at 18:31
  • sorry, i consider myself done here - i answered thus far as I could. The rest is for you to figure out – Mikhail Berlyant May 10 '20 at 18:35
  • You were helpfull actually. Thanks. I' ll try to skip the first row using standard SQL within the query (similar to the approach you've recomended). I will acknowledge your answer once this shows to be effective for this case. – Diego Arnaldo Penayo May 10 '20 at 18:40
  • Hi just an update. I fixed this by transposing the imput table so as headers I get the dates which do not change of position making my loading process always successfull. Will think harder before coming here the next time :D. Thanks anyways – Diego Arnaldo Penayo May 12 '20 at 17:34