TXT file import issue

Question

Dear JMP users,

I am facing a problem while importing a TXT file. I am using the Following jsl instruction:

Open("U:\Lib\PRODUCTION\Report_Prod.txt");

I had no problem until a couple of weeks when it came that some data were missing. I could not understand why as data were visible in the file when importing the .txt file into Excel.

Today, I finally tried to import using "Load Text File" jsl instruction and I could see abnormal characters into the log (see capture enclosed). It seems that JMP is facing some import issue that Excel does not see.

Do you have any solution to propose? Many thanks in advance!

Jerome

Capture_IMPORT_TXT.JPG

Craige_Hales · Answer

Do the odd characters occur every time? Are they at the end? Or are they in the middle?

The picture shows the odd characters interrupting a value

.
interrupted in middle of a date

Does the rest of the interrupted value follow the odd characters?

How does excel show that value and the one(s) that should follow it?

How big does Windows think the file is, and how big should it be?

Here's a JSL snippet that you can adjust to show the data before and after the odd characters happen:

loadtextfile("$sample_data/../import data/bigclass.txt",
	blob(readOffsetFromBegin(200), // start 200 bytes into the file
	readLength(10))); // read 10 bytes
// Char To Blob( "6~0967~0D~0AJOHN", "ascii~hex" )

Change the 10 byte length to 100 and change the 200 to capture 50 bytes before and after the change.

The readOffsetFromBegin and readLength values that you use might help answer the question too.

Does the odd data always begin at the same position?

I suspect something changed in the process that creates the file. Using Notepad to view the file will tell if JMP is the only program having trouble reading it. The diamond character with the question mark is the Unicode Replacement Character and is either (1) the actual character in the file or (2) the character JMP is using to display an invalid byte sequence in the file. The hex data from the loadtextfile example will help answer that question.

What sort of machine or process makes the file? How is the file transferred to the computer JMP is using? Is there a possibility the process is interrupted before a file transfer is completed?

txnelson · Answer

* Is the picture you attached in your first entry, the complete file?  If not, does the pattern of "strange, unprintable characters" repeat throughout the file?
* Have you tried printing out the Load Text File() variable in Hex to determine what the characters are?
* What does the opened Excel spreadsheet look like in comparison to what JMP reads in?

Since you can read in the fxt file using Load Text File.....if we can get more information about the special characters, we may be able to parse the loaded text string, save it to a temp file and then have JMP read it back in.

J_Bonnouvrier1 · Answer

Thank you for your reply, but actually it does not fully help: Indeed, the file I am trying to open is automatically generated and I am trying to create a jsl script in order to automatically import it into JMP for further analysises…. using XL before would definitively not help, except if I am able to include this operation into jsl.