How to import all text files (column wise) from a folder?

[Amit1]

Dear Friends,
I am trying to import several text files (around 80 in number) at a time from a folder. The data should be loaded to the excel sheet column wise. I was able to get the macro for loading it one by one where every time i have change the name of the file in the path.
The code in provided below:

Sub DataLoad()
'
' DataLoad Macro
' DataLoad
'
' Keyboard Shortcut: Ctrl+q
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Documents and Settings\Administrator\Desktop\target folder" _         '(I'm mentioning the file   name here after target folder)
        , Destination:=Range("$A$1"))
        .Name = "data1"
        .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 = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("B1").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B1").Select
    ActiveSheet.Paste
    Columns("B:B").EntireColumn.AutoFit
    ActiveWorkbook.Save
    ActiveWorkbook.Save
    Range("C1").Select
End Sub

How to load all the files at a time in a Excel sheet?
Looking forward for your suggestions.

 

[Joe4]

I modified some code I found here to do what you want. Make sure you select the "Microsoft Scripting RunTime" library in VBA references to use this code.

Sub ReadFilesIntoActiveSheet()
' Modified from code found here: [URL]http://stackoverflow.com/questions/4937664/can-i-import-multiple-text-files-into-one-excel-sheet[/URL]

    Dim fso As FileSystemObject
    Dim folder As folder
    Dim file As file
    Dim FileText As TextStream
    Dim TextLine As String
    Dim cl As Range
    Dim myCol As Long

' Get a FileSystem object
    Set fso = New FileSystemObject
