We are extracting data from a system and looking for the best field delimiter and text qualifier to use? Tried semi-colon and double quotes, but IOM doesn't seem to like semi-colons as a delimiter. Then a comma as the delimiter and double quotes, but IOM doesn't seem to like double quotes as the text qualifier (would include the double quotes in the data).
Of course comma and not text qualifier works, but then we run into issues when data contains a comma.
I tried searching, also couldn't find any user configuration options in regards to file formats within the program.
IOM can use comma delimited and tab delimited files only. Fields that contain commas must be wrapped in double quotes, in a comma delimited file, to avoid the comma in the field being seen as a field delimiter. Due to this, IOM is designed to handle data wrapped in double quotes. The field does not have to contain a comma for IOM to properly handle data wrapped in double quotes, and IOM can handle a file where some fields are wrapped and some are not.
The comma delimiters must be outside the quotes, like this:
"Krizowsky","Andrew","123 Any Street","Charleston","SC","29403"
They cannot be within the quotes, like this:
"Krizowsky,""Andrew,""123 Any Street,""Charleston,""SC,""29403"
If you are receiving a specific error when you attempt to import a comma delimited file with text wrapped in double quotes and it is formatted like my above correct example, please post it so that I can assist you with resolving it.
The "Field Name" column actually has no bearing on the import itself, and we take the text from that row exactly as it appears between the delimiters. That column is there so that you know what data is in each field and can properly map your profile. If we changed it in any way it might not make sense to you anymore, and we don't want to guess at which characters you might or might not need in order to recognize where that field should be mapped. You might be surprised at the format of some header rows I've encountered. So yes, if your header row has double quotes wrapping the text, you will indeed see those quotes in the Field Name, but the quotes will not be imported as data for the actual data rows below the header row.
Please let me know if I can be of further assistance.