Batch importing multiple text files to excel and comma delimiting them

whatalegend123

whatalegend123

whatalegend123

Associate
Joined
16 Jun 2007
Posts
279
Location
mysterious archipelago
Hi guys

I am currently using a piece of equipment in work which outputs data results into text files..for measurements that are taken.I currently have imported the text files one by one to excel and comma delimited them to get at the data,using the standard excel function

I was wondering if anyone would know of / how to write a macro to automate this process

The data comes in text files named xxxx-1.txt, xxxx-2.txt etc and each text file contains roughly 200 lines of data presented like this

XXX, 111
XXX, 222
XXX, 333
XXX, 444

and i have to deal with roughly 10-20 txt files at one go.

so essentially, im looking to import 10 .txt files at a time each containing 200 lines of data which is separated by commas.

Also if i could just import the right hand column of data would ideal only the 111,222,333 part.

i found this from an old forum but it doesnt seem to work as it was written in 2002

"Sub GetTextFiles()
Dim lngCounter As Long, wbText As Workbook

On Error GoTo ErrHandler

Application.DisplayAlerts = False
Application.ScreenUpdating = False

With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles
.LookIn = "C:temptext files" 'Change this to your folder name
.Execute
For lngCounter = 1 To .FoundFiles.Count
If Right(.FoundFiles(lngCounter), 4) = ".txt" Then
Workbooks.OpenText .FoundFiles(lngCounter)
ActiveSheet.UsedRange.Copy
ActiveWorkbook.Close False
ThisWorkbook.Sheets.Add
ActiveSheet.Paste
End If
Next lngCounter
End With


Application.DisplayAlerts = True
Application.ScreenUpdating = True

Exit Sub
"
 
Not knowing anything about vb my solution would be powershell based, pump into a csv and then open in excel.

Can you give some example text files?

Code: 
import-csv c:\input.txt | select {$_.name} | export-csv c:\output.csv -notype

$_.name would be your column headings (assuming you have them) and select just the data you need.

To import multiple txt files I guess you could use a loop/array and output to a single csv.
 
Last edited:
Are all of the text files formatted in the same way? If so, you could use the get-content PowerShell cmdlet. For example;

Code: 
Get-Content .\file1.txt,.\file2.txt,.\file3.txt > .\all.txt

This would read the contents of file1.txt, file2.txt and file3.txt in the current directory (denoted by the .\ notation in PowerShell), and then dump them into all.txt. Then it would be a matter of importing the one text file into Excel, and then deleting the column that you don't want. You could almost certainly automate that part too, but I have no clue how you'd do it -hopefully my suggestion will still save you some time.

Edit: damn, not only was I beaten, but Uhtred's solution is also much more elegant.
 
For starters get rid of 'On Error GoTo ErrHandler' seeing as there is no ErrHandler to go to.

Does something like this work?

Code: 
Sub GetTextFiles()
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:temptext files" 'Change this to your folder name
        .Execute
        For lngCounter = 1 To .FoundFiles.Count
            Workbooks.OpenText.FoundFiles(lngCounter)
            ActiveSheet.UsedRange.Copy
            ActiveWorkbook.Close False
            ThisWorkbook.Sheets.Add
            ActiveSheet.Paste
        Next lngCounter
    End With
Exit Sub

Reply with any error messages that occur.

edit- no point even try that tbh, it's just not going to work, give me a min or two or probably someone will find out a way before I do!
 
Last edited:
why not just pump them in to a SQL Server db?

Surely the data means something so MS's business intelligence (Bids) is better far more pwoerful at this kind of stuff than pivot charts/tables. I assume this data is from a PLC controlling a robot or machine?

Plus if your file grows as I'm sure it will at some point you will hit excels limit row wise and it will start taking hours to display data in to a meanigful manner. Especially at 2k lines per import.
 
Code: 
Sub GetTextFiles()
Set wb = ActiveWorkbook
sausage = Dir("C:\Users\AusomeSauce\Desktop\New folder\*.txt") 'change 

Next i
End Sub

This is probably not a particularly nice way or doing it, but it seems to work :)

Sorry ignore that, I was closing teh workbook before pasting the info which causes a pop-up and potential errors. Here is some better code

Code: 
End Sub

Having looked at your OP more closely I think this code will better suit your needs:
Code: 
Sub GetTextFiles()

Application.ScreenUpdating = False

Set wb = ActiveWorkbook

sausage = Dir("C:\Users\Oliver\Desktop\New folder\*.txt") 'change this directory
salami = "C:\Users\Oliver\Desktop\New folder\" & sausage 'change this dicrectory

For i = 1 To 5 'make this be the number of files you have, i.e. put 1 To 5 if you have 5 files
    If i = 1 Then
    Else
        sausage = Dir()
        salami = "C:\Users\Oliver\Desktop\New folder\" & sausage 'again change the directory
    End If
    Set nwb = Workbooks.Open(salami)
    ActiveSheet.UsedRange.Copy
    Set ws = wb.Sheets.Add
    ws.Paste
    ws.Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, Comma:=True, FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
    ws.Columns("A:A").Delete Shift:=xlToLeft
    nwb.Saved = True
    nwb.Close
Next i

Application.ScreenUpdating = True

End Sub

I'm pretty sure this does exactly what you want.
 
Last edited:
RoganTheGypo said:
fk vb! Dead language! :P *Gets back to oracle :(* 7am finish!
Click to expand...

Maybe for standalone applications but for office work that involves excel work it's perfect as it's pretty damn simple but will still make you look like a genius in front of other colleagues, because code looks like code no matter what the language :p
 
desires said:
Maybe for standalone applications but for office work that involves excel work it's perfect as it's pretty damn simple but will still make you look like a genius in front of other colleagues, because code looks like code no matter what the language :p
Click to expand...

Yeah most people see it as a bit of a dark art in the regular office, I work in a dev/support team for NMUK we are in to SQL, C, ASP & other bits and bobs plus the infastructure (which is mind bending to me a lot of the time) So the words 'Macros' and 'VB' are banned in the office lol! Praise Jeebus for Google and stackexchange :P
 
Hi thanks so much for all the responses

Desires you are a hero and you code was exactly what i needed... however if i could ask one more favour.. would it be possible to output all of the data into one sheet as opposed to creating a new work sheet for each text file?
 
Last edited:
Code: 
Sub GetTextFiles()
 
Application.ScreenUpdating = False
 
Set wb = ActiveWorkbook
Set ws = wb.Sheets(1)
 
sausage = "C:\Users\Me\Desktop\New folder\" 'change this
salami = Dir(sausage)
 
Do While salami <> ""
    lunch = sausage & salami
    Set nwb = Workbooks.Open(lunch)
    ActiveSheet.UsedRange.Copy
    Set nws = wb.Sheets.Add
    nws.Paste
    nws.Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, Comma:=True
    nws.Columns("A:A").Delete Shift:=xlToLeft
    nws.UsedRange.Copy
    ws.Range("B1").Formula = "=COUNTA(A:A)+1"
    ws.Cells(ws.Range("B1").Value, 1).PasteSpecial xlPasteAll
    ws.Range("B1").Value = ""
    Application.DisplayAlerts = False
    nws.Delete
    Application.DisplayAlerts = True
    nwb.Saved = True
    nwb.Close
    salami = Dir
Loop
 
Application.ScreenUpdating = True
 
End Sub
 
Haha thanks..sorry to be a pain i should have mentioned it before. but can each new text file go in a new column.. as opposed to all in one column..

i should really learn to do this stuff
 
You must log in or register to reply here.
Back
Top Bottom