Google Sheet - How to override Matillion's column typecasting?

Matillion ETL
1

We are loading a Google Sheet from a client that has been shared to us.

A column in the data (Invoice Number) contains both numeric and alpha values.

The alpha values are after the first 100 rows.

Matillion is reading the metadata of the sheet and assigning a type of Integer to Invoice Number.

0694G00000RrvC5QAJ_0D54G00009HtKvOSAV
0694G00000RrvC5QAJ_0D54G00009HtKvOSAV534×431 28.5 KB

How do we override the type Integer and assign a type string?

 

Things I have tried:

  1. I have tried to typecast in the SQL (Advanced mode) but can’t seem to get this to work.
  2. I have tried creating a “static” stage table with a varchar Invoice Number column and turning off the ‘Recreate Target Table’ option in the Google Sheet component.
  3. I have tried setting some Connection Options in the Google Sheet component to affect the API call (valuerenderoption).

 

Any suggestions? Or is this an issue with the Google Sheet itself? Does the client need to set the column to “Text” explicitely?

 

Thanks

2

SOLVED:

 

Using a larger RowScanDepth in Connection Options has forced Matillion to compare more rows in the source data before assigning data types.

 

0694G00000RrvIrQAJ_0D74G000008yqs5SAA
0694G00000RrvIrQAJ_0D74G000008yqs5SAA957×531 23 KB

 

0694G00000RrvIwQAJ_0D74G000008yqs5SAA
0694G00000RrvIwQAJ_0D74G000008yqs5SAA721×166 6 KB

3

Hi,

 

Is there a solution for the same issue for Data Loader.

I can't find RowScanDepth parameter anywhere.

 

Thanks, B

4

Hi.

I mean here:

069Tm000002boxbIAA_0D7Tm000000UOzNKAW
069Tm000002boxbIAA_0D7Tm000000UOzNKAW831×444 32.9 KB

These are the settings where I would expect the parameter:

069Tm000002bqRVIAY_0D7Tm000000UOzNKAW
069Tm000002bqRVIAY_0D7Tm000000UOzNKAW1314×718 39 KB

 

Thanks, B.

5

Yes, it is the Cloud version. I am new ti Matillion and didn't even know there are old and new (Cloud) versions :)

If I find a solution, I will post it here.

 

All available parameters are hard predefined. These are the available options:

069Tm000002e15tIAA_0D7Tm000000UwL7KAK
069Tm000002e15tIAA_0D7Tm000000UwL7KAK231×240 4.26 KB

069Tm000002e1AjIAI_0D7Tm000000UwL7KAK
069Tm000002e1AjIAI_0D7Tm000000UwL7KAK209×235 4.78 KB

069Tm000002e1FZIAY_0D7Tm000000UwL7KAK
069Tm000002e1FZIAY_0D7Tm000000UwL7KAK188×141 2.36 KB

6

I have contacted Matillion support, and they have added the missing parameter in Cloud Data Loader.

069Tm000002p5KfIAI_0D7Tm000000XX8TKAW
069Tm000002p5KfIAI_0D7Tm000000XX8TKAW900×326 15.5 KB

7

Hi there @SB699925​!

Thanks so much for sharing how you solved this, this will be super helpful to anyone else in the community who has the same question as you in the future.

Thanks again!

Claire

8

Hi - which component do you mean?

Data Transfer / Database Query?

 

Not sure what you mean by Data Loader, sorry!

9

Ok I see, it looks like you are on the Cloud version. We are still in the Matillion ETL, non-cloud. They are quite different!

 

I'm not familiar with the Data Loader in that case - but if you discover the answer please let me know!

10

I see, in that case if the help documentation for the Data Loader doesn't mention it then maybe you should raise a ticket with Matillion and have them respond directly?

All the best!

11

What if you enter the RowScanDepth as a connection parameter? Does that work? Can you enter free-text in the Parameter list or is that a hard-coded drop-down?