topic Re: Keeping comma containing values from being split into columns during CSV import in Discussions

Keeping comma containing values from being split into columns during CSV import

ckerr — Wed, 18 Oct 2017 15:08:07 GMT

Short version: the CSV files I am working which occassionally contain text strings which include a comma.

The JSL I have written to open this table very neatly splits the string at the comma and send the part after the comma into the next data column - exactly where it doesn't go.

Excel will get it right when opening this file.

Is there a way to get JMP / JSL to treat this one comma as just a character and not a delimiter?

Or put another way: Is there a way to adjust the script below so that commas inside quotes will be ignored as potential delimiters?

Particulars:

JMP 13.2.1, 64-bit running on Windows 7.

Excerpts of the csv file:

Opening it as text in Notepad, the offending row contains:

...11:00,Skipped,"Light thrown away, they have another light they will put up",,,,

Note: The double quotes around the test entry are the only ones in the data as best as I can tell.

What Excel shows and I am hoping to get out of JMP:

column 11	column 12
Comment	Pest Findings
Light thrown away, they have another light they will put up

What JMP is giving me instead:

column 11	column 12
Comment	Pest Findings
Light thrown away	they have another light they will put up

Column 12 / Pest findings would normally say something about insects found, mice, etc. and should NOT be showing the back half of the comment. It should be blank on this row.

The piece of script I am using to open the table is as follows:

	Open(
		qfull,
		columns(
			Column( "Company", Character, "Nominal" ),
			Column( "Address", Character, "Nominal" ),
			Column( "City", Character, "Nominal" ),
			Column( "State", Character, "Nominal" ),
			Column( "Zip", Character, "Nominal" ),
			Column( "Area", Character, "Nominal" ),
			Column( "Device Type", Character, "Nominal" ),
			Column( "Device Name", Character, "Nominal" ),
			Column( "Date", Numeric, Informat( "m/d/y h:m" ), "Continuous", Format( "ddmonyyyy", 19 )),
			Column( "Date", Character, "Nominal"),
			Column( "Activity", Character, "Nominal" ),
			Column( "Comment", Character, "Nominal" ),
			Column( "Pest Findings", Character, "Nominal" ),
			Column( "Questions", Character, "Nominal" ),
			Column( "Materials", Character, "Nominal" )
		),
		Import Settings(
			End Of Line( CRLF, CR, LF ),
			End Of Field( Comma, CSV( 1 ) ),
			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( 7 ),
			Data Starts( 8 ),
			Lines To Read( "All" ),
			Year Rule( "20xx" )
		)
	);

Is there a way to adjust this script so that commas inside quotes will be ignored as potential delimiters?

Re: Keeping comma containing values from being split into columns during CSV import

ms — Wed, 18 Oct 2017 15:42:23 GMT

Try with

Strip Quotes( 1 )

Re: Keeping comma containing values from being split into columns during CSV import

ckerr — Wed, 18 Oct 2017 15:57:05 GMT

That's fixed it!

Thank you!

Re: Keeping comma containing values from being split into columns during CSV import

wingman131 — Wed, 31 Jan 2018 14:13:44 GMT

I have a similar problem but not in a scripted context. I'm opening a Triple-S file linked to a CSV data file. Open-ended data is properly quoted per CSV standards, but JMP is still interpreting the commas inside the quoted strings as field separators.

E.g.

123456,1,"Lorem ipsum dolor sit amet, consectetuer...",3,5

Gets parsed into 6 columns instead of the correct 5 columns.

I've played with the various import settings for text files under preferences, but to no effect.

Any thoughts would be appreciated. Thanks!

Re: Keeping comma containing values from being split into columns during CSV import

briancorcoran — Wed, 31 Jan 2018 18:45:31 GMT

This is a bug, JMP should not break apart the quoted portion of the value. Unfortunately, it will be JMP 14.1 before this is fixed.

Brian Corcoran

JMP Development

Re: Keeping comma containing values from being split into columns during CSV import

wingman131 — Wed, 31 Jan 2018 18:53:52 GMT

OK. Thanks for the response.