pratiksuhasaria

New Member
Joined
Mar 26, 2019
Messages
24
While importing a text file with city, states, and zip codes, i run into an issue during my data review because Excel is dropping the leading 0’s from the zip codes. How can be this fixed so that leading 0's does not get drop? here is the code on which i was trying.

Sub CombineTextFiles()

Dim FilesToOpen
Dim x As Integer
Dim wkbAll As Workbook
Dim wkbTemp As Workbook
Dim sDelimiter As String

On Error GoTo ErrHandler
Application.ScreenUpdating = False

sDelimiter = "|"

FilesToOpen = Application.GetOpenFilename _
(FileFilter:="Text Files (*.txt), *.txt", _
MultiSelect:=True, Title:="Text Files to Open")

If TypeName(FilesToOpen) = "Boolean" Then
MsgBox "No Files were selected"
GoTo ExitHandler
End If

x = 1
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
wkbTemp.Sheets(1).Copy
Set wkbAll = ActiveWorkbook
wkbTemp.Close (False)
wkbAll.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:="|"
x = x + 1

While x <= UBound(FilesToOpen)
Set wkbTemp = Workbooks.Open(Filename:=FilesToOpen(x))
With wkbAll
wkbTemp.Sheets(1).Move After:=.Sheets(.Sheets.Count)
.Worksheets(x).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=False, Space:=False, _
Other:=True, OtherChar:=sDelimiter
End With
x = x + 1
Wend

ExitHandler:
Application.ScreenUpdating = True
Set wkbAll = Nothing
Set wkbTemp = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description
Resume ExitHandler
End Sub
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Misca

Well-known Member
Joined
Aug 12, 2009
Messages
1,606
Welcome to the message board!

Try adding the datatypes to your code:
Code:
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2))

The problem with datatypes is you should have fixed number of columns + the data must be in the same order (at least according to data type). In this case it seems like you can set everything to text.
 

pratiksuhasaria

New Member
Joined
Mar 26, 2019
Messages
24
Hi Misca

Not possible to have fixed number of columns + the data may vary depending on file.
So i need something that would directly import text ile in excel without making any changes
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
So, are you saying that you may have a different number of columns with each file (you do not know how many columns the file will have)?
If so, determine what is the most possible number of columns that the file may have, and then add misca's suggestion for that number of fields, i.e.
if your file will never have more than 5 columns, then try:
Code:
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4,2), Array(5,2))
adjust accordingly.

The issue is if you do not tell it the data type of each particular field, Excel is going to try to "guess" and figure it out itself. Since your Zip Codes are numeric, it thinks they are numeric entries and using the General or Number data type. Leading zeroes have no meaning for numeric values, so they will be dropped. So you need/want to import those fields as Text so the leading zeroes are not dropped. That is what misca's code does. It tells Excel that each field is to be brought it as Text, not General or Number.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,000
Messages
5,526,186
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top