Importing Text Files

[#1]

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?

[#2 · Accepted solution]

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.

[#3 · Accepted solution]

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.

 

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

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

 

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): 

 

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. 

[#4 · Accepted solution]

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.