Dataport - escaping quotes in import file

kaseycparker · 2006-04-13

I am creating a dataport that will be used as part of a monthly interface importing invoices into Navision. The import file is comma delimited with text fields enclosed in quotes. If a quote actually exists in a field then the quote is escaped by doubling the quote (e.g. "This is an example of ""escaping"" quotes inside a field").

The Navision dataport handles the comma delimiter, using the FieldSeparator property; and the enclosing quotes using the FieldStartDelimiter and FieldEndDelimiter properties. However, the dataport is not handling the escaped quotes inside fields. When it hits an escaped quote in a field it is treating it as the end of the field, and then the next quote as the start of the next field. The data port then errors with a datatype violation on a field further in the record because the fields are now being read out of place.

I have no control over the format of the import file, as it is created by an outside party. Any suggestions, please, on how to get the dataport to correctly handle the escaped quotes?

Savatage · 2006-04-13

kaseycparker wrote:

If a quote actually exists in a field then the quote is escaped by doubling the quote (e.g. "This is an example of ""escaping"" quotes inside a field").

You mention IF so are you saying sometimes it could be Double-Double Quotes and Sometimes Not?

Or is it set in stone that it's always Double-Double?
If so just change
FieldStartDelimiter ""
FieldEndDelimiter ""

kaseycparker · 2006-04-13

Savatage wrote:

kaseycparker wrote:

If a quote actually exists in a field then the quote is escaped by doubling the quote (e.g. "This is an example of ""escaping"" quotes inside a field").

You mention IF so are you saying sometimes it could be Double-Double Quotes and Sometimes Not?

Or is it set in stone that it's always Double-Double?
If so just change
FieldStartDelimiter ""
FieldEndDelimiter ""

Thanks for the response. I think I wasn't clear about my description before. The text fields are always enclosed in one set of quotes. However, sometimes a text field, which is enclosed in quotes, actually contains a legitimate quote inside field. In this case the quote in the field is escaped by having two sets of quotes, i.e. "". Here's a sample record in the import file:

01,"55 Audubon Ave ""Grace Salon""","NEW YORK","NY",10032,

The second field in this record is enclosed in quotes but also has legitmate quotes in the filed "Grace Salon" these quotes are escaped by using two sets of quotes ""Grace Salon"".

So I need to keep the FieldStartDelimiter and FieldEndDelimiter = " to account for the enclosing quotes; but I need a way of handling legitimate quotes inside the field. I need the second field in the record above to be inserted into the street address field in the target table as:

55 Audubon Ave "Grace Salon"

I hope this makes sense.

DenSter · 2006-04-13

When I ran into the same issue, I ended up importing the data into an excel sheet, and writing a VBA macro that creates a text file with delimiters of my choice, so you can use whatever you want. At the time I used @ as field start and end, and ~ as separator, but if you have email addresses in your data obviously that's not going to work for the same reasons

. I searched my hard drive for the sheet, but I can't find it anymore.

ara3n · 2006-04-13

What I usually do is set it to <None>. In the dataport I delete Starting and trailing Quotes, and then replace "" with ".
Just simple string manipulation.
Or in excel save it as Tab deleminator.

kriki · 2006-04-14

ara3n wrote:

What I usually do is set it to <None>. In the dataport I delete Starting and trailing Quotes, and then replace "" with ".
Just simple string manipulation.
Or in excel save it as Tab deleminator.

This is the same what I do, but I try to start with tabs. They are easilier to handle and more secure. And if this is not possible, then I use ara3n's first option.

kaseycparker · 2006-04-14

Thank you all for the responses. I also thought of trying to set the FieldStartDelimiter and FieldEndDelimiter to <None> and eliminate the quotes with string manipulation functions. The problem is that the quotes exist because there are commas within the quote enclosed fields. The quotes prevent the legitimate commas inside a field from being recognized as a field separator.

Unless someone can still come up with a better solution in Navision; I think I will have to go with a solution like DenSter proposed, writing a macro in excel to alter the field delimiters from commas to tabs, or something of the like. This will just make fully automating the interface a little more difficult.

Thanks again for the suggestions.

Savatage · 2006-04-14

We also ask outside companies that want to send us info to use ( ~ ) instead of a comma.

~ is so rarely used anywhere that it's a perfect separator.

I have no control over the format of the import file, as it is created by an outside party

Maybe you can ask them to use something other than a comma [-o<
ie..
"Order"~"CS46685"~"Item"~"10000"~"99999"~"1"~"1.50"
"Order"~"CS46685"~"Item"~"20000"~"41132"~"1"~"67.99"
"Order"~"CS46685"~"Item"~"30000"~"41131"~"1"~"67.99"

kriki · 2006-04-14

Savatage wrote:

We also ask outside companies that want to send us info to use ( ~ ) instead of a comma.

~ is so rarely used anywhere that it's a perfect separator.

I have no control over the format of the import file, as it is created by an outside party

Maybe you can ask them to use something other than a comma

Best is the TAB. Also because if you can read in a file in xls, you can easily save it as tab-seperated.

Savatage · 2006-04-14

kriki wrote:

Also because if you can read in a file in xls, you can easily save it as tab-seperated.

Data->Import External Data->Import Data->"The File"

If you pick "other" during the import wizard you can select ~ as your separator too.

----

Anyway if someone is going thru the trouble to create a file for you I find it had to believe that with today's software that they can't help you out. I'd ask O:)

Joe_Little · 2006-04-14

Navision's dataports have to my knowledge always struggled with this. Pretty much back to Avista days. If you have the option of having the original data format changed, you might consider tab delimited (field separator <TAB>) with no delimiters (<None>). This has generally been what I have found works best and you don't have to sweat too much data that has characters that you never thought would be used.

kaseycparker · 2006-04-14

I was able to figure out a solution in Navision. Setting the FieldStartDelimeter to

<">

which is the default, and the FieldEndDelimeter to

",

the dataport imports all data without error, because it only considers a quote as terminating a field if the quote was followed by a comma. This method doesn't actually escape the quotes; it imports both the legitimate quotes inside a field and the escaping quotes. So using my previous example, the field from the file

...,"55 Audubon Ave ""Grace Salon""",...

was imported into the target table as

55 Audubon Ave ""Grace Salon""

However, the extra set of quotes can now be eliminated using string manipulation functions.

Thanks again for everyone's suggestions. Hopefully this can help someone in the future also.

kriki · 2006-04-14

Joe Little wrote:

This has generally been what I have found works best and you don't have to sweat too much data that has characters that you never thought would be used.

That's my experience too.

kaseycparker wrote:

I was able to figure out a solution in Navision. Setting the FieldStartDelimeter to

<">

which is the default, and the FieldEndDelimeter to

",

the dataport imports all data without error, because it only considers a quote as terminating a field if the quote was followed by a comma. This method doesn't actually escape the quotes; it imports both the legitimate quotes inside a field and the escaping quotes. So using my previous example, the field from the file

...,"55 Audubon Ave ""Grace Salon""",...

was imported into the target table as

55 Audubon Ave ""Grace Salon""

However, the extra set of quotes can now be eliminated using string manipulation functions.

Thanks again for everyone's suggestions. Hopefully this can help someone in the future also.

And if there is a ',' in the text to be imported? :whistle:

kaseycparker · 2006-04-14

kriki wrote:

kaseycparker wrote:

I was able to figure out a solution in Navision. Setting the FieldStartDelimeter to

<">

which is the default, and the FieldEndDelimeter to

",

the dataport imports all data without error, because it only considers a quote as terminating a field if the quote was followed by a comma. This method doesn't actually escape the quotes; it imports both the legitimate quotes inside a field and the escaping quotes. So using my previous example, the field from the file

...,"55 Audubon Ave ""Grace Salon""",...

was imported into the target table as

55 Audubon Ave ""Grace Salon""

However, the extra set of quotes can now be eliminated using string manipulation functions.

Thanks again for everyone's suggestions. Hopefully this can help someone in the future also.

And if there is a ',' in the text to be imported? :whistle:

True, if there was a legitimate ", inside of the text field then the import would error. This would be extremely rare, if it ever occurs, with the type of data I am importing. If it does happen then we'll have to manually modify the import file - removing the quotes, replacing them with single quotes, etc. - and run the dataport. However; by and large, this solves the problem of being able to automate the interface without paying the vendor to modify their extract file. Thanks for the heads up.

Dneal · 2006-10-30

you might consider tab delimited (field separator <TAB>) with no delimiters (<None>).

I found this worked for me. However now any data that contains a , will be surrounded by " ". I don't understand why this is happening. Have I missed something?

Dneal

kriki · 2006-10-31

Dneal wrote:

you might consider tab delimited (field separator <TAB>) with no delimiters (<None>).

I found this worked for me. However now any data that contains a , will be surrounded by " ". I don't understand why this is happening. Have I missed something?

Dneal

Did you change the delimiter properties to <None>?

Dneal · 2006-10-31

The properties are as follows:

FieldStartDelimiter=<NONE>
FieldENDDelimiter=<NONE>
FieldSeparator=<TAB>

Dneal

Savatage · 2006-10-31

kaseycparker wrote:

The Navision dataport handles the comma delimiter, using the FieldSeparator property; and the enclosing quotes using the FieldStartDelimiter and FieldEndDelimiter properties. However, the dataport is not handling the escaped quotes inside fields. When it hits an escaped quote in a field it is treating it as the end of the field, and then the next quote as the start of the next field.

going back to the original question - have you tried leaving the start & end blank and just using the comma as the field seperator?

The FieldStartDelimeter and the FieldEndDelimiter properties are used to "Quote" the field contents in situations where the data of a field contains the character that is defined as a separator (FieldSeparator, RecordSeparator or DataitemSeparator).

These delimeters are not obigatory. This means that if only one field of a record needs to be quoted, only this field has to be enclosed by the FieldStartDelimeter and the FieldEndDelimiter characters. The other fields can have the delimeters optionally; it will make no difference during importing.

Howdy, Stranger!

Quick Links

Categories

Dataport - escaping quotes in import file

Answers