Import Excel into Collection

[daniromero89]

Hello, everyone

I am trying to Add static data to my app by uploading an Excel file. Then, I am creating a collection with that data but I am having problems. I don't know why the collection contains only 5 of the columns with data. (the original Excel has more than 30 columns with information). I have tried deleting most of the columns and uploading the same file with only 8 columns and it works perfectly. The thing is that I need to upload the original file.

Is there something that can be done?

Thank you very much in advance.

[Anonymous]

Go to File>Settings>Advanced Settings. Under 'Preview Features' disable 'Explicit Column Selection'.

This option automatically filters some data based on if it is used in the app. Try that and let me know if that fixes it!

---
If this answered your question, please click "Accept Solution". If this helped, please Thumbs Up.

[daniromero89]

It was already disable. 

Thank you for your reply.

[GarethPrisk]

Are you deleting the static data from the app before the next upload? Otherwise, it will create another table with a numeric suffix, but any code referencing the intended name will still pull from the original file.

Whatv version of Excel is the file?

Does it have any protections or macros? Can you test moving the table as raw data into a new Excel file, formatting as a new table, saving and importing that file into the app?

Static data has worked well for me in the past. Never had issues like this, so I am curious if there's anything non-standard about the source file so we can triage and hopefully make it so that files works as you intend.

[daniromero89]

Thank you for your reply.

Yes, every time I upload the file, I delete the previous version. 

The version is EXCEL for office 365 MSO 16.0.

The file does not have any protection or macros. I already tried moving the table as raw data into a new file but it doesn't work. The only thing that worked was deleting most of the columns. 

[GarethPrisk]

Is this data sensitive? If not, can you send me the file directly and I can try to replicate/resolve.

Otherwise, can you describe the table (column count, data types, row count)? Also, how big is the file (curious)?

[daniromero89]

This is the file I have problems with. I would really appreciate it if you could give a hand checking it. 

[GarethPrisk · Accepted solution]

Found the problem.

It doesn't like the columns that you have named as "#. Question Text" - which I assume you were using to reflect questions/responses, with the question text as the column header. The formatting of those, with the period (.) in it appear to break during the import from Excel.

You'll need to revise those column headers to get the data into the app. You can generalize the column names, and use a second table to map the generic names to the question text, like I have below. Alternatively, questions can/should be normalized into their own table, as a lookup to the response, and a value-pairing of question/response.

Isolated the columns which came into the app, from original table.

Confirmed in test app, using data source.

Renamed the missing columns to a simpler pattern.

Confirmed those columns now appear in app, via static data.

Example table that maps the question text, to the generic column name.

It's funny, because you can declare column headers like that, but I think it's a limitation/bug from Excel.

ClearCollect(
    colTest,
    {
        'abc123': "abc123",
        'abc 123': "abc 123",
        'abc . 123': "abc . 123",
        'abc # 123': "abc # 123"
    }
)

Let me know if you have any other questions.

[daniromero89]

You are totally correct! Thank you very much for your help. I really appreciate it.