TXT file import issue

[#1]

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

[#2]

If Excel is okay with those control characters, that would lead me to believe that those are Excel-type commands. When you look at those cells in Excel, do you see links to other spreadsheets or macro commands or anything like that?

What if you open in Excel and re-save as TXT from Excel and then bring into JMP with your JSL command. Does that work? If so, there is not much you can do except to only open text files, no non-text stuff is allowed.

[#3]

You might try opening the file in an external editor like Notepad. Delete the junk and re-save the file. The junk is in the middle of a value, or maybe goes to the end of the file; you'll have to decide how to trust what's left.

Or, see if you can get a fresh copy of the file without the junk.

I'm not sure what the file format is, but it appears it might be using | to separate values. JMP is likely trying to read a lot of those odd characters into a single cell. Not sure what excel might be trying to do with them. If the file uses newlines for record delimiters (it looks like it does), the editing step above will be a lot easier!

[#4]

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.

[#5]

1. 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?
2. Have you tried printing out the Load Text File() variable in Hex to determine what the characters are?
3. 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.

[#6]

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?