method for importing .txt files into separate columns

**microsofteggshell** · 02-11-2013, 03:12 PM

Hi, I have to import text files (example attached) manually into a spreadsheet and was looking for a simpler, less time consuming method of doing this. I came across some suggestions on this forum that gave me hope that there is a better way. However I don't know much about VBA and have so far being substituting my current macro into some of the suggested solutions posted here. I just cant seem to get it to fully do what I require which is:

1)Take .txt files from a selected folder,
2)Format using macro used for manually adding .txt files
3)Add .txt files into existing spreadsheet into next free column.
The .txt. files are sorted by date in the folder (oldest-newest) and this is the order which I enter them into the spreadsheet. The sheet gets updated weekly with about 20-30 files .txt each time.

The macro I use when manually adding at the moment is:

Sub test()
'
' test Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;F:\129629_$01_121056.txt" _
        , Destination:=Range("$A$1"))
        .Name = "129629_$01_121056"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = ":"
        .TextFileColumnDataTypes = Array(1, 1, 9, 9, 9, 9, 9, 9, 9, 9, 9)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveWindow.SmallScroll Down:=-12
    Application.Goto Reference:="test"
End Sub

The code I was trying to use for my application is this example:
http://www.rondebruin.nl/txtcsv.htm

Everytime I add in my macro I either mess up the code or the macro runs fine but will only add one file. Any suggestion on what can be done to fix it are much appreciated.
This is my first post here, so please let me know if I should do things differently...Thanks again for your time

**Alf** · 02-11-2013, 05:58 PM

You could try and run the macro "ImpTxtFile" and see if this works for you.

Alf

**microsofteggshell** · 02-12-2013, 08:34 AM

Thanks a million Alf, The Macro works almost exactly as needed. Just one question, The macro seems to import by date decending per month e.g. Jan 31st - Jan 1st then Feb 12th - Feb 1st, is there any way to get it to import the files as they are in the folder with date ascending from oldest to newest e.g 1 Jan - 12 Feb? I have tried re-sorting files in the folder but the macro seems to import the same each time. Once again, thanks for your help!

**Alf** · 02-12-2013, 09:13 AM

is there any way to get it to import the files as they are in the folder with date ascending from oldest to newest

I really don't know. If you perhaps could upload a small sample of say 3 different files (same month) I'll have I look at it and see what I can do.

Alf

**microsofteggshell** · 02-12-2013, 10:34 AM

I've attached the files
Thanks

**Alf** · 02-12-2013, 12:39 PM

Had a look at the files you uploaded and I’m not sure of how to tackle this part of the problem.

The “Dir” function sorts the file by name so even it your folder is sorted by date par instance the macro will pick the files in the order “Sorted by name”.

As your file name is a number i.e. 128897_090524 macro starts with the lowest number and work it ways upward to 129113_085530.

A possible solution is to add “Jan01_” to the first January file then “Jan02_” to the next file and so forth.

Another solution could be using a macro that imports all text file names from the folder to say B1 to B?? Sheet2:

Sub Get_fil_name()
Dim FileName As String
FileName = Dir("F:\*.txt")
Do While Len(FileName) > 0

Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = FileName

FileName = Dir
Loop

End Sub

You then sort the names in the order you wish to import the files. Macro can then be modified to read file name from B1 to B?? when importing to Excel.

Last solution (as I’m running out of ideas). Mark this thread as “Solved” and click on my star (bottom left) and give a rating to my answer.

Then starts a new thread called “Importing text files by date” and give examples of the file names and state you problem. You could also upload your present code for importing. Most likely some other forum member may then be able to help you.

Alf

**microsofteggshell** · 02-14-2013, 06:53 AM

Thanks again Alf for all your help. I have tried the options but still no joy. I will go with your last solution, Thanks.

**Alf** · 02-14-2013, 09:36 AM

Thanks for feedback and rep! I'll check the forum from time to time as I would really like learn how to import by date.

There is an Excel function in Visual Basic called "FileDateTime" that returns the date and time of when a file was created or last modified. Perhaps one could use this but this modified part worries me.

As your text file contains a date "of creation" perhaps one could use that as a base for sorting i.e. first import txt files to Excel and then check "creation date" and then do the final sorting on the Excel sheet. Hmmm I will have a think about that possibility.

Alf