BookmarkSubscribeSubscribe to RSS Feed
ckerr

Community Trekker

Joined:

Sep 3, 2014

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

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 11column 12
CommentPest Findings
Light thrown away, they have another light they will put up 

 

What JMP is giving me instead:

column 11column 12
CommentPest Findings
Light thrown awaythey 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?

2 ACCEPTED SOLUTIONS

Accepted Solutions
ms

Super User

Joined:

Jun 23, 2011

Solution

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

Try with

Strip Quotes( 1 )

 

ckerr

Community Trekker

Joined:

Sep 3, 2014

Solution

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

That's fixed it!

Thank you!

5 REPLIES
ms

Super User

Joined:

Jun 23, 2011

Solution

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

Try with

Strip Quotes( 1 )

 

ckerr

Community Trekker

Joined:

Sep 3, 2014

Solution

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

That's fixed it!

Thank you!

wingman131

New Contributor

Joined:

Jan 31, 2018

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

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!

briancorcoran

Joined:

Jun 23, 2011

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

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

wingman131

New Contributor

Joined:

Jan 31, 2018

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

OK. Thanks for the response.