Excel text to columns formatting to date issue

Question

Good morning,

I have a macro that imports all text files from a directory into the workbook. This has been working very well and has not had any formatting or related issues.

I have encountered text files that seem to autoformat and am having troubl.e trying to get this to work with text to columns.

Data in text file:

#
#
#
# CELL : RESULT
#
0:0
1:0
2:0
3:0
4:0
5:0
6:0
7:0
8:0
9:0
10:0
11:0
12:0
13:0
14:0
15:0
16:0
17:0
18:0
19:0
20:0
21:0
22:0
23:0
24:0
25:

After import:

# CELL : RESULT
#
0:00
1:00
2:00
3:00
4:00
5:00
6:00
7:00
8:00
9:00
10:00
11:00
12:00
13:00
14:00
15:00
16:00
17:00
18:00
19:00
20:00
21:00
22:00
23:00
24:00:00

After text to column:

#       
#       
#       
# CELL   RESULT 
#       
0:00    0   12:00 AM
0:00    0   12:00 AM
0:00    0   12:00 AM
0:00    0   12:00 AM
0:00    0   12:00 AM
0:00    0   12:00 AM
0:00    0   12:00 AM
0:00    0   12:00 AM
0:00    0   12:00 AM
0:00    0   12:00 AM
0:00    0   12:00 AM
0:00    0   12:00 AM
0:00    0   12:00 PM
0:00    0   12:00 PM
0:00    0   12:00 PM
0:00    0   12:00 PM
0:00    0   12:00 PM
0:00    0   12:00 PM
0:00    0   12:00 PM
0:00    0   12:00 PM
0:00    0   12:00 PM
0:00    0   12:00 PM
0:00    0   12:00 PM
0:00    0   12:00 PM
1/1/1900  12    0   12:00 AM

When copied and pasted, the data looks fine.

I have tried converted data to general or text but that turns the data into decimals. I have also tried to trim the last ":*" out but have failed at that too.

If anyone has any guidance on what is happening, I would be greatly appreciative. The code I am using to import the text files is:

Sub Import_Text_Files()

    Dim fPath   As String
    Dim fCSV    As String
    Dim wbCSV   As Workbook
    Dim wbMST   As Workbook

    Set wbMST = ThisWorkbook
    'this is to set the directory in which the text files are
    fPath = Sheets("Console").Cells(16, 12).Value

    'turn off screen updating for speed
    Application.ScreenUpdating = False

    'start the text file listing
    fCSV = Dir(fPath & "*.txt")
    On Error Resume Next
    Do While Len(fCSV)      0
        'open a CSV file
        Set wbCSV = Workbooks.Open(fPath & fCSV)
        'delete sheet if it exists
        wbMST.Sheets(ActiveSheet.Name).Delete
        'move new sheet into workbook
        ActiveSheet.Move After:=wbMST.Sheets(wbMST.Sheets.Count)
        'ready next CSV
        fCSV = Dir
    Loop

    Set wbCSV = Nothing

End Sub

I apologize I cannot post images on this account as I do not have the reputation and haven't used this site in years. I have them on my end, however.

Can you edit your question by adding (1) an example of the raw data; (2) example of the desired format of this raw data; and (3) an example of the format you're getting? — xidgel, Aug 14 '18 at 15:10
Thank you for the comment - I did through images but that failed, I will copy and paste some in now. — Excel Help, Aug 14 '18 at 15:23
I have added this in - thanks! My desired format is a 2 column output with the incrementing primary key and associated value (0 in this case). — Excel Help, Aug 14 '18 at 15:36
@EverythingExcel: Why not [import using ADO](https://learn.microsoft.com/en-us/previous-versions/windows/internet-explorer/ie-developer/scripting-articles/ms974559(v=msdn.10)) into a recordset, then dump the data into the sheet using `range.copyfromrecordset`? — Our Man in Bananas, Aug 14 '18 at 15:40
@everything-excel: also see [ExcelTip: Import data from a text file (ADO) using VBA in Microsoft Excel](https://www.exceltip.com/import-and-export-in-vba/import-data-from-a-text-file-ado-using-vba-in-microsoft-excel.html) and [SO: return csv file as recordset](https://stackoverflow.com/questions/11635526/return-csv-file-as-recordset) — Our Man in Bananas, Aug 14 '18 at 15:43
Thank you so much! I learnt a lot from that. I've also since reached a solution. — Excel Help, Aug 20 '18 at 12:20
The solution is correction the data at source and giving it a delimiter that is NOT a colon :). — Excel Help, Aug 20 '18 at 12:20

Ron Rosenfeld · Accepted Answer · 2018-08-14T20:17:09.887

Instead of Workbooks.Open you could be using Workbooks.OpenText

If you do that, you can specify the colon as the delimiter and split it before Excel has a chance to change it to what it thinks you might prefer (times in this case).

It is rarely a good idea to use Workbooks.Open on a text file.

Check VBA Help for more information on `Workbooks.OpenText

Example code:

Workbooks.OpenText Filename:=fullPathofTextFile, _
    Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
    Comma:=False, Space:=False, Other:=True, OtherChar:=":", FieldInfo:= _
    Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

You could also use Power Query (aka Get&Transform) to accomplish the same thing with a Data Connection that can be refreshed, if necessary.

The ADO option mentioned in the comments is especially useful if the text file is UTF-8.

Thank you very much for that - I pushed back on source data and the process now works with different delimiters. This is an accepted answer as it is on the same lines of that. I appreciate your help! — Excel Help, Aug 20 '18 at 12:21

Excel text to columns formatting to date issue

1 Answers1