Data connection omits text but connects numbers

I'm trying to connect one excel workbook to another through a data connection. However, when the new sheet is created in the second workbook, some of the columns only show number values (dates, numbers, etc.) but the text (names, locations, etc.) doesn't show up. I've tried messing around with inserting columns and hiding them, but it seems every time I try to duplicate a potential solution, the same result doesn't occur.

I managed to get one workbook to connect with the text and numbers both showing up, so I duplicated the file and created a data connection with that copy, but the text was omitted from the copied file.

The two files were actually identical. I recently refreshed the data connection and now and it's the same case, with no text showing up.
To give a little more background on the issue; one workbook is a "project plan" and the other workbook is a type of dashboard. There will be several individual project plans that I want to consolidate into one dashboard workbook (each project plan will be a new sheet in the dashboard), and the main sheet in the dashboard will have a calculation involving the other sheets. The individual project plans will be updated in their own file, but I want the dashboard with the linked project plans to be able to update as well. Is there a better way to do this than a data connection? I can't really change the format of the project plan to only include either numbers or text in each column.
Hope that information helps.