Need to retain text format in code to import all csv files in folder

Question

I'm trying to import all csv files in a folder into new worksheets in an Excel file while retaining the text format.

I pieced together some code through some research and have it nearly working the way I need, but when I run the macro, all of the columns are set to General.

Any insight into this is greatly appreciated.

Sub ImportCSV()
Application.ScreenUpdating = False
Const conSpath As String = "C:\MyPath\"
Dim sMasterFile As String
Dim sSheetName As String
Dim sFile As String
Dim iNextSheet As Integer

ChDir conSpath
sMasterFile = ActiveWorkbook.Name
iNextSheet = Sheets.Count
sFile = Dir(conSpath & "*.csv", vbNormal)
While sFile <> ""
  Workbooks.OpenText Filename:=sFile, _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, _
        Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), _
        Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2)), TrailingMinusNumbers:=True
    sSheetName = ActiveSheet.Name

    Sheets(sSheetName).Copy After:=Workbooks(sMasterFile).Sheets(iNextSheet)
    Workbooks(sFile).Close SaveChanges:=False
    iNextSheet = iNextSheet + 1
    sFile = Dir
Wend
Application.ScreenUpdating = True
End Sub

Edit: I was able to change the columns to text, but I am still losing my leading zeros.

Sub ImportCSV()
Application.ScreenUpdating = False
Const conSpath As String = "C:\MyPath\"
Dim sMasterFile As String
Dim sSheetName As String
Dim sFile As String
Dim iNextSheet As Integer

ChDir conSpath
sMasterFile = ActiveWorkbook.Name
iNextSheet = Sheets.count
sFile = Dir(conSpath & "*.csv", vbNormal)
While sFile <> ""
 Workbooks.OpenText FileName:=sFile, _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
        FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2), _
        Array(5, 2)), TrailingMinusNumbers:=True
    sSheetName = ActiveSheet.Name: Sheets(sSheetName).Cells.NumberFormat = "@"

    Sheets(sSheetName).Copy After:=Workbooks(sMasterFile).Sheets(iNextSheet)
    Workbooks(sFile).Close True
    iNextSheet = iNextSheet + 1
    sFile = Dir
Wend
Application.ScreenUpdating = True
End Sub

Format the cells as Text and then use QueryTable. See [THIS](http://stackoverflow.com/questions/11267459/vba-importing-text-file-into-excel-sheet/11267603#11267603) link — Siddharth Rout, Jan 20 '14 at 20:01
I am able to get that to work for a single file import, but I am trying import all .csv files in a specific folder. There is usually about 20 files in the folder, and the names are changed daily. — user955289, Jan 20 '14 at 20:08
Do you mean all csv in one sheet or separate sheets? Did you also check the other methods in that links — Siddharth Rout, Jan 20 '14 at 20:09
All the csv files would need to be in separate worksheets because the headers are different in a lot of the files. I did check all of the code in that link, but it didn't seem to fit my needs. — user955289, Jan 20 '14 at 20:15
No, I couldn't get it to work. I was able to make it so I could import all the files into one worksheet: `With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;" & strPath & strExtension, Destination:=Range("$A$" & nxt_row)) .Name = strExtension` but I am still losing my leading zeros. — user955289, Jan 21 '14 at 17:47
https://learn.microsoft.com/en-us/office/vba/api/excel.querytable.textfileparsetype — 0m3r, Jan 10 '19 at 03:00

score 0 · Answer 1 · answered Jan 20 '14 at 20:46

0

The WorksheetFunction.Text method might solve your problem with leading zeros. This allows you to set the format on a single number that you want formatted as text.

If your numbers (including leading zeros) are all the same length, you could do something like this with the range of cells containing the numeric value:

Sub ConvertToTextWithLeadingZeros()

    Dim rngText As Range
        Set rngText = Selection
    Dim rngCell As Range
    Dim strText As String

    For Each rngCell In rngText
        strText = WorksheetFunction.Text(rngCell, "000000")
        rngCell.NumberFormat = "@"
        rngCell.value = strText
    Next rngCell

End Sub

Just set "0000000" to contain a number of zeros equal to the number of digits you want (including leading zeros).

answered Jan 20 '14 at 20:46

Instant Breakfast

1,383
2
14
28

Thanks for the response. The values that contain leading zeros are varying in length, so this won't quite work. – user955289 Jan 20 '14 at 20:50
Try this edit: `strText = WorksheetFunction.Text(rngCell.text, "000000")` – guitarthrower Jan 20 '14 at 22:35
This still restricts the values to a fixed length. The values themselves are varied in length. For example, one value could be 0054, and another value could be 000561480. – user955289 Jan 21 '14 at 17:08

score 0 · Answer 2 · answered Feb 03 '20 at 14:29

I solved the "losing-leading-zeros"-Problem handling csv-files with VBA this way:

    With ThisWorkbook.Worksheets("tmp").QueryTables.Add(Connection:="TEXT;" & xFile, Destination:=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
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 437
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = True
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With

The important part is the Attribute .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)

Every text-column needs a "2".

Need to retain text format in code to import all csv files in folder

2 Answers2