Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dropping leading zeros in character fields.
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dropping leading zeros in character fields.
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 "Ron K" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dropping leading zeros in character fields.
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 "Ron K" wrote in message ... 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. -----Original Message----- 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 "Ron K" wrote in message ... 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leading Zero dropping off after export to Excel | Excel Discussion (Misc queries) | |||
How do I stop Excel from dropping leading zeros? | Excel Discussion (Misc queries) | |||
Dropping Zeros | Excel Worksheet Functions | |||
Converting xls to CSV file - dropping leading 0s | Excel Discussion (Misc queries) | |||
removing leading zeros in numeric fields | Excel Discussion (Misc queries) |