Skip to main content
Documentation
Feature Requests Trust Center Status Community

Community

Connector Improvement: Magic Folder Excel - Skip Headers

Daniel Belknap User

Excel files often have header rows, and when loading from a magic folder we often don't have control over the format of the file we're loading. These headers throw off Fivetran and result in missing data because it doesn't read a column without a header. Especially true if these headers include merged cells. Here's an example:

File Header gets loaded with Col 1's data, and Col 6 gets loaded since it extends past Merged Header, but nothing in between. If I could skip the Merged Header row, Fivetran would see the right columns to load. (I'm surprised Col 5 doesn't even get loaded in this example.)

Please sign in to leave a comment.

Comments

4 comments

Date Votes
  • Official comment
    Alison Fivetranner

    Hi Daniel,

    Thank you for submitting this feature request. Can you tell me more about where the Excel files are coming from such that you don't have control of their structure?

    It is true that today we only sync columns that have a column header in the first row see the note at this Reference.

    We are considering options for enhancing our data detection more powerful and flexible within Excel although the sheer number of possible ways data can be structured provides some challenges. The more examples of what data structures are common and in what use cases the better. Would you be willing to share example files?

    Regards

    Alison

  • Daniel Belknap User

    The case I have right now, it's simply that it's a file another team owns. I could ask them to change it, but the same issue will likely happen frequently. This is a "published" sort of file that's a reference to users from across the organization, so the extra header is somewhat meaningful for users coming in to look at the file. The only example

    I've seen files coming from reporting tools that generate headers and footers - the same reasoning as in the csv setup guide. We received some files from a service that included a total row, for example. And while we could update these or deal with them after ingestion, we don't really want to go in and edit the original files - in this case there were dozens of files, which would mean either taking a lot of time manually, or scripting something.

    I don't know if it would be possible on the Schema tab to have options per sheet? So I could skip row 1 on the sheet I screenshot above, but not other sheets? Or if there could be a spot to enter a cell reference where the table starts - "A2" in this example, and then Fivetran would read relative to that cell reference.

  • Alison Fivetranner

    Hi Daniel,

    Thank you for the additional information. I think if you need to tailor skipping rows for headers and footers than perhaps just enabling a 'complex' mode that allows all the configuration options we currently provide for our other file connectors makes sense.

    I do hear your need for us to support for more complex headers in a continuous data set - simple merged rows in a top level feels very common.

    Will add it to our list of ideas to explore

    Alison 

  • Max Jackson User

    I have a similar issue with Daniel. The owner of the file creates header rows and footer rows with some textual information that I do not need. Currently I need to process the files outside of fivetran into CSV, then push it into fivetran either via email or magic folder. In magic Folder, the connector just creates a table of the file and doesn't append to a single table.

Didn’t find what you need?

Contact support