Whatever I do I can not import data from excel. with JMP 11 without excel wizard the column properties were well recognized of xls format.
After switching to V12 all columns are character to JMP and after changing the column to numeric data gets lost.
Worksheets( "REALDATA" ),
Use for all sheets( 0 ),
Concatenate Worksheets( 0 ),
Create Concatenation Column( 0 ),
Has Column Headers( 1 ),
Number of Rows in Headers( 1 ),
Headers Start on Row( 1 ),
Data Starts on Row( 2 ),
Data Starts on Column( 1 ),
Data Ends on Row( 0 ),
Data Ends on Column( 0 ),
Replicated Spanned Rows( 0 ),
Suppress Hidden Rows( 1 ),
Suppress Hidden Columns( 1 ),
Suppress Empty Columns( 0 ),
Treat as Hierarchy( 0 )
First the Date type variable is a character which causes problems when joining/updating/concatenating with another table with same name column wth date/time properties
Try to write a script to change it to numeric but then data gets lost
Switching to date/time doesnt help
Changing the excel format cell also does not help.
Managing Director is waiting for sales report and I am in trouble
Would a competent let me know his email number and I send him the data table so gives it a check? I really have no idea, did all what my skills let me...
Has the StayDate column been typed as d/m/y within Excel? You can select the entire column by click on the column head (like the "A" or "B" header), and then context-click to do Format Cells. I would then select a format that is very clear as to the day/month order. Then try to import this.
It looks like some dates are interpreted as m/d/y and some as d/m/y, which to me says that the column has mixed types.
The settings that you show shouldn't matter. However, if you have hidden garbage in cells that could. You can select row 100 in the preview and then press the green plus next to "Data ends with row" to see if it makes a difference. The recent blog ("The Wizard is New(and Improved!)" I wrote describes this feature.
Rushing there to get the info.
After checking each cell of 30000 rows and columns it seems the data preview of excel import wizard looks fine. The problem starts when would like to update a mother data table with the imported data table, even though the column formats are same mother does not want to except the child. Data join starts at row 32295...
So are you saying now that the Excel table is coming in what looks like a correct format, but it will not join as you expect? If so, we would really need to see those tables to find out what is happening. You will need to call Tech Support and share the data if possible.
For the first 100 rows seen at data preview yes, now checking the combinations of importing and not importing hidden rows, hidden columns, empty data...etc... Will come back with the results
If you have JMP 12, as it seems you do, I would try "Preview all rows". It will show you any rows that are causing incompatibilities in the preview, saving you the import step.
Thank you for advises, it will be a bad day to check tens of excel importing process with up to 600k rows. I will start with the smallest and till now what I realised that those settings make a big change. Not importing hidden rows e.g. was comfortable now need new scripts to select them delete them sort them etc...