Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Text File into Excel loses leading zero on some fields.
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Text File into Excel loses leading zero on some fields.
On 18 Dec, 18:40, Dave Peterson wrote:
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. wrote: 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- Hide quoted text - - Show quoted text - Thank you. Please could you explain the numberformat to me? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Text File into Excel loses leading zero on some fields.
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 " wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing Text File into Excel loses leading zero on some fields.
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.) wrote: <<snipped Please could you explain the numberformat to me? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IMPORTING PC-DOS PLAIN TEXT FILES WITH LEADING SPACES | Excel Discussion (Misc queries) | |||
Import a txt file with text fields that have meaningful leading sp | Excel Discussion (Misc queries) | |||
webquery loses format of leading zero and to scientific | Excel Programming | |||
number of fields exceeds 256 when importing from a dbf file | Excel Discussion (Misc queries) |