# Thread: Populate a field with consecutive numbers by a constant length string.

1. ## Populate a field with consecutive numbers by a constant length string.

Hi,

I'm searching for an arcpy script or VBscript for the field calculator so I can populate a field with consecutive numbers represented by a constant length string. The consecutive numbers can come from the "FID" field.
Example:
000001
000002
...........
000011
...........
000345

I had a VBA script from Easycalculate who does the trick, but it doesn't work in ArcGis 10.
VBA script:

'rec_Number_ConstantLengthString.cal
'Author: Ianko Tchoukanski
'http://www.ian-ko.com
'=========================
Static rec As Long
Static j As Long
Dim lStart As Long
Dim lInterval As Long
Dim i As Integer
Dim iStringLength As Integer
Dim sID As String
'============================
'set the variables below
iStringLength = 7
lStart = 0
lInterval = 1
'============================
sID = ""
If (j = 0) Then
rec = lStart
Else
rec = rec + lInterval
End If
For i = 1 To iStringLength - Len(CStr(rec))
sID = sID & "0"
Next i
sID = sID & CStr(rec)
j = j + 1

Anybody any ideas?

2. ## Re: Populate a field with consecutive numbers by a constant length string.

Hi,

This might sound a bit simplistic, but have you tried to create the numbers in excel and then just copy and paste into the attributes table? To get the numbers to display as 001 (for example) in excel just type the first number as '001 the apostrophe will ensure that the cell contents are displayed exactly as you type them and not converted to 1. Just drag down the cells from the first, and there you are!

I've just tried this and it worked fine for me.

Paul

3. ## Re: Populate a field with consecutive numbers by a constant length string.

Hi,

Thansk for the reply. I knew the Excel solution, but as far as I know, it's not possible to copy paste the consecutive numbers from Excel in an ArcMap attribute table. If it's possible, I don't know how to do it, i've tried a couple of things, but it didn't work for me.

Microsoft Office doesn't saves dbf files anymore and I don't want work with joins, to transfer the consecutive numbers from the excel file to the attribute table.

4. ## Re: Populate a field with consecutive numbers by a constant length string.

You might get some ideas from this Python demo script, it pads floating point values to a width of 6 characters, 0 decimal places, then pads with zeros and converts to a string. I am sure that you could develop a code block to perform the same in the field calculator

Code:
```'''
'''
vals = [1,10,100,1000]
for i in vals:
out = "%06.0f" % (i)
print str(out)```
the result is
Code:
```000001
000010
000100
001000```
changing the "out" line to

out = "%08.2f" % (i)

yields

Code:
```00001.00
00010.00
00100.00
01000.00```
so you can see it is quite flexible.

5. ## Re: Populate a field with consecutive numbers by a constant length string.

You can still uses your favourite VBA scripts whilst ESRI allows you too. They made changes to the VB scripting engine so all variables are now of type variant so any scripts where you declare a variable for example Dim X as Long should now become Dim X otherwise the calculation fails.

Duncan

6. ## Re: Populate a field with consecutive numbers by a constant length string.

Hi,

Thanks for the suggestions.

@Dan_patterson: thanks for the demo script, but I don't have any knowledge of python and how to transform the code so I can use it with the field calculator. Also the result is not the result i'm looking for. I want to calculate something like this:

Example:
000001
000002
...........
000011
...........
000345

@Hornbydd: I've tried this, but i didn't succeed to run the script (with the variables as variant) mentioned in my first post. I think it's not allowed to declare statics in the new VB scripting engine.

7. ## Re: Populate a field with consecutive numbers by a constant length string.

Hi,

I've had a tinker with the code, it seems that "Next i" should become simply "Next" in a VB Script For Loop. Also it looks like they have disabled support for static variables, they really don't want us using VB Script do they?

As much as I hate to say it you either have to give in to ESRI and start writting your scripts in python or use something else like this.

Duncan

8. ## Re: Populate a field with consecutive numbers by a constant length string.

Originally Posted by webbedrecords
Hi,

Thansk for the reply. I knew the Excel solution, but as far as I know, it's not possible to copy paste the consecutive numbers from Excel in an ArcMap attribute table. If it's possible, I don't know how to do it, i've tried a couple of things, but it didn't work for me.

Microsoft Office doesn't saves dbf files anymore and I don't want work with joins, to transfer the consecutive numbers from the excel file to the attribute table.
That's weird - it worked for me. Admittedly I was just using a test shpfile I made, so it wasn't particularly complex, but they copied in no problem.

9. ## Re: Populate a field with consecutive numbers by a constant length string.

Originally Posted by PaulGordon72
That's weird - it worked for me. Admittedly I was just using a test shpfile I made, so it wasn't particularly complex, but they copied in no problem.
I've tried again and it worked! Thanks!

Never the less, I shall take a look at arcpy in the near future, you can't solve everything with Excel.
And like Hornbydd already mentioned, ESRI really doesn't want us using VB script anymore.