Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

XL query component (redshift)

Wondering if its possible to skip a number of lines at the beginning before loading in data from XL. Lets say we have a header line at line 5 then data records from there onwards. How to skip those first 4 lines ? A supplementary question is this. Let's say I have a workbook with 4 sheets of different data that I want to load into 4 separate tables. What's the best way to do that? Possible to use an iterator maybe?

10 Community Answers

Matillion Agent  

Kalyan Arangam —

Hi Tom,

You may use the “Cell Range” property to control what values are read.
For example, you may set your CellRange as A5:F* to read columns from A to F starting with row 5.

Alternatively, you may set CellRange to A5:* to read the entire sheet from Row 5.

You may read more about this in teh properties section on the component documentation.

https://redshiftsupport.matillion.com/customer/en/portal/articles/2503811-excel-query-component?b_id=8915#header1

Hope that helps.

Best
Kalyan


tom reid —

Any thoughts on my supplementary question about loading different sheets in the dame workbook
into different tables


Matillion Agent  

Kalyan Arangam —

Hi Tom,

Apologies, I forgot about that.
You’ll need to create a component per worksheet on your job.

Hope that helps.

Best
Kalyan


tom reid —

Sorry one more question on this topic. If a cell contains a formula is it possible to get the value of the cell rather than the formula text


Matillion Agent  

Paul Johnson —

Hi Tom,
By default the value is returned, its not possible to get the formula at the moment.

Regards,
Paul


tom reid —

Great, thanks for that


tom reid —

Does the XL query component handle Chinese/Japanese characters contained in cells OK. For example say I have spreadsheet where one of the cells contains something like the following text. Will I be able to load this OK to S3 then to Redshift.

Customer Development - 销售类


Matillion Agent  

Kalyan Arangam —

Hi tom,

I tried a simple excel file with that string and it worked.
Have you run into any issues when doing this yourself?

Best
Kalyan


tom reid —

Yes, although the issue was on the loading of data into S3 side of things. I saved one sheet of a mult-sheet spreadsheet to a text file. To preserve the weird characters in it, I had to save as Unicode which in XL I believe equates to UTF-16, but S3 expects
UTF-8.data. Maybe I was trying to be too clever. I guess what you're saying though is just dump the whole XL to S3 and matillion can handle it from there.


Matillion Agent  

Kalyan Arangam —

Hi Tom,

I created a regular excel file with that string in it. Copied to S3 and let matillion run it and It worked.
Have a go and let us know if you run into issues.

Best
Kalyan

Post Your Community Answer

To add an answer please login