Field length of "real" data of importing Excel with descriptor rows

[ansonwun]

Hi. I am importing an Excel (xls, not xlsx) with the first 10 rows containing some basic, unstructured description of the data/file. The real data, about 20+ columns, start after that. When I read the Excel in, I noticed that cells with length exceeding 256 characters do not seem to be imported - they end up being blank.

If I remove those first 10 rows manually the data imported fine. But I'd rather not do that as I need to bulk import multiple file with the same structure. May I know if there's any work around?

Thanks in advance!

[BenMoss]

When you are configuring an excel file input it's possible to set the important line within the 'options', 7: Start Data Import on Line.

I think if you know that this number of rows is consistent to skip, then set this to that number (say 10, as outlined in your example), then I imagine you won't get the subsequent issue.

Ben

[ansonwun]

Thanks for the prompt reply!

Unfortunately there doesn't seem to be such an option in the config. I believe importing csv gives that option, but seems not for xls. Attached is a screenshot of the config panel.

[LordNeilLord]

Hi @ansonwun,

You can try the sample tool instead which will allow you to skip the first n records in the file:

I'd also suggest that if you are using the wildcard import for bulk importing, you include the filename in the import process...and then use the grouping function in the sample tool (grouped on filename) to make sure it imports correctly.

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

[BenMoss]

Arr,

Sorry, the option also exists for .xlsx files but not .xls, of course if you can convert them then fine but I'm under the assumption you cannot in which case I would use @LordNeilLord's method and understand whether records are still truncating or not.

Ben

[BenMoss]

---

Sorry, the option also exists for .xlsx files but not .xls, of course if you can convert them then fine but I'm under the assumption you cannot in which case I would use @LordNeilLord's method and understand whether records are still truncating or not.

Ben

[ansonwun]

Thanks both for the prompt reply.

I tried the Sample Tool but the imported cell still appears to be blank. In fact I dropped the Browse tool right after the Input Data tool, and saw that the cell is already blanked out...

[LordNeilLord]

Hey @ansonwun

If you can attach a sample sheet then we can look closer for you and find a solution

Neil

[ansonwun]

Hi, thanks again. I'm attaching a sample of the xls file. After importing, the last 2 cells of the second row are blank - I just read it in from the standard Input Tool.

[LordNeilLord · Accepted solution]

Hey @ansonwun,

Try this configuration:

I'm not sure why it works but it does!

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome