Subscribe Bookmark RSS Feed

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

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

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

8781_Screen Shot 2015-05-14 at 15.21.06.png


Try to write a script to change it to numeric but then data gets lost


8782_Screen Shot 2015-05-14 at 15.21.22.png

Switching to date/time doesnt help


8783_Screen Shot 2015-05-14 at 15.21.44.png


Changing the excel format cell also does not help.

Managing Director is waiting for sales report and I am in trouble

9 REPLIES
saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

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

briancorcoran

Joined:

Jun 23, 2011

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

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

Just checked, as I see the data is homogen.

Does these settings effect anything about my problems?

8786_Screen Shot 2015-05-14 at 16.23.13.png

briancorcoran

Joined:

Jun 23, 2011

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.

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

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

8789_Screen Shot 2015-05-14 at 16.38.49.png

briancorcoran

Joined:

Jun 23, 2011

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

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

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

briancorcoran

Joined:

Jun 23, 2011

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

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

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