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

 

[SHEETMAGIC]

Hi there,
I am back onto this task. I am wondering if the lack of any reply to my message in February was caused by anything in particular?
I am also now exploring using Apple Automator to assist in this process. I've figured out how to get an Apple Mail rule to trigger a script that will save the attachment to a folder and append that date to the end of the file name. The files that come in every month have the same filename.
I've also figured out how to automate the importing of the file into Excel.
Where I come unstuck is with APPENDING the data to a previous Excel sheet… so that each month the row of data in the file I am importing (which happens to be the 2 row, sometimes through to the 3rd or 4th) is appended to the previous months.

Is this something any one can point me in the right direction on?

Thank you,

Jonathan

(Excel 14.2.3 on Mac OS X 10.7)

 

[SHEETMAGIC]

I found this thread (http://www.mrexcel.com/forum/showthread.php?626941-Excel-2011-for-MAC-VBA-Ron-de-Bruin-Merge-Folders) which is similar to what I am trying to do.
I had also found Ron's page (http://www.rondebruin.nl/mac.htm) whilst looking for solutions to this.

I have set things up so that the files are renamed to TXT files, and have the date appended on the end.

I found some solutions for a macro like this on Windows, but there were too many differences for me to get it to work on Mac.

Would love to figure this out. I am sure that with some fiddling I would get Ron's script to do what I want. THe script I am referring to is here: http://www.rondebruin.nl/mac.htm#Merge

The WYSIWYG editor is not loading for some reason, so I apologise for the long URLs rather than HTML links.

 

[xenou]

You said in post 1 you've tried recording a macro. That might be a helpful place to start to give a good idea of what the import involves (detail-wise) if you can post that. Generally this is a relatively straightforward thing. Mac users are rarer here so that may be a reason why no response (I don't even know myself if a Mac would require anything different or not, compared to a PC). A second useful bit of detail might be sample data from the TSV file (with confidential data removed, if any).

 

[Tinbendr]

Take a look at this site.

 

[SHEETMAGIC]

Thank you for the suggestions. I'll do everything I can to provide necessary info.
First off, this thread (http://www.mrexcel.com/forum/showth...data-from-several-txt-files-in-a-given-folder) seems to be describing a procedure that is VERY close to what I want to do. However it is for Windows, and I was not able to get it to work past the line (modified to suit my path) -
f = DIR("Lion:Users:jonathan:Documents:Test:" & "*.txt")
(sorry, not sure why no WYSIWYG tools are appearing… I've tried in Safari and Chrome… let me see if I can guess a few shortcodes)

—

Here is what the macro recorded. This was from importing one file into call A1 onward in an existing sheet.

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;Lion:Users:jonathan:Documents:Reports:sales_comp.txt" _
        , Destination:=Range("A1"))
        .Name = "sales_comp"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlMacintosh
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
        1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
        , 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
        .UseListObject = False
    End With
End Sub

(glad the code markup was easy to guess)

Keep in mind that the actual files will be names as shown in this macro EXCEPT they will have a country code and a date appended onto the end of them. Something like sales_comp_US_25-08-2012.txt
I get these reports for US and UK and AU… so name could be

sales_comp_UK or AU or US

and then the date and then .txt.

I’ll now check out the link Tinbendr suggested.

Thank you very much.

 

[SHEETMAGIC]

I looked at the link provided. I note it says:

The macro below will copy every txt file that you select with GetOpenFilename to your workbook.
If you select ten txt files in the folder then you end up with 10 new worksheets in
your workbook. Each worksheet will have the name of the txt file.

It differs from what I am trying to achieve in that I need the data from row 2 (until the last row with data in it — usually row 3 or 4, but could be higher) appended onto ONE sheet, starting the appending at the first empty row.

I did find that site in my search, and this info was kind of helpful: http://www.rondebruin.nl/mac.htm#Merge
The first issue I faced is that I was not sure how to specify a TXT file with the ExtChoice examples given there:

ExtChoice 1 = only xls files
ExtChoice 2 = only xlsx files
ExtChoice 3 = only xlsm files
ExtChoice 4 = only xlsb files
ExtChoice 5 = xls, xlsx, xlsm, xlsb files

 

[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.

 

[xenou]

I'm not enthused about a querytable solution since that uses MSQuery which is a Microsoft data access technology (not really sure that it would be on a MAC). What happens when you just open the workbook (Workbooks.Open). Does it look okay as an excel file? You might want to record the code as you go through the open workbook steps - this could be a clue as to how to properly open the file as a TSV.

Note, in reply to your earlier post:

First off, this thread (http://www.mrexcel.com/forum/showthr...a-given-folder) seems to be describing a procedure that is VERY close to what I want to do. However it is for Windows, and I was not able to get it to work past the line (modified to suit my path) -
f = DIR("Lion:Users:jonathan:Documents:Test:" & "*.txt")

DIR is a windows command to change directories. See here: http://stackoverflow.com/questions/10045474/dir-function-not-working-in-mac-excel-2011-vba
We still don't know if the querytable works since we haven't gotten that far yet ... ! First, just post the code from opening the workbook without a querytable involved (just record yourself opening the TSV file with excel).

 

[SHEETMAGIC]

We still don't know if the querytable works since we haven't gotten that far yet ... ! First, just post the code from opening the workbook without a querytable involved (just record yourself opening the TSV file with excel).

The strangest thing is happening. For some reason when I record a Macro of my opening the TSV file (which invokes the Import dialogue, which I go through and complete) does NOT actually write a macro. The macro name (Sub and End Sub) appear in the editor, but there's nothing between the subs… unless I happen to click on a cell and then a Range.select command is there… but nothing of the file opening.