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?
