I think your analysis is correct. I have been having a look at this using V8 and the import engine version of MS Jet that it uses for Excel seems to assume one header row exists and then sets the column values based on what it finds in subsequent rows. I'm not sure how many it checks - 250 comes to mind but that may be wrong. So if I have 10 lines, 5 numbers and 5 text, I get numeric. If there are 11 lines, 5 numbers and 6 text it gives Character fields but skips the numerics (it seems).
Have a look at this post from a much earlier question and version but probably still holds true - I have not tried this myself.
It may help.
The other options seems to involve exporting the Excel sheet to either a fixed width 'report' file and then reading that as a 'normal' report using Monarch. (Printing the Excel worksheet columns to a file as a report would have the same effect.)
Or, if you have V8 Monarch, export the Excel sheet as a delimited file - .csv for example - and then use the exported file as your database rather than the Excel worksheet. V8 should then allow you to set the properties of the import columns to your preferred values.
I hope this is of some help but wonder if others have found alternative ways to get around this issue.
It is also possible that the year 2002 post is subject to more recent updates in the Microsoft knowledge base so a search their could also be useful.
Edit for some typos.
[size="1"][ May 19, 2006, 03:45 PM: Message edited by: Todd Niemi ][/size]
A quick fix I use is to sort the Excel sheet by the offending column descending. This puts the alpha fields at the top and Monarch treats the whole column as text.
Sorry forgot the other bit, before the sort, I convert the Excel columns to text using the formula =Text('Cell',0)in another column and then copy that back to the original column as values. You can accomplish this with a macro if you have to do it frequently.
Alternativley you can use the Text formula in another column of your spreadsheet. Monarch will still tell you that it has found bad data in the first column, but you can ignore that one on the import and bring in the column with the Text formula as your data. YOu can then reposition the columns within the Monarch Table as required.
[size="1"][ July 05, 2005, 09:54 AM: Message edited by: Nigel Winton ][/size]
I'm using Monarch version 8 and I wanted to compare 180 excel sheets to prepare a new report. All the excel sheets with same column name and I would like to open muliple excel files as a multiple database input files.
/b[/quote]I think you would need to export the Excel files (I assume a single worksheet for the data you require) to a report file and then have Monarch open the 180 report files. Or maybe export from Excel to an accumualted data set in an Access file?
Using Monarch 9 you could export to multiple .csv or .tab files or PDF files and then get Monarch to open all those at the same time.