I saved the spreadsheet as a CSV file, and created a new project using the CSV file. All the fields that were previoulsy left blank are now properly populated.
As a bonus, when importing from a CSV file, you are allowed to specify the field type, which you can't do when importing from XLS.
On the other hand, if anyone has suggestions as to how to resolve the problem without having to resort to the CSV file, I'm still interested in your advice.
I have the same problem with some Excel files. The solution I use is convert the column in Excel to text.
To do this, select the column or range of cells in the column and then select "Data" from the menu bar. Select "Text to Columns" option. Click on Next twice and on step 3 of the wizard, click on the Text option under Column data format. Then click finish. This will convert the entire column data to text.
I have this scripted since I use this on a daily basis and have multiple spreadsheets. From there I apply my usual Monarch Model and go from there.
Let us know if you have any problems.
You've come across an known problem with mixed types within a field in an Excel database. There have been quite a few discussions here about this challenge.
Fortunately, you've also discovered one of the solutions already!
While there have been some other suggested solution (XL -> Access -> Monarch, being one), I think the CSV solution works as well as anything, perhaps better at times, and is pretty straightforward.
Edit: My turn to be beat to the post. I'd agree that converting the column to text is likely the easiest solution as it doesn't require the file format conversion and is really quick.
I am having similar issues with Monarch/Datapump and Excel. Taking the data directly from XLS is near impossilbe so I thought I would use Datapump to do the conversion to CSV and then have a second Monarch model open the CSV, this is not working as for some reason Monarch will no save the info to the model not to use the first row as column heading/field names so my filters, etc won't work or I get all <NULL> data. Any suggestions? Thanks