How to import .CSV preserving leading zeros numbers into Excel using VBA

Question

I have a database which make the extract in .CSV and using VBA I import the data into Excel but when imported, there are some leading zeros that are missing in the IDs

Even when I open the .CSV file in Excel, those zeros are not there. This is the current code I'm using:

sub import()
Dim File As String

MsgBox "Please select the Extract File", vbInformation

With Application.FileDialog(msoFileDialogFilePicker)
    .Filters.Clear
    .InitialFileName = "\\route"
    .AllowMultiSelect = False
    .Filters.Add "csv", "*.csv"
    If .Show = -1 Then
        File = .SelectedItems(1)
    Else
        MsgBox "Please, select the file then try again", vbExclamation
        Exit Sub
    End If
End With

With Worksheets("Data Paste").QueryTables.Add(Connection:= _
    "TEXT;" & File _
    , Destination:=Worksheets("Data Paste").Range("$A$1"))
    .Name = "FileName"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 65001
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileTrailingMinusNumbers = True
    .TextFileColumnDataTypes = Array(xlTextFormat)
    .Refresh BackgroundQuery:=False
End With
Exit Sub

I tried changing the format of the worksheet to TEXT beforehand, but those zeros are still missing even though that data is in text format.

Edit 1: I opened the .CSV with NotePad and those leading zeros are there. But not when I open it with Excel

"Even when I open the .CSV file, those zeros are not there" - are you opening the file in excel? Have you tried (eg) Notepad ? If something looks like a number then Excel will convert it and drop any leading zeros. How many fields/columns in your file? — Tim Williams
– Tim Williams, Commented Jun 8, 2021 at 21:37
@TimWilliams yes, I opened that file in NotePad and the zeros are there, but when importing them with VBA into Excel, those zeros are missing. Opening CSV with excel won't show those leading zeros either. — Dark161000
– Dark161000, Commented Jun 8, 2021 at 21:40
.TextFileColumnDataTypes = Array(xlTextFormat) will only apply text format to the first column. "Returns or sets an ordered array of constants that specify the data types applied to the corresponding columns in the text file that you are importing into a query table. The default constant for each column is xlGeneral. Read/write Variant." learn.microsoft.com/en-us/office/vba/api/… — Tim Williams
– Tim Williams, Commented Jun 8, 2021 at 22:03

Tim Williams · Accepted Answer · 2021-06-08 22:22:26Z

1

.TextFileColumnDataTypes = Array(xlTextFormat)

will only apply text format to the first column. You need to add a value corresponding to each column which needs to be imported as text format.

https://learn.microsoft.com/en-us/office/vba/api/excel.querytable.textfilecolumndatatypes

Returns or sets an ordered array of constants that specify the data types applied to the corresponding columns in the text file that you are importing into a query table. The default constant for each column is xlGeneral. Read/write Variant.

answered Jun 8, 2021 at 22:22

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to import .CSV preserving leading zeros numbers into Excel using VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related