BookmarkSubscribe
Choose Language Hide Translation Bar
alex
Community Trekker

Import from Excel yields random missing fields (dots) in JMP

Hi,

All my source data is in Excel and when importing into JMP, either by Opening the file directly or by doing a scripted Open Database(), I have some columns of numbers that show up with missing fields(dots). The data in Excel is a formula which provides a number. I can't find a pattern that would hint to why this is happening (some fields are ok).

Any suggestions or known errors?

I'm using JMP8 and Excel 2010 (have also tried saving in Excel 97 format but it's the same)

My current work around is, from Excel, saving as a CSV.
0 Kudos
3 REPLIES 3
alex
Community Trekker

Re: Import from Excel yields random missing fields (dots) in JMP

putting a =value() around the formulas seems to have fixed it although I don't know why.
0 Kudos
Highlighted
mewing
Community Trekker

Re: Import from Excel yields random missing fields (dots) in JMP

More than likely Excel wasn't saving a number in those cells (for whatever reason - Excel doesn't always make sense), but when you saved as a CSV it dumped all formating and left only numbers. I don't do much importing from Excel to anything, but my default action is to always save as a CSV before importing to any program so that I don't get Excel artifacts.
uday_guntupalli
Community Trekker

Re: Import from Excel yields random missing fields (dots) in JMP

I am having this problem too and I am new to JSL.  I cant figure out why .

I have a column of time stamps as shown below :

Column 1         Column 2

1/1/2011 0:00         5 

1/1/2011 1:00         2

1/1/2011 2:00         1

As I import this and a couple of other columns the JMP Data Table looks like this :

I have a column of time stamps as shown below :

Column 1         Column 2

1/1/2011 0:00         5 

1/1/2011 1:00         2

1/1/2011 2:00         1

     .                         .

     .                         .

     .                         .

While importing the data from the excel file I tried to do the following :

Open(FilePath, Lines To Read(3)) ;

I also tried to delete the empty rows using :

keeprows = Loc Nonmissing( dtSplit << get as matrix( {2, 3, 4} ) );
  dtSplit<<select rows(keeprows)<<invert Row Selection<<delete rows ;

Kindly let me know

Regards

Uday

Best
Uday
0 Kudos