' get the directory you want
    Set folder = fso.GetFolder("C:\C\Test\")
' set original column at 1
    myCol = 1
' set the starting point to write the data to
    Set cl = ActiveSheet.Cells(1, myCol)
    
' Loop thru all files in the folder
    For Each file In folder.Files
' Open the file
        Set FileText = file.OpenAsTextStream(ForReading)
' Read the file one line at a time
        Do While Not FileText.AtEndOfStream
            TextLine = FileText.ReadLine
            cl = TextLine
' Move to next row
            Set cl = cl.Offset(1, 0)
        Loop
' Clean up
        FileText.Close
' Move to top of next column
        myCol = myCol + 1
        Set cl = Cells(1, myCol)
    Next file
    
    Set FileText = Nothing
    Set file = Nothing
    Set folder = Nothing
    Set fso = Nothing
    
End Sub

 

[Amit1]

Dear Joe,
Thanks a lot, i will try it out and let you know about the output.

Best Regards,
Amit

 

[Amit1]

Dear Joe,
I have tried the code after enabling the "Microsoft Scripting RunTime" library in VBA references. I am getting a runtime error of error code "1004" (Application or object defined error).
Debugger is showing the following part highlighted:
Read the file one line at a time
Do While Not FileText.AtEndOfStream
TextLine = FileText.ReadLine
cl = TextLine
After by passing the debugging i was able get one file imported but both the columns (As it is having 2 columns) are being loaded in the first column itself (might be problem with the offset setting).
Below is the sample dataset:
Base Value(s)

61 35.833332
73 43.333332
85 42.5
97 45.0
109 47.5
121 50.833332
133 51.666664
145 55.0
157 55.0
169 58.333332
this continues upto almost 500 rows and like this i have to load around 26/sheet and more than 1000 sheets( divided into different workbooks).
Thanks for your help, if you can suggest the rectifications, that will be a great help for me.

Best regards,
Amit

 

[Joe4]

Regarding your error, do you have some sort of header or blank row at the top of your data files?

Regarding have two columns, you will probably need to update the column counter in the code to:
myCol = myCol + 2

 

[Amit1]

Yeah, I am having header rows as Base and Value(s) and following that a blank row is also present.

 

[Joe4]

You had mentioned that you have two columns of data in your text files. How is the data delimted (csv, tab-delimited, space delimited, etc)?

 

[Amit1]

Sorry to reply you late, actually 2 sets of data i have to import , one set is tab-delimited and the other comma separated.
As i have to import individually so it will be a separate exercise of tab-delimited and csv.The sample data sets are as follows:
Base Value(s)

61 49.166668
73 48.333332
85 49.166668
97 50.0
109 50.0
121 50.833332
133 51.666664
145 51.666664
157 51.666664
169 49.166668
181 49.166668
193 50.0
205 50.0
217 50.833332

and the other type is:
Base Value(s)

61 52.499996 60.000004 35.0
76 55.0 57.5 35.0
91 60.000004 57.5 35.0
106 65.0 60.000004 32.5
121 57.5 65.0 37.5
136 57.5 62.5 40.000004
151 50.0 57.5 40.000004
166 47.5 65.0 35.0
181 50.0 57.5 35.0
196 47.5 60.000004 37.5
211 47.5 57.5 45.0
226 52.499996 52.499996 45.0

 

[Joe4]

This problem just keeps getting more and more complicated which each post you make. It can be quite frustrating to the people trying to help you. It is best to lay out all the conditions up-front so that we know what we are working with, so we don't post answers which answer the question you asked, but don't solve your problem because you left out some very important details (file formats, headers, number of columns).

Not only does it look like you have different kind of text files, but what originally was a one-column file, turned into two-column files, and finally look like you have variable number of columns in your last example (one has two, one has four).

 

[Amit1]

Dear Joe,
Sorry for replying you late and the confusion created during this course of discussion.
As there is no attachment permitted in this forum, I am not able to send the files directly to you. Anyways,
below are the 2 types of files I am having (containing more than 10000 rows each). Individual folder contains any one type of these file types.
FILE TYPE1 (2 columns) :

Base Value(s)

61 49.166668
73 48.333332
85 49.166668
97 50.0
109 50.0
121 50.833332
133 51.666664
145 51.666664
157 51.666664
169 49.166668
181 49.166668
....................
....................
11605 50.833332
11617 49.166668
11629 47.5


Average Value(s)
================
1 52.26166


FILE TYPE2(4 columns):
Base Value(s)

251 5.945524 6.3486958 5.859436
275 7.471512 8.216547 5.489109
299 3.3624458 10.85059 14.033716
323 10.316038 8.778086 15.06167
347 7.7888756 8.169615 8.784737
371 8.20015 7.5109463 7.996622
395 14.309538 9.34387 7.269682
419 17.8904 5.586954 10.848452
443 16.41527 5.5203247 13.118637
467 11.9956 6.607086 14.703793
491 13.020374 8.141617 9.414464
515 13.578577 13.574339 13.136975
539 4.6174107 10.4442005 13.585515
......................................................
......................................................
......................................................
11435 22.076962 12.707674 23.764414
11459 25.681255 9.325983 15.720619
11483 24.166227 10.536232 13.178138
11507 21.713467 20.807606 14.291999
11531 21.3046 30.898634 16.405272
11555 28.130867 34.521667 20.781502


Average Value(s)
================
1 11.497483
2 10.740389
3 11.867325


Both the types are in .txt format and the files are named as (say for instance) Data1.txt and Data2.txt.
On a similar fashion, I am having n number of files in a folder (260 to be exact/folder) and total folders are 25.
I want to import the data from each folder at a time automatically, where the file will be imported in an excel file (I am using 2007), which will be something like this ...............
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Data1[/TD]
[/TR]
[TR]
[TD]Base[/TD]
[TD]Values()[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD] 49.166668[/TD]
[/TR]
[TR]
[TD]73[/TD]
[TD]48.333332[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The code so far I was using (posted in this thread) was not automated, I had to change the file name (Data1.txt/Data2.txt)(which is eventually the header of the imported data) every time and refresh to import it.
The obtained output table of the individually imported files are like the following (includes one blank row after the header(Data1) to be exact)....
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Data1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Base[/TD]
[TD]Values()[/TD]
[/TR]
[TR]
[TD]61[/TD]
[TD] 49.166668[/TD]
[/TR]
[TR]
[TD]73[/TD]
[TD]48.333332[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So, how to automate the whole procedure (using any kind of loop) until it imports all the .txt files present in a folder (260 to be exact) where it will keep on changing the file names automatically during importing.
N.B: The file name provided here (Data1/Data2.txt) is different than the original names and they do not follow any kind of pattern (except they are all .txt file). All the file names are arbitrary (which should be the column header as shown in the example output) and could not be changed at any circumstances.
Hope I am able to convey my message properly.
Thanks in advance for all the helps rendered by you so far.


Best Regards,
Amit