• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Import several .tsv files into one Excel file

Busymanjohn

Busymanjohn

Member
Hi guys, I have several ( 18 in total ) .tsv files that I would like to import into one Excel file each file appearing in a different tab, in addition I would then like to have a front tab ( Summary ) that would show all of the data from the other tabs. The information in the .tsv files would change each time they are imported so would obviously like to delete the old data and update with the new data. Does anyone have a way to do this with or without VBA ?
 
Check out Ron's page here. In the Copy/Paste/Merge section, he's got several articles about merging workbooks. You can probably adapt the code to work on tsv files.
 
I'm not sure where the page is here, but I think he's referring to Ron de Bruin's tips which can be found at http://www.rondebruin.nl/tips.htm


Also of interest:

http://chandoo.org/wp/2009/02/04/satisfaction-surveys-excel/

http://excel.tips.net/T003005_Condensing_Multiple_Worksheets_Into_One.html

http://chandoo.org/forums/topic/how-do-i-consolidate-several-tables-in-one-long-table


The latter is a topic I was rather verbose on using a QueryTable to consolidate data from multiple sheets, although your exact application sounds to be different.


Ron covers importing multiple files and various merging/consolidating options. Along with VBA code where applicable.
 
Hi guys, I managed to get a piece of code that works fine to import these .tsv files, what I would like to change though is to have the files start in column B, as they are now with this code they start in column A, can anyone help with the code change ( I am useless with with code, should really sign up for Chandoo's class ). Here is the code I am using ....


Sub Open_TSV_Files()

Dim sCurPath$, sCurName$, aFNames, i&, oNewSheet As Worksheet

sCurPath = ActiveWorkbook.Path

sCurName = ActiveWorkbook.Name

'CHECK: Current directory.

If Len(sCurPath) = 0 Then

MsgBox "Please first save this spreadsheet - then I know which folder to look in for the TSV files.", vbInformation + vbSystemModal

Exit Sub

End If

'CHECK: Get file list.

aFNames = Split(VB_GetFileList(sCurPath & "*.tsv"), vbCrLf)

If UBound(aFNames) < 0 Then

MsgBox "Failed to find any '.tsv' files in the current directory:" & vbCrLf & sCurPath, vbInformation + vbSystemModal

Exit Sub

End If

'DO: Open Tab separated files.

For i = 0 To UBound(aFNames)

Workbooks.OpenText Filename:=sCurPath & "" & aFNames(i), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Tab:=True

Sheets(1).Move After:=Workbooks(sCurName).Sheets(1 + i)

Next i

MsgBox "FYI: Have opened the following " & (1 + UBound(aFNames)) & " files, each in a separate tab:" & vbCrLf & vbCrLf & Join(aFNames, vbCrLf), vbInformation + vbSystemModal

End Sub

Function VB_GetFileList(sFileSpec$, Optional iFileType& = vbNormal, Optional sSepChar$ = vbCrLf) As String

'SPEED: Faster than other methods - uses Dir()

'DESC: Return a file listing, separated using 'sSepChar'.

'EXAMPLE: MsgBox "Hi - Here is the .BAT files on c: root..." & vbCrLf & vbCrLf & VB_GetFileList("c:*.bat"), vbInformation

Static sPrevFSpec$, sPrevFileList$, tPrevTime

Dim sFileList$, sFname$


'CHECK: Duplicate file listing requested within 5 seconds?

If StrComp(sFileSpec, sPrevFSpec, vbTextCompare) = 0 Then

If (Timer() - tPrevTime) < 5 Then

VB_GetFileList = Join(Split(sPrevFileList, "|"), sSepChar)

Exit Function

End If

End If


'GET: File list

sFileList = Dir$(sFileSpec, iFileType)

If Len(sFileList) Then

Do

sFname = Dir

If Len(sFname) = 0 Then Exit Do

sFileList = sFileList & "|" & sFname

Loop

End If

VB_GetFileList = Join(Split(sFileList, "|"), sSepChar)


sPrevFSpec = sFileSpec

sPrevFileList = sFileList

tPrevTime = Timer

End Function
 
Hi Busyman --

For i = 0 To UBound(aFNames)

Workbooks.OpenText Filename:=sCurPath & "" & aFNames(i), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Tab:=True

Sheets(1).Move After:=Workbooks(sCurName).Sheets(1 + i)

Next i



is the loop that does the actual import, using the built-in Excel import function.


You could add a line between
Code: 
Workbooks.OpenText... and Sheets(1).Move
... like the following:



Sheets(1).Columns(1).Insert
Click to expand...

to insert a blank column to the left of the table, shifting it all to column B onward before the new worksheet is finally placed in your target workbook.
 
Thanks asa, works well ,,, now, one last thing, after I have merged the files and have them all starting in col. B in each tab ( I will be using column A to do an IF statement ) I would like to have the data from each of the tabs move into a front sheet where col A has country codes and ignore those that don't have a country code ,,,, I guess this would need to be a separate macro and no adjoined to the existing macro .... how do I do this?
 
First, I'd definitely make it a separate macro, simply for debugging purposes. You could later then have a "master" macro that calls both the other macros.

Your description of what you want done is somewhat vague, from a VB perspective. My guess is the basic structure will be something like:

[pre]
Code: 
Sub MoveData()
i = 1
With Worksheets("Summary")
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> .Name Then
'This is the part that's not clear
'on which cell/value/data you want copied
MyValue = ws.Range("A1") '????
.Cells(i, "A") = MyValue
i = i + 1
End If
Next
End With
End Sub
[/pre]
 
Hi Luke, Thanks for the code ,,, but when I go to run it I get a message box "Subscript out of range" .... do I need to do something with the code before I run the macro?
 
I would like the code to pull the data from col A of each tab into a front tab ( say called Summary ), col A of each tab will contain 2 letters which represent country codes and there is ( at present ) 18 of them .... but the letters will not appear on every row in col A of each tab, so I only want to pull those rows of data that have country codes from each tab into the summary tab and have no gaps between the rows in the summary tab ,,,, does this help explain it more?
 
Ok, got the Subscript out of range piece working, and the Macro is pulling data from each tab into the Summary tab, but only for what is in cell A1 of each tab ,,,, the range needs to be cell A1 to Z100 ( for now ) and only pull the data where col A in each tab is populated with a country code ( like AT, BE, DI, FR etc etc etc ), how do i change the code to pull this?
 
Hi Luke, any ideas on how I can change the code to do what I need it to do ,,, I've tried updating the MyValue = ws.Range("A1")to MyValue = ws.Range("A1:Z300"), but it doesn't work ,,,, and I'm now stuck :-(
 
[pre]
Code: 
MyValue = ws.Range("A1") '????

The above assigns to MyValue the Value in cell A1.  It actually refers to a Range object, but Value is the default property of a range.  It only works for one cell.

[pre][code].Cells(i, "A") = MyValue
[/pre]

The Above line assigns MyValue to the Value property of the cell in Row i, Column "A" of your Summary sheet. (.Cells is also a range object, with Value the default property again).


To copy a range of cells, if you want to copy them unchanged, you could use the Copy method of the Range object. The example from Excel's Help:

Worksheets("Sheet1").Range("A1:D4").Copy _
destination:=Worksheets("Sheet2").Range("E5")[/code][/pre]
However, Luke's routine looks to be designed to summarize your data by creating one row in the Summary sheet for every other sheet in the workbook, and copying one cell to column "A". It could be extended to copy a different cell to columns B, C, D, etc. by repeating the MyValue/.Cells lines several times.

How is copying 26 columns and 300 rows for every worksheet a summarization?


Do you want to put formulas on your Summary sheet to calculate Sums, Averages, etc.?


Just some thoughts, I'm not taking over for Luke -- but your requirement doesn't seem clear to me.


~~~~~~~~~~~~~~~~~~~


OK, forgive my rambling, I have re-read your original requirement. Sounds like you want to retrieve a list of unique country codes in one Summary table, from all the other sheets?


I think this could be done with a pivot table, since they can take multiple source ranges, group fields, and eliminate blanks. This would give you a unique list of country codes.


It could also be done with some significant changes to Luke's previously posted code that loops through all the cells in column A of each imported sheet, finding country codes, and adding them to the Summary sheet. To eliminate duplicates, it could either use some fancy VBA footwork before putting the data on the Summary Sheet, or use various techniques to eliminate them after adding them. In Excel 2010 the macro could call the "Remove Duplicates" feature. In other versions, rely on filtering, and then delete the filtered rows.


Asa
 
Hey asa / Luke ,,,, I managed to find a guy who gave me this code ,,, and seems to work well ... but thanks for the feedback guys, I really appreciate it ,,, next stop Amazon for a book on VBA.


Sub MoveData()

i = 1

With Worksheets("Summary")

For Each ws In ThisWorkbook.Worksheets

If ws.Name <> .Name Then

For l = 2 To ws.Range("a" & ws.Rows.Count).End(xlUp).Row

If Len(ws.Range("a" & l).Text) > 0 Then

i = i + 1

.Range("a" & i).Resize(, 26).Value = ws.Range("a" & l).Resize(, 26).Value

End If

Next

End If

Next

End With

End Sub
 
You must log in or register to reply here.
Back
Top