Hi,

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.

Hi Andrew,

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.

Thanks!

Tiffany

Hi Tiffany,

Thanks for the response. We are trying tab delimited now and seeing how that goes.

Not sure why, but when I originally used the comma and quotes option when generating the import profile the quotes were being shown in the Field Name section and I assumed it would be including them in any data import. So thought it wasn't an option.

Hi Andrew,

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.

Thanks!

Tiffany

Ah I was miss-lead / confused about the quotes being included, I've gotten them to revert to a comma separated file with double quote text delimiter.

Hi Tiffany,

Am I correct in saying based on your above example that the text within the double quotes can contain comma's?:

"Krizowsky","Andrew","123 Any Street, Some District","Charleston","SC","29403"