Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Re: Import from Excel yields random missing fields (dots) in JMP

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

Apr 29, 2011 12:44 AM
(3344 views)

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.

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.

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

Highlighted
##

** **keeprows = Loc Nonmissing**(** dtSplit << get as matrix**(** **{****2**, **3**, **4****}** **)** **)**;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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 :

dtSplit<<select rows(keeprows)<<invert Row Selection<<delete rows ;

Kindly let me know

Regards

Uday

Best

Uday

Uday