Would like help to automate importing a TSV file

[SHEETMAGIC]

Hi folks,

A few times a month I receive a TSV file that I want to add to an Excel sheet. The first row is always the same, consisting of the column titles.
The remaining rows vary in number. Sometimes there's just one, other times three (and in the future perhaps more).
The files come with consecutive numbering, in the form of sales-comp-#.tsv - # being a number one higher than the files before it. I currently have files 1 to 77 to import. The next one I receive will be number 78.

I have tried recording a Macro but was not sure how to handle that fact that the data needs to be appended into the next available row in the same spreadsheet each time. If there were always the same number of new rows being imported, I figured there'd be a straightforward way to increment by that number, but as it varies, I got stuck.

The file extension can be renamed to CSV if that helps in any way. They come in as TSV (the data is tab delimited) though. But I noticed Excel does not include TSV in it's import file type... so I was having to switch to ALL FILES.

Ideally I'd like a macro or script associated with a button, which will find any new files and important them. In which case it might need to rename the previously imported ones, so they can be easily ignored the next time.

Any help would be greatly appreciated.

With thanks,

Jonathan


I am using Excel 14.1.4 on Mac OS X

 

[xenou]

Rat. I'm not a mac user. Try changing the extension to .txt and see if the recorder can capture that. Otherwise, check under VBA help for the OpenText function - normally, that's the function you would invoke to open a tab delimited file.

 

[Tinbendr]

If you can provide a sample, we might be able to skip the Excel import and parse the file directly into Excel.

Here's another article about using ADO to import text files.

 

[xenou]

^^ Good article but not sure it will work on a mac (does Excel/Mac VBA have an implementation of the vb scripting library and/or ActiveX Data Objects?)

 

[SHEETMAGIC]

If you can provide a sample, we might be able to skip the Excel import and parse the file directly into Excel.

I don't see a way to attach a file. Please let me know the best way to provide a sample.


Also here is the macro recorded when opening the file, with a TXT extension. It still invokes the Import routine, obviously, because the file contains Tab Delimited Values and Excel needs to know how I want that handled. Although none of that shows up in the make when the opening of the file is recorded. It only shows up when I specifically IMPORT the file.

Sub OPENTXT()
'
' OPENTXT Macro
'

'
    Workbooks.OpenText Filename:= _
        "Lion:Users:jonathan:Documents:Publishing Related:Test:sales_comp UK January 2012.txt" _
        , Origin:=xlMacintosh, StartRow:=2, DataType:=xlDelimited, TextQualifier _
        :=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
        False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1)
    ExecuteExcel4Macro "WINDOW.SIZE(398,53,"""")"
    ExecuteExcel4Macro "WINDOW.MOVE(2,-42,"""")"
End Sub

 

[Tinbendr]

You have to use a file sharing service like box.com or google docs to provide a link to the sample.

And what do you want from the file? All of it, part of it, etc?

 

[SHEETMAGIC]

If a sample of the data is required, do let me know.
But I think it is enough to say this:

Row 1 is the headers. It is redundant because the excel sheet will already have those in place.
Row 2 is the beginning of the data for import.
The last row is simply the last row with data in it. Typically row 2, but could be 3 or higher.
The columns go from A through to BY. Altough, if it makes it easier, I actually only need to the data from columns C through to Z.

Hi Tinbendr,
Please see the above notes.
Also… the filename will look like sales_comp_US_25-08-2012.txt
There are three file "types". On with "US" (like the above name example), another with "UK", and soon one with "AU" in it.
I will duplicate the macro so that there is one for importing each file type into different sheets or perhaps different workbooks if that works out easier.

I have added the file to Dropbox. The link is: https://dl.dropbox.com/u/76391030/sales_comp_UK_ 2012-08-25.txt
As mentioned, there are columns from A to BY. I think everything past Z is redundant and not needed.

 

[Tinbendr]

OK try this.

Sub Import()
    Dim WS As Worksheet
    Dim FN As Variant
    Dim MyArray As Variant
    Dim FF As Integer
    Dim A As Long
    Dim B As Long
    Dim iRow As Long
    Dim Temp$
    
    Set WS = ActiveWorkbook.ActiveSheet

    FN = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If FN <> False Then
        With WS
            FF = FreeFile
            Open FN For Input As #FF
            Line Input #FF, Temp$

            Do
                Line Input #FF, Temp$
                MyArray = Split(Temp$, Chr(9))
                iRow = iRow + 1

                For B = 0 To 25
                    .Cells(iRow, B + 1).Value = MyArray(B)
                Next
                A = A + 1
            Loop Until EOF(FF)
        End With
    End If
    Close #FF
End Sub

 

[SHEETMAGIC]

I realise I let the ball drop on this one, and I don't wish to leave thing unfinished as I appreciate the help I was receiving. Back in August I ended up doing the math by hand, going through each sheet one-by-one. It got to the point where my attempt to automate it was creating too much of a delay in delivering the necessary data.
BUT I will have to pick this ball back up, as collating this data is going to be an ongoing requirement.

@Tinbendr: Please excuse my tardy reply, and thank you for the code you suggested. I will try this shortly and get back to you.

Regards,

Jonathan