Open/Read fixed width text file in JMP

Question

Hi All,

I have a number of fixed width data files(all in same structure) as txt format those do not have column names. Can anyone suggest a template JSL Script that I can customize/use to open any of the files, assign column names, and save as as JMP table?

Thanks in advance.

jthi · Answer

After you are done with your selections and are able to press Import, press it and you can get the script from Table Script "Source"

Open(
	"$DOWNLOADS/test.csv",
	columns(
		New Column("date",
			Numeric,
			"Continuous",
			Format("m/d/y", 10),
			Input Format("m/d/y")
		),
		New Column("c000002", Numeric, "Continuous", Format("Best", 10)),
		New Column("c000003", Numeric, "Continuous", Format("Best", 10)),
		New Column("c000004", Numeric, "Continuous", Format("Best", 10)),
		New Column("c000005", Numeric, "Continuous", Format("Best", 10)),
		New Column("c000006", Numeric, "Continuous", Format("Best", 10)),
		New Column("c000007", Numeric, "Continuous", Format("Best", 10))
	),
	Import Settings(
		Fixed Column Widths(13, 3, 7, 9, 11, 6, 90),
		Strip Quotes(0),
		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),
		First Named Column(1),
		Data Starts(2),
		Lines To Read("All"),
		Year Rule("20xx")
	)
)

Does have 	"$DOWNLOADS/test.csv", and Fixed Column Widths(13, 3, 7, 9, 11, 6, 90). Are these still missing something?

monir · Answer

Hi jthi,

Thanks for your prompt response. Yes, I tried as you suggested here. This works for the file that I manually opened by choosing each column width. But if I want to use this script for other files, by changing the file name/location of course, it does not import the data though creates the table structure.

Any suggestions?

Thanks

jthi · Answer

Have you tried opening the files using JMP's Open with preview?

Select

find your file, make sure Using Preview is enabled and click Open

You will them get many different Import options you can change to (hopefully) fit your file format

After you are done with your selections and are able to press Import, press it and you can get the script from Table Script "Source"