Percent-encoded data in import

[jimicos]

I have several percent-encoded text files I've loaded to my Access database.
That's working well, but the data is percent-encoded. I'd like to automate
the decoding of this data either on import or on command. I've been unable
to find an easy way to do it. I have no Access experience and I'm learning
as I go. Will this require some kind of macro or is there a utility of some
sort that will handle it?

Thanks

 

[gllincoln]

Hi Jim,

When you say percent encoded data - do you mean data that has % as the field delimiter and it's being imported into your data fields, or do you mean certain percentage values are being imported as text fields and the percentage mark is being displayed?

If percent encoded %mydata% is what you refer to - then using the Replace function in the 'update to' row of an update query can handle that.

Replace ([myfieldname],"%","")

If a percentage value and you want to convert it to a decimal value but it's being stored in a text field.

Format(CSng([myfieldname]),"0.0000")

You might want fewer places to the right of the decimal but 4 places generally works well for interest rates, etc.

Hope this helps,
Gordon

 

[jimicos]

Sorry, I didn't explain fully. I mean that the data is downloaded from a web
source and they've replaced certain reserved ASCII characters with their hex
equivalents.. Instead of * you'll see %2A. Instead of @ you'll see %40.

Here's a list of the reserved characters:
http://en.wikipedia.org/wiki/Percent-encoding#Percent-encoding_reserved_characters

Having said that, it looks like this update query with a replace function is
making the change. Is that the most efficient way to decode these characters
to their ASCII counterparts? Also, is there a way to add several "update to"
clauses to the update query? Right now I'm making one substitution at a time.

Thanks again

Hi Jim,

When you say percent encoded data - do you mean data that has % as the
field delimiter and it's being imported into your data fields, or do you
mean certain percentage values are being imported as text fields and the
percentage mark is being displayed?

If percent encoded %mydata% is what you refer to - then using the Replace
function in the 'update to' row of an update query can handle that.

Replace ([myfieldname],"%","")

If a percentage value and you want to convert it to a decimal value but
it's being stored in a text field.

Format(CSng([myfieldname]),"0.0000")

You might want fewer places to the right of the decimal but 4 places
generally works well for interest rates, etc.

Hope this helps,
Gordon

I have several percent-encoded text files I've loaded to my Access database.
That's working well, but the data is percent-encoded. I'd like to automate
the decoding of this data either on import or on command. I've been unable
to find an easy way to do it. I have no Access experience and I'm learning
as I go. Will this require some kind of macro or is there a utility of some
sort that will handle it?

Thanks

 

[gllincoln]

Hi Jimicos,

re: percent-encoding. That's somewhat more complex then. Might require a custom function.

Public Function fixPctCode(ByVal s As String) As String

Dim i As Integer 'loop counter
Dim h As String 'stores hex value as text

'swap %-encode for &H notation
s = Replace(s, "%", "&H")
'initialize i to 1
i = 1
'continue until no more hex values
Do While i > 0

'set i to location of hex
i = InStr(s, "&")

'if we find hex
If i > 0 Then

'grab text version of hex
tmp = Mid$(s, i, 4)
'slice out hex text, replace with ascii char
s = Left(s, i - 1) & Chr(tmp) & IIf(i > Len(s) - 4, "", Mid(s, i + 4))

End If

'loop until we run out of hex values
Loop

'return modified string
fixPctCode = s
End Function

Hope this helps...
Gordon

Sorry, I didn't explain fully. I mean that the data is downloaded from a web
source and they've replaced certain reserved ASCII characters with their hex
equivalents.. Instead of * you'll see %2A. Instead of @ you'll see %40.

Here's a list of the reserved characters:
http://en.wikipedia.org/wiki/Percent-encoding#Percent-encoding_reserved_characters

Having said that, it looks like this update query with a replace function is
making the change. Is that the most efficient way to decode these characters
to their ASCII counterparts? Also, is there a way to add several "update to"
clauses to the update query? Right now I'm making one substitution at a time.

Thanks again

Hi Jim,

When you say percent encoded data - do you mean data that has % as the
field delimiter and it's being imported into your data fields, or do you
mean certain percentage values are being imported as text fields and the
percentage mark is being displayed?

If percent encoded %mydata% is what you refer to - then using the Replace
function in the 'update to' row of an update query can handle that.

Replace ([myfieldname],"%","")

If a percentage value and you want to convert it to a decimal value but
it's being stored in a text field.

Format(CSng([myfieldname]),"0.0000")

You might want fewer places to the right of the decimal but 4 places
generally works well for interest rates, etc.

Hope this helps,
Gordon

I have several percent-encoded text files I've loaded to my Access database.
That's working well, but the data is percent-encoded. I'd like to automate
the decoding of this data either on import or on command. I've been unable
to find an easy way to do it. I have no Access experience and I'm learning
as I go. Will this require some kind of macro or is there a utility of some
sort that will handle it?

Thanks

 

[jimicos]

You'll have to forgive me.. I'm a bit lost. How would I implement this
code? And I assume I'd need to pass the data to be converted into this
function? How would I invoke it?

Thanks again

Hi Jimicos,

re: percent-encoding. That's somewhat more complex then. Might require a
custom function.

Public Function fixPctCode(ByVal s As String) As String

Dim i As Integer 'loop counter
Dim h As String 'stores hex value as text

'swap %-encode for &H notation
s = Replace(s, "%", "&H")
'initialize i to 1
i = 1
'continue until no more hex values
Do While i > 0

'set i to location of hex
i = InStr(s, "&")

'if we find hex
If i > 0 Then

'grab text version of hex
tmp = Mid$(s, i, 4)
'slice out hex text, replace with ascii char
s = Left(s, i - 1) & Chr(tmp) & IIf(i > Len(s) - 4, "", Mid(s,
i + 4))

End If

'loop until we run out of hex values
Loop

'return modified string
fixPctCode = s
End Function

Hope this helps...
Gordon

Sorry, I didn't explain fully. I mean that the data is downloaded from a web
source and they've replaced certain reserved ASCII characters with their hex
equivalents.. Instead of * you'll see %2A. Instead of @ you'll see %40.

Here's a list of the reserved characters:
http://en.wikipedia.org/wiki/Percent-encoding#Percent-encoding_reserv ed_characters

Having said that, it looks like this update query with a replace function is
making the change. Is that the most efficient way to decode these characters
to their ASCII counterparts? Also, is there a way to add several "update to"
clauses to the update query? Right now I'm making one substitution at a time.

Thanks again

Hi Jim,

When you say percent encoded data - do you mean data that has % as the
field delimiter and it's being imported into your data fields, or do you
mean certain percentage values are being imported as text fields and the
percentage mark is being displayed?

If percent encoded %mydata% is what you refer to - then using the Replace
function in the 'update to' row of an update query can handle that.

Replace ([myfieldname],"%","")

If a percentage value and you want to convert it to a decimal value but
it's being stored in a text field.

Format(CSng([myfieldname]),"0.0000")

You might want fewer places to the right of the decimal but 4 places
generally works well for interest rates, etc.

Hope this helps,
Gordon

I have several percent-encoded text files I've loaded to my Access
database.
That's working well, but the data is percent-encoded. I'd like to
automate
the decoding of this data either on import or on command. I've been
unable
to find an easy way to do it. I have no Access experience and I'm
learning
as I go. Will this require some kind of macro or is there a utility of
some
sort that will handle it?

Thanks

 

[gllincoln]

Hi Jimicos,

Probably easiest thing would be to create a code module and paste the function into the module.

Since you aren't likely to want to tackle writing your own import code right now, the best way to use this function might be to go ahead and import the percent-encoded file. Then create an update query that 'fixes' the encoding. You would select each field of your table that you wish to translate the percent-encoding for and in the update row you would type in

=fixPctCode([theFieldName])

Replacing theFieldName with the real name of your field named in the top row (Field).

Hope this helps...
Gordon

You'll have to forgive me.. I'm a bit lost. How would I implement this
code? And I assume I'd need to pass the data to be converted into this
function? How would I invoke it?

Thanks again

Hi Jimicos,

re: percent-encoding. That's somewhat more complex then. Might require a
custom function.

Public Function fixPctCode(ByVal s As String) As String

Dim i As Integer 'loop counter
Dim h As String 'stores hex value as text

'swap %-encode for &H notation
s = Replace(s, "%", "&H")
'initialize i to 1
i = 1
'continue until no more hex values
Do While i > 0

'set i to location of hex
i = InStr(s, "&")

'if we find hex
If i > 0 Then

'grab text version of hex
tmp = Mid$(s, i, 4)
'slice out hex text, replace with ascii char
s = Left(s, i - 1) & Chr(tmp) & IIf(i > Len(s) - 4, "", Mid(s,
i + 4))

End If

'loop until we run out of hex values
Loop

'return modified string
fixPctCode = s
End Function

Hope this helps...
Gordon

Sorry, I didn't explain fully. I mean that the data is downloaded from a web
source and they've replaced certain reserved ASCII characters with their hex
equivalents.. Instead of * you'll see %2A. Instead of @ you'll see %40.

Here's a list of the reserved characters:
http://en.wikipedia.org/wiki/Percent-encoding#Percent-encoding_reserv ed_characters

Having said that, it looks like this update query with a replace function is
making the change. Is that the most efficient way to decode these characters
to their ASCII counterparts? Also, is there a way to add several "update to"
clauses to the update query? Right now I'm making one substitution at a time.

Thanks again

:

Hi Jim,

When you say percent encoded data - do you mean data that has % as the
field delimiter and it's being imported into your data fields, or do you
mean certain percentage values are being imported as text fields and the
percentage mark is being displayed?

If percent encoded %mydata% is what you refer to - then using the Replace
function in the 'update to' row of an update query can handle that.

Replace ([myfieldname],"%","")

If a percentage value and you want to convert it to a decimal value but
it's being stored in a text field.

Format(CSng([myfieldname]),"0.0000")

You might want fewer places to the right of the decimal but 4 places
generally works well for interest rates, etc.

Hope this helps,
Gordon

I have several percent-encoded text files I've loaded to my Access
database.
That's working well, but the data is percent-encoded. I'd like to
automate
the decoding of this data either on import or on command. I've been
unable
to find an easy way to do it. I have no Access experience and I'm
learning
as I go. Will this require some kind of macro or is there a utility of
some
sort that will handle it?

Thanks