cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar

Importing Text Files

I am importing a text files that uses more than just commas to separate values. Here is the basic function I am using.

 

open("$desktop/jsonfiles/test.json","text")

 

How do I modify the function to identify other characters such as "[" to be used to identify a new column of data?

3 ACCEPTED SOLUTIONS

Accepted Solutions
Craige_Hales
Super User

Re: Importing Text Files

The source script in a successful import using the text import wizard will get you what you need. You can add one character to the list of delimiters like this:

Open(
    "C:\...23a569_1.json",
    Import Settings(
        End Of Line( CRLF, CR, LF ),
        End Of Field( Comma, Other( "[" ), CSV( 0 ) ),
        Strip Quotes( 1 ),
        Use Apostrophe as Quotation Mark( 0 ),
        Use Regional Settings( 0 ),
        Scan Whole File( 1 ),
        Treat empty columns as numeric( 0 ),
        CompressNumericColumns( 0 ),
        CompressCharacterColumns( 0 ),
        CompressAllowListCheck( 0 ),
        Labels( 0 ),
        Column Names Start( 1 ),
        Data Starts( 1 ),
        Lines To Read( "All" ),
        Year Rule( "20xx" )
    )
)

 This will not work for json files in general, but it might work for yours because the rows in the data are on a single line of json text.

Craige

View solution in original post

Re: Importing Text Files

Here are some additional steps for importing .txt files into JMP (in Windows) beyond what was already clarified and communicated by @Craige_Hales 

 

1. Open JMP. 

2. Use Windows Explorer to select "All Files(*.*)" then open as "Data Using Preview"  In this example, I'm using a .txt file version of the Big Class dataset available in the JMP sample data directory.

PatrickGiuliano_0-1654724458527.png 

PatrickGiuliano_1-1654724622562.png

3. Specify delimiters, lines to skip, etc...

PatrickGiuliano_2-1654724683821.png

4. Click Next. Click over the Icons to change the data type. Click the red drop down arrows to change the format.

 

PatrickGiuliano_4-1654724896345.png 

PatrickGiuliano_5-1654724936885.png

5. Click the "Import" button.  JMP will save a source script to the data table (which is stored in the upper left hand corner of the table next to the green play button): 

PatrickGiuliano_6-1654724990093.png 

If you right click to edit the script, it will look something like this:

Open(
	"$DOCUMENTS/Big Class.txt",
	columns(
		New Column( "name", Character, "Nominal" ),
		New Column( "Grade No", Character, "Nominal" ),
		New Column( "weight", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "age", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "sex", Character, "Nominal" ),
		New Column( "height", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "median split on weight",
			Numeric,
			"Continuous",
			Format( "Best", 12 )
		),
		New Column( "sex indicator", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "Grade", Character, "Nominal" )
	),
	Import Settings(
		End Of Line( CRLF, CR, LF ),
		End Of Field( Comma, CSV( 0 ) ),
		Strip Quotes( 1 ),
		Use Apostrophe as Quotation Mark( 0 ),
		Use Regional Settings( 0 ),
		Scan Whole File( 1 ),
		Treat empty columns as numeric( 0 ),
		CompressNumericColumns( 0 ),
		CompressCharacterColumns( 0 ),
		CompressAllowListCheck( 0 ),
		Labels( 1 ),
		Column Names Start( 1 ),
		Data Starts( 2 ),
		Lines To Read( "All" ),
		Year Rule( "20xx" )
	)
)

6. Copy and paste this script into a new script window.

7. Next, parameterize the file name reference and use that code to open similarly formatted files with ease. Alternatively, you can use JMP 16's Enhanced Log to pick off the code used for the import operation and modify it from there. 

PatrickGiuliano_7-1654726589318.png

View solution in original post

Craige_Hales
Super User

Re: Importing Text Files

Also, if you are importing JSON, try the JSON wizard (since JMP 14, I think.) The original question was a workaround for a special type of JSON that won't work for most JSON files.

Craige

View solution in original post

3 REPLIES 3
Craige_Hales
Super User

Re: Importing Text Files

The source script in a successful import using the text import wizard will get you what you need. You can add one character to the list of delimiters like this:

Open(
    "C:\...23a569_1.json",
    Import Settings(
        End Of Line( CRLF, CR, LF ),
        End Of Field( Comma, Other( "[" ), CSV( 0 ) ),
        Strip Quotes( 1 ),
        Use Apostrophe as Quotation Mark( 0 ),
        Use Regional Settings( 0 ),
        Scan Whole File( 1 ),
        Treat empty columns as numeric( 0 ),
        CompressNumericColumns( 0 ),
        CompressCharacterColumns( 0 ),
        CompressAllowListCheck( 0 ),
        Labels( 0 ),
        Column Names Start( 1 ),
        Data Starts( 1 ),
        Lines To Read( "All" ),
        Year Rule( "20xx" )
    )
)

 This will not work for json files in general, but it might work for yours because the rows in the data are on a single line of json text.

Craige

Re: Importing Text Files

Here are some additional steps for importing .txt files into JMP (in Windows) beyond what was already clarified and communicated by @Craige_Hales 

 

1. Open JMP. 

2. Use Windows Explorer to select "All Files(*.*)" then open as "Data Using Preview"  In this example, I'm using a .txt file version of the Big Class dataset available in the JMP sample data directory.

PatrickGiuliano_0-1654724458527.png 

PatrickGiuliano_1-1654724622562.png

3. Specify delimiters, lines to skip, etc...

PatrickGiuliano_2-1654724683821.png

4. Click Next. Click over the Icons to change the data type. Click the red drop down arrows to change the format.

 

PatrickGiuliano_4-1654724896345.png 

PatrickGiuliano_5-1654724936885.png

5. Click the "Import" button.  JMP will save a source script to the data table (which is stored in the upper left hand corner of the table next to the green play button): 

PatrickGiuliano_6-1654724990093.png 

If you right click to edit the script, it will look something like this:

Open(
	"$DOCUMENTS/Big Class.txt",
	columns(
		New Column( "name", Character, "Nominal" ),
		New Column( "Grade No", Character, "Nominal" ),
		New Column( "weight", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "age", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "sex", Character, "Nominal" ),
		New Column( "height", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "median split on weight",
			Numeric,
			"Continuous",
			Format( "Best", 12 )
		),
		New Column( "sex indicator", Numeric, "Continuous", Format( "Best", 12 ) ),
		New Column( "Grade", Character, "Nominal" )
	),
	Import Settings(
		End Of Line( CRLF, CR, LF ),
		End Of Field( Comma, CSV( 0 ) ),
		Strip Quotes( 1 ),
		Use Apostrophe as Quotation Mark( 0 ),
		Use Regional Settings( 0 ),
		Scan Whole File( 1 ),
		Treat empty columns as numeric( 0 ),
		CompressNumericColumns( 0 ),
		CompressCharacterColumns( 0 ),
		CompressAllowListCheck( 0 ),
		Labels( 1 ),
		Column Names Start( 1 ),
		Data Starts( 2 ),
		Lines To Read( "All" ),
		Year Rule( "20xx" )
	)
)

6. Copy and paste this script into a new script window.

7. Next, parameterize the file name reference and use that code to open similarly formatted files with ease. Alternatively, you can use JMP 16's Enhanced Log to pick off the code used for the import operation and modify it from there. 

PatrickGiuliano_7-1654726589318.png

Craige_Hales
Super User

Re: Importing Text Files

Also, if you are importing JSON, try the JSON wizard (since JMP 14, I think.) The original question was a workaround for a special type of JSON that won't work for most JSON files.

Craige