topic Re: Import xls files with Multiple File Import platform in Discussions

Import xls files with Multiple File Import platform

anne_sa — Sat, 10 Jun 2023 23:54:00 GMT

Hello everybody,

I have a set of xls files that I would like to import and concatenate.

I am able to open them one by one correctly, however if I try to use the Multiple File Import platform, I get an empty file.

If I transform them in xlsx (instead of xls) it works as expected.

Is there a specific setting to modify, so that it could fix the problem?

I have found some posts related to issues with the Multiple File Import platform, when the files have quite unusual formats, but it is not my case here (1 worksheet, 1st row = headers...)

Thanks in advance for your help!

Re: Import xls files with Multiple File Import platform

Craige_Hales — Thu, 08 Sep 2022 14:09:03 GMT

It sounds like you might have CSV data, or maybe really old .xls format files, and you are either renaming them to .xlsx or opening them with excel and saving them as .xlsx. Sometimes the wrong extension is used and excel may be able to figure it out.

If the files have a .csv extension (and actually contain comma separated values) then you should be able to use the MFI options directly.

When they open one-by-one correctly, what JMP tool did you use? Can you post the source script (right-click->edit). That will tell a lot about what worked. If the data isn't sensitive, a sample file might help too.

Re: Import xls files with Multiple File Import platform

anne_sa — Thu, 08 Sep 2022 15:14:40 GMT

Thanks for your answer @Craige_Hales.

When I open it using the Excel Import Wizard of JMP it works as expected and here is the corresponding source script:

Open(
	"$DESKTOP/Test JMP Import/Test.xls",
	Worksheets( "Sheet1" ),
	Use for all sheets( 1 ),
	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( 1 ),
		Replicated Spanned Headers( 0 ),
		Suppress Hidden Rows( 1 ),
		Suppress Hidden Columns( 1 ),
		Suppress Empty Columns( 1 ),
		Treat as Hierarchy( 0 ),
		Multiple Series Stack( 0 ),
		Import Cell Colors( 0 ),
		Limit Column Detect( 0 ),
		Column Separator String( "-" )
	)
)

However, it seems that indeed the format file is a bit weird, because I have an Excel warning when I try to open it directly from Excel... And if I try to attach it to this message I have an error as well:

The problem is that I am not the one who created the files at first so I don't know if it is possible to fix that afterwards without having to manually open / save each file :(

Re: Import xls files with Multiple File Import platform

Craige_Hales — Thu, 08 Sep 2022 16:02:25 GMT

Don't give up yet! From a windows file explorer (not JMP) try right-click the file and open_with->notepad or wordpad.

I'm guessing you'll see CSV data in the .xls file and renaming it as .CSV will make it work.

If you have more than a handful of these files to rename, JMP has a FilesInDirectory function to find them and a renameFile(?) function to rename them.

You might also see binary data or some sort of XML data in notepad, paste the front of that data so we can guess again...
(I'm out for the rest of the day. Someone else may have a good idea.)

Re: Import xls files with Multiple File Import platform

anne_sa — Fri, 09 Sep 2022 07:11:43 GMT

The renameFile function seems very promising indeed. I tried with a subset of these files: if I rename them as csv I have all data concatenated in a single column. If I rename as xlsx then the MFI works like a charm but I am not able to open the file in Excel anymore( "invalid format or extension").

If I open a file with Notepad it looks like that (no matter if it is saved as csv, xls or xlsx):

Instead of renaming, copying using the CopyFile function may be a solution to keep a version of the file that can be open in Excel if needed, and use the xlsx version to import in JMP?

Otherwise I found this post with a script to import and combine several Excel files. I tried with a subset of files and it seems ok. I will test with a larger number of files to see if it works as well.

Re: Import xls files with Multiple File Import platform

pauldeen — Fri, 09 Sep 2022 10:27:05 GMT

You found the answer: that is tab (or spaces) seperated CSV data you are looking at. Definetly not excel data file format.