Problem to format numeric data from Txt file

J_Bonnouvrier1

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

EXEMPLE-IMPORT.txt

brady_brady

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. I've attached a sample file; if you place it in your Downloads folder the script will hopefully run without modifications.

</CODE></P> <PRE><CODE class=" language-jsl">Names Default To Here( 1 ); //set these to reflect your path and file name 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

 

 

 

 

example.txt