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
Kalyan Arangam —
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.
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.
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.