How do I change the CSV file delimiter from a comma "," to a carrot "^"?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-03-2016 09:52 AM
Hi. We are exporting data as a CSV (customer requires it) but we can't use a comma as one of the fields is a comments field which is full of commas. I want to use a carrot ^, since this is not a common character.
how do I change or set the delimiter character?
Kris
- Labels:
-
Service Mapping
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-06-2016 11:25 AM
Hi. #1, that is happening right now because of the comma... I see your point, though. We are running a multi-domain setup. That being said, can I have multiple versions based on domains?
#2. I am running it from an Export Set. So basically it just pulls fields from the alm_asset table.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-06-2016 11:43 AM
If you're selecting the CSV format from an export set, it must be able to handle commas in data. Can you check if the "offending" column with commas is enclosed in double quotes? You can simply open the export file in text editor (Notepad on PC) and inspect the field value.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-06-2016 11:58 AM
the column is in double quotes. The column is location. Here is one of the offending ones: "ISMC SK Regina 1 Research Dr, Network Room". That's from the file. I am using HP Connect-It to read the export into our Service Manger system (it's an older system and the WSDL-REST API abilities are turned off due to security). HP Connect-It sees this as two columns "ISMC SK Regina 1 Research Dr" and "Network Room" because of the comma after the Dr. if that makes sense.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-06-2016 12:04 PM
Also, the only ones getting rejected are the ones in double quotes.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
10-06-2016 01:27 PM
OK, Kris, the issue is with the other end, which apparently doesn't conform to the CSV spec and not recognize enclosing double quotes. You have 2 options:
1. Fix the other end to property recognize the enclosing double quotes.
2. Change the delimiter in ServiceNow, as you were originally hoping. You'll need to confirm if this will work on the receiving (other) end first. On the ServiceNow side, you may not be able to use Export Set but create a custom script. These articles might give you some ideas:
Export table records as a comma-separated value text file?
Beyond the Comma — File Export Delimiters.
