Dropping leading zeros in character fields.

  • Thread starter Thread starter Ron K
  • Start date Start date
R

Ron K

Hello Group
I am programatically creating an excel work sheet loading
the columns from fields in a Database file. The fields
in the DB are defined as character but when the spread
sheet gets created it drops the leading zeros. It assume
the data is numeric but it is not. These leading zeros
are required. Does anyone know how I can resolve this
problem.
I am using a program language called Visual RPG which is
very similar to Visual Basic.
Thanks.
 
Open it as a text file
Suppose you have file
0001 A
0002 B
0003 C
0004 D
0005 E

separated by any delimeter. (In my example it is tab). Lets say your file is
"YourFie.txt"
then the following code will do the job:

Workbooks.OpenText Filename:="YourPath" & "\" & "YourFie.txt", _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 2), Array(2, 1))

FieldInfo says first column will be of type text.

Ömer Ayzan
 
What is your file type. I presume it is a text file (yourfile.txt) Don't
attempt to surround the data with quotes.
Alternatively you can use excel's import wizard to understand what is going
on.
From within excel start recording a macro. Then open your text file as you
normally would. Choose your text file. Because it is text import wizard will
start. Follow instructions of the wizard. When it comes step 3 it asks you
column data format. There are 3 of them, namely "General", "Text", "Date".
At the bottom list box select your column with leading zeros and put its
column format to text. You'll see that you properly opened your column with
leading zeros. The trick is that you say this column is of type text.
Now you can stop the macro recorder an look at the code it recorded.
The information is in the FieldInfo:
In my earlier example there were two columns separated with Tab. You can use
whatever separator there is in your original file. Fieldinfo is array. If
you look at my example field info "FieldInfo:=Array(Array(1, 2), Array(2,
1))" says first column is of type 2 (text) and the second column is of type
1 (general)
I think this will help
regards,
Ömer

Omer
Surrounding the text in double quotes does retain leading
zeros for values such as "0001 A" but it does not work
when all of the values are numeric such as "0012345".
The result is "12345".
Any other suggestions.
 
Back
Top