Extract specific data from .txt file and put it into worksheet excel
Hello people,
i am working in an chemical lab and for the moment i am struggling with following problem. On daily base, a lab instrument generates a txt file containing data. We print those data and put it afterwards manually into an existing excel file. Automatic importing of the txt file is not possible because we only enter the data that is necessary.
So in attachment you see a txt file with 5 lines containing data. Below you can see the first line:
It is my intention to put the data of some well defined components into excel at fixed places. An example: the first values of the line are "u1". The value noted after Fe, in this case 112.04, must be filled in under the column with header Fe1. The value noted after Zn, in this case 480.48, must be filled in under column with header Zn1. Etc for the other 2 elements.
The first values of the second line are "u2", which means that the values must be filled in under the columns containing (element)2 (example Fe2) etc.
Data must be added every day, so it is not the intention to overwrite the old data in excel but start at the first free row below the last data.
I guess my problem can be fixed with some VBA language or so, but i have tried it with no luck due to lack of experience.
According to the attachment a pure VBA starter demonstration still working if you change any header
you must paste to the destination Sheet2 worksheet module :
PHP Code:
Sub Demo1() Dim S$, H, R&, F%, V, W, L&, C S = ThisWorkbook.Path & "\data_txt.TXT": If Dir(S) = "" Then Beep: Exit Sub H = Me.UsedRange.Rows(1).Value2 R = Cells(Rows.Count, 1).End(xlUp).Row F = FreeFile Open S For Input As #F V = Split(Input(LOF(F), #F), vbCrLf) Close #F Application.ScreenUpdating = False For Each W In V W = Split(Replace(W, " ; ", ";"), ";") S = Split(W(0), "u")(1) F = 1 For L = 1 To UBound(W) Step 2 C = Application.Match(W(L) & S, H, 0) If IsNumeric(C) Then If F Then R = R + 1: F = 0: Cells(R, 1).Value = Date Cells(R, C).Value2 = W(L + 1) End If Next L, W Application.ScreenUpdating = True End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » !
Last edited by Marc L; 01-24-2020 at 10:37 AM.
Reason: oups ! Typos …
Re: Extract specific data from .txt file and put it into worksheet excel
Hi Lebbersmurf,
In my previous job I found myself in a similar situation, needing to extract data from a messy TXT output from a DOS application.
If the output file has always the same formatting/lenght, and you need extracting the information at fixed locations, all you need to do is record a macro. Clean up the file once and the next time just run the macro.
Re: Extract specific data from .txt file and put it into worksheet excel
You must change the header;
1) Remove space between chemical symbol and the number. e.g. Fe 2 should be Fe2 etc.
2) Ca1, Ca2 , Ca3, Ca3, Ca4 should be Ca1, Ca2 , Ca3, Ca4, Ca5
LinkBack URL
About LinkBacks
Register To Reply
Hi ! Try this ‼
Originally Posted by Lebbersmurf
Bookmarks