cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
BSwid
Level IV

Import Multiple Files, Excel - Get the right format result

The import multiple files help and video are very helpful.
https://www.jmp.com/support/help/14-2/import-multiple-files.shtml
https://www.youtube.com/watch?v=-6e7kh1cTvU

 

However, I'm having trouble achieving the same format results importing multiple files vs. importing a single file.  The single file import resulted in the correct format and column properties.   The multiple file import was great, stacking the files into about 4m rows, but the column properties and format were not as I would have liked.

A few thoughts,

Maybe it is the CSV import settings?
Maybe I need to customize an import script?

Maybe the format of my .xlsx files is not exactly the same and it is confusing the JMP import?

 

I've attached three images.

1. The import settings I'm using.

2. How one file simply using open to import turns out (as desired).

3. How the import multiple files... results (not as desired).

The main thing is getting the columns dates read as dates.

 

Thanks.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Craige_Hales
Super User

Re: Import Multiple Files, Excel - Get the right format result

MFI does not currently attempt to guess dates, and there is currently no way to make it pick a date format.

 

MFI was initially built to import CSV files, fast. Fast means not guessing about various formats. guessing dates is slow, and impossible to be certain when ddmmyyyy and yymmdddd are in the mix.

 

During testing, there were a number of requests to make MFI also handle other file types, including .XLS and .JMP. When MFI opens these files, they go through the same post processing mechanism the CSV files use for stacking, which means column type and format are not available and must be rediscovered. (That's what you are observing.) MFI does guess numeric vs character column.

 

https://community.jmp.com/t5/JSL-Cookbook/Concatenate-data-tables/ta-p/82457 is a different approach you might use. I'd expect it to be as fast as MFI for the .XLS files because MFI does not open .XLS files in parallel. (CSV files will load much faster with MFI because MFI uses threading to open more than one CSV at a time.)

 

Craige

View solution in original post

2 REPLIES 2
BSwid
Level IV

Re: Import Multiple Files, Excel - Get the right format result

There's a post that helped me deal with this after import (JSL below) but I'd like to understand how to do it during import.

// Columns to set properties and format
//Sales document		
//Ship date
//Requested Ship Date
//Days Difference GI/ReqShip
//Qty Shipped
//Shipped $
//PO Date
//Item (/BIC/AZCOPA00.S_ORD_ITEM)

Names Default to Here(1);
dt = current data table();

//Quantity and $
column(dt,"Sales Document") 			<< data type(Numeric)	<<Modeling Type(Continuous)		<<Format(Best,12);
column(dt,"Days Difference GI/ReqShip") 	<< data type(Numeric)	<<Modeling Type(Continuous)		<<Format(Best,15);
column(dt,"Qty Shipped") 			<< data type(Numeric)	<<Modeling Type(Continuous)		<<Format(Best,15);
column(dt,"Shipped $") 			<< data type(Numeric)	<<Modeling Type(Continuous)		<<Format(Currency, USD, 24,2);
column(dt,"Item (/BIC/AZCOPA00.S_ORD_ITEM)")	<< data type(Numeric)	<<Modeling Type(Continuous)		<<Format(Best,15);


//dates - this worked
//https://community.jmp.com/t5/Discussions/Changing-character-column-to-date-colum/m-p/19048?advanced=false&collapse_discussion=true&filter=location&location=forum-board:discussions&q=JSL%20script%20to%20format%20correct%20data%20to%20dates&search_type=thread
column(dt,"Ship Date")			<< data type(numeric) 	<<Modeling Type(Continuous)		<< format("locale Date");
column(dt,"Requested Ship Date")	<< data type(numeric) 	<<Modeling Type(Continuous)		<< format("locale Date");
column(dt,"PO Date")			<< data type(numeric) 	<<Modeling Type(Continuous)		<< format("locale Date");


//dates - this didn't work - ended up with cell values like 3606681600 instead of a date
/*
column(dt,"Ship Date") 	<< data type(Numeric)	<<Modeling Type(Continuous)		<<Format(m/d/y,12);  //input format(m/d/y)
column(dt,"Requested Ship Date") 	<< data type(Numeric)	<<Modeling Type(Continuous)		<<Format(m/d/y,12);  //input format(m/d/y)
column(dt,"PO Date") 	 	<< data type(Numeric)	<<Modeling Type(Continuous)		<<Format(m/d/y,12);  //input format(m/d/y)
*/
Craige_Hales
Super User

Re: Import Multiple Files, Excel - Get the right format result

MFI does not currently attempt to guess dates, and there is currently no way to make it pick a date format.

 

MFI was initially built to import CSV files, fast. Fast means not guessing about various formats. guessing dates is slow, and impossible to be certain when ddmmyyyy and yymmdddd are in the mix.

 

During testing, there were a number of requests to make MFI also handle other file types, including .XLS and .JMP. When MFI opens these files, they go through the same post processing mechanism the CSV files use for stacking, which means column type and format are not available and must be rediscovered. (That's what you are observing.) MFI does guess numeric vs character column.

 

https://community.jmp.com/t5/JSL-Cookbook/Concatenate-data-tables/ta-p/82457 is a different approach you might use. I'd expect it to be as fast as MFI for the .XLS files because MFI does not open .XLS files in parallel. (CSV files will load much faster with MFI because MFI uses threading to open more than one CSV at a time.)

 

Craige