12335_en-US

liliconcom

Impossible to import from excel with excel wizard JMP 12 for Mac<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

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.

Open(

    "/Users/......../2015_BUDGET_REALISATION.xlsx",

    Worksheets( "REALDATA" ),

    Use for all sheets( 0 ),

    Concatenate Worksheets( 0 ),

    Create Concatenation Column( 0 ),

    Worksheet Settings(

        1,

        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

Re: Impossible to import from excel with excel wizard JMP 12 for Mac

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...

Re: Impossible to import from excel with excel wizard JMP 12 for Mac

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.

Brian

Re: Impossible to import from excel with excel wizard JMP 12 for Mac

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

Re: Impossible to import from excel with excel wizard JMP 12 for Mac

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.

Brian Corcoran

JMP Development

Re: Impossible to import from excel with excel wizard JMP 12 for Mac

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...

Re: Impossible to import from excel with excel wizard JMP 12 for Mac

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.

Re: Impossible to import from excel with excel wizard JMP 12 for Mac

Just checked, as I see the data is homogen.

Does these settings effect anything about my problems?

Re: Impossible to import from excel with excel wizard JMP 12 for Mac

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.

Brian Corcoran

JMP Development

Re: Impossible to import from excel with excel wizard JMP 12 for Mac

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...