Importing Text File into Excel loses leading zero on some fields.

[brettmanning24]

Hello,

Is it possible to import a txt file into Excel and keep the leading
zero's? I am using vbscript to import the text file, the text file
contains a user reference which sometimes contains a number with a
leading zero i.e. 0111. This number is imported as 111. The user
reference varies sometimes so I cannot really use a custom field i.e.
"0"#.

Below is an example of the import procedure I am using, is it possible
to specify that for example column L is displayed as text?

Range("FileOrig").FormulaR1C1 = FILEtoOPEN

Workbooks.OpenText Filename:=FILEtoOPEN, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1),
Array(4, 1), Array _
(5, 4), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1),
Array(10, 1), Array( _
11, 1), Array(12, 1), Array(13, 1))
Range("A1:AZ1500").Select
Selection.Copy

Thanks,

Brett

 

[Dave Peterson]

Try recording a macro when you open that text file manually. But make sure you
change the field to Text.

Or if you want to keep that field a number, you could keep it General, but then
apply the numberformat in your code.

 

[brettmanning24]

Try recording a macro when you open that text file manually. But make sure you
change the field to Text.

Or if you want to keep that field a number, you could keep it General, but then
apply the numberformat in your code.











--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thank you.

Please could you explain the numberformat to me?

 

[Joel]

This is not an importing problem it is more the way excel handles numbers.
To havve leading zeroes either the data has to be stored as textt or the
number needs to be formated to include leading zeroes.

You need to fromat the worksheet as either text or or with a format with
leading zeroes.

Here is a macro that may help.


Sub Getdata1()

Const MyPath = "C:\temp\test"
Const ReadFileName = "test.txt"
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Const StartCol = 1
Const Colwidth = 2
Dim ColWidths(7, 2)
Dim Data(7) As String



ColWidths(1, StartCol) = 1
ColWidths(1, Colwidth) = 21
ColWidths(2, StartCol) = 22
ColWidths(2, Colwidth) = 21
ColWidths(3, StartCol) = 43
ColWidths(3, Colwidth) = 14
ColWidths(4, StartCol) = 57
ColWidths(4, Colwidth) = 23
ColWidths(5, StartCol) = 80
ColWidths(5, Colwidth) = 15
ColWidths(6, StartCol) = 95
ColWidths(6, Colwidth) = 16
ColWidths(7, StartCol) = 111
ColWidths(7, Colwidth) = 16


Set fs = CreateObject("Scripting.FileSystemObject")
'open file
ReadPathName = MyPath & "\" & ReadFileName
Set fread = fs.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

RowCount = 1
Do While tsread.atendofstream = False

InputLine = tsread.ReadLine

Call GetDataField(InputLine, ColWidths, Data)
Call WriteSheet(Data, RowCount)
Loop
tsread.Close

End Sub

Sub GetDataField(InputLine, ByRef ColWidths, ByRef Data)
Const StartCol = 1
Const Colwidth = 2
For DataField = 1 To 7
Data(DataField) = Trim(Mid(InputLine, _
ColWidths(DataField, StartCol), _
ColWidths(DataField, Colwidth)))
Next DataField

End Sub
Sub WriteSheet(ByRef Data, ByRef RowCount)
For DataField = 1 To 7
If IsNumeric(Data(DataField)) Then
If Left(Data(DataField), 1) = "0" Then
Cells(RowCount, DataField). _
NumberFormat = "0####"
End If
Cells(RowCount, DataField) = _
Val(Data(DataField))
Else
Cells(RowCount, DataField) = Data(DataField)
End If
Cells(RowCount, DataField) = Data(DataField)
Next DataField

RowCount = RowCount + 1

End Sub

 

[Dave Peterson]

Numberformat is the format for the cell.

In xl2003, I'd use:
Select the cell (or range of cells)
format|cells|Number tab
and use a custom numberformat of something like:
00000

(to always show at least 5 digits.)