Subscribe Bookmark RSS Feed

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

ckerr

Community Trekker

Joined:

Sep 3, 2014

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

Try with

Strip Quotes( 1 )

 

ckerr

Community Trekker

Joined:

Sep 3, 2014

Solution

That's fixed it!

Thank you!

2 REPLIES
ms

Super User

Joined:

Jun 23, 2011

Solution

Try with

Strip Quotes( 1 )

 

ckerr

Community Trekker

Joined:

Sep 3, 2014

Solution

That's fixed it!

Thank you!