398367_en-US

liliconcom

Problem to format numeric data from Txt file

Dear Community,

 

I am trying to build a jsl script to properly import data formatted as in the ecnlosed example file. I am facing to challenges:

- The decimal sign is "," instead of ".". By using the default setup of my regional preferences, JMP interprets the "," as a thousand delimiter. How to force JMP properly interpret these number within a jsl script?

- the negative numbers are formatted as follows: 64,000-. Can JMP handle such format.

 

One solution could be to re-format the txt file, but it is an extract from the ERP and the IT team does not understand that I don't manage to handle such file wheras Excel does.

 

Thanks and regards,

 

Jérôme

Automation and ScriptingMass CustomizationRe: Problem to format numeric data from Txt file

Hi,

You might try something like the following; while you mention that IT does not understand your problem, you can use JSL to reformat the file yourself. While there is likely an import setting to handle the commas-as-decimals, I cannot think of it offhand and cannot find it in the documentation. Perhaps someone else will reply with that setting. Until then, see if this does the trick.

 

Names Default To Here( 1 );

path = "$DOWNLOADS/";
origFile = "example.txt";
holdFile = "example_modified.txt";

//get text of original file and replace commas with decimals
txt = Substitute( Load Text File( path || origFile ), ",", "." );

//save resulting text to a file, open this file as a table, then delete the file
Save Text File( path || holdFile, txt );
dt = Open( path || holdFile );
Delete File( path || holdFile );

//create a new column. if the last character is not "-", the string is immediately converted to a number. If the last
//character IS a "-", the string is converted to a number after the "-" is moved to the first character of the string
dt << New Column( "xx", formula( Num( If( Right( :INFO, 1 ) != "-", :INFO, "-" || Left( :INFO, Length( :INFO ) - 1 ) ) ) ) );

//remove the formula, so we can delete the original INFO column and rename our new column to INFO
dt:xx << Delete Formula;
dt << Delete Columns( "INFO" );
dt:xx << set name( "INFO" );

Cheers,

Brady

 

 

 

 

Re: Problem to format numeric data from Txt file

I love this smart solution, I didn't know this way to modify txt files directly. Thank you very much!