Unpacking Data (i.e. 00001460{ in a text file)

[IrishRed]

Hello All,
I receive a monthly text file which includes fields that have packed data.
For example the field value comes in as 00001460{ and must be converted to
000014600 which means that { has a value of 0 and then A has a value of 1 and
so on. I am looking for a way to basically automate a find and replace. I
want to convert { to 0, A to 1, B to 2, C to 3, etc...
Is there a way to do this in Access ? I am also researching possibly doing
it before the text file is imported to Access. Any suggestions would be
greatly appreciated. Thank you for your time.

Red

 

[MikeB]

Hello All,
I receive a monthly text file which includes fields that have packed data.
For example the field value comes in as 00001460{ and must be converted to
000014600 which means that { has a value of 0 and then A has a value of 1 and
so on. I am looking for a way to basically automate a find and replace. I
want to convert { to 0, A to 1, B to 2, C to 3, etc...
Is there a way to do this in Access ? I am also researching possibly doing
it before the text file is imported to Access. Any suggestions would be
greatly appreciated. Thank you for your time.

Red

If it is at all possible, I'd suggest you speak to the person
originating the file. If they use a mainframe, it is trivial to write
a Sort (yes Sort) step that can do data conversion on specific fields.
It shouldn't take them more than 30 minutes (that is if they have to
find and read the Sort manual) to figure out how to do this and to
code it up.

 

[John W. Vinson]

Hello All,
I receive a monthly text file which includes fields that have packed data.
For example the field value comes in as 00001460{ and must be converted to
000014600 which means that { has a value of 0 and then A has a value of 1 and
so on. I am looking for a way to basically automate a find and replace. I
want to convert { to 0, A to 1, B to 2, C to 3, etc...
Is there a way to do this in Access ? I am also researching possibly doing
it before the text file is imported to Access. Any suggestions would be
greatly appreciated. Thank you for your time.

Red

An update query updating the field to

Left([fieldname], 8) & Instr("{ABCDEFGJI", Right([fieldname], 1)) - 1

should do it.

 

[IrishRed]

John,
THANK YOU SOOOOOOO MUCH ! This is great. It works perfectly for what I need.
I wish that I posted sooner. I stubbornly tried to figure this out for too
long.

Again, thank you so much. You help is very much appreciated.

Hello All,
I receive a monthly text file which includes fields that have packed data.
For example the field value comes in as 00001460{ and must be converted to
000014600 which means that { has a value of 0 and then A has a value of 1 and
so on. I am looking for a way to basically automate a find and replace. I
want to convert { to 0, A to 1, B to 2, C to 3, etc...
Is there a way to do this in Access ? I am also researching possibly doing
it before the text file is imported to Access. Any suggestions would be
greatly appreciated. Thank you for your time.

Red

An update query updating the field to

Left([fieldname], 8) & Instr("{ABCDEFGJI", Right([fieldname], 1)) - 1

should do it.

 

[Dirk Goldgar]

Hello All,
I receive a monthly text file which includes fields that have packed data.
For example the field value comes in as 00001460{ and must be converted to
000014600 which means that { has a value of 0 and then A has a value of 1
and
so on. I am looking for a way to basically automate a find and replace. I
want to convert { to 0, A to 1, B to 2, C to 3, etc...
Is there a way to do this in Access ? I am also researching possibly doing
it before the text file is imported to Access. Any suggestions would be
greatly appreciated. Thank you for your time.

Red

An update query updating the field to

Left([fieldname], 8) & Instr("{ABCDEFGJI", Right([fieldname], 1)) - 1

should do it.

Be aware, though, that there's a different set of encoded characters for
negative numbers. John's method is simple and elegant, if you can be sure
you will always have positive numbers of a fixed length.

For a similar question, quite some time ago, I wrote this function:

'----- start of code -----
Function fncZonedToNumber( _
ZonedValue As Variant, _
Optional DecimalPlaces As Integer = 0) _
As Variant

Dim strValue As String
Dim strLast As String

If IsNull(ZonedValue) Then
fncZonedToNumber = Null
ElseIf VarType(ZonedValue) <> vbString Then
fncZonedToNumber = CVErr(5) ' invalid argument
ElseIf Len(ZonedValue) = 0 Then
fncZonedToNumber = Null
Else
strLast = Right(ZonedValue, 1)
strValue = Left(ZonedValue, Len(ZonedValue) - 1)

If InStr(1, "0123456789", strLast, vbBinaryCompare) Then
strValue = strValue & strLast
ElseIf InStr(1, "ABCDEFGHI", strLast, vbBinaryCompare) Then
strValue = strValue & Chr(Asc(strLast) - 16)
ElseIf InStr(1, "JKLMNOPQR", strLast, vbBinaryCompare) Then
strValue = "-" & strValue & Chr(Asc(strLast) - 25)
ElseIf StrComp(strLast, "{", vbBinaryCompare) = 0 Then
strValue = strValue & "0"
ElseIf StrComp(strLast, "}", vbBinaryCompare) = 0 Then
strValue = "-" & strValue & "0"
Else
fncZonedToNumber = CVErr(5) ' invalid argument
Exit Function
End If

If DecimalPlaces = 0 Then
fncZonedToNumber = Val(strValue)
Else
fncZonedToNumber = Val(strValue) / (10 ^ DecimalPlaces)
End If

End If

End Function
'----- end of code -----