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

Question

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.

uday_guntupalli · Answer

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

unknown · Answer

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.

alex · Answer

putting a =value() around the formulas seems to have fixed it although I don't know why.