#1
|
|||
|
|||
importing and xonditioning data from a text file
Hello everyone.
I searched over the forum for a solution but couldn’t find one. I have a text file that is updated daily by adding more rows. This is how the text file looks,except there are hundreds of rows: When importing this data into EXCEL I only need certain values to appear from each row(CD,UNIT,Oper,VAL,DateTime)so I manually delete most of the columns to get just the data I need,like this: What is the best way to automate this process? Will appreciate your help. |
#2
|
|||
|
|||
Can you post a sample workbook with a dozen or so records ?
On one sheet what you're starting with and on another sheet what you want to end up with. Thanks |
#3
|
|||
|
|||
Thanks for reply,
TXT is original file and XLSX is the file I am trying to get. |
#4
|
|||
|
|||
Try giving this a test run.
I used the macro recorder for importing the text file using semicolon and equal sign as delimiters. Then wrote a little macro to delete some columns, split the date/time to adjacent cells and delete the original date/time column. Paste this into a standard module of a new workbook. You'll need to adjust for your own text file location. Code:
Sub ImportTextFile() ' ' as recorded by macro recorder ' Application.ScreenUpdating = False With ActiveSheet.QueryTables.Add(Connection:= _ "TEXT;D:\Forum Stuff\2018\2018_03_01\norcross\uroven121.txt", Destination:= _ Range("$A$1")) .Name = "uroven121_5" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 437 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = True .TextFileCommaDelimiter = False .TextFileSpaceDelimiter = False .TextFileOtherDelimiter = "=" .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With Call Manipulate Application.ScreenUpdating = True End Sub Private Sub Manipulate() Dim lr As Long Dim cl As Range With ActiveSheet .Range("A:C,E:G,I:I,K:K,M:S").Delete Shift:=xlToLeft lr = .Cells(Rows.Count, "E").End(xlUp).Row For Each cl In .Range("E1:E" & lr) cl.Offset(, 1).Resize(, 3).Value = Split(Trim(cl.Value), " ") Next cl .Columns("E").Delete Shift:=xlToLeft End With End Sub |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need help to extract specific data from text file using vba | victor92 | Excel Programming | 0 | 12-01-2017 12:53 AM |
Importing Data from seperate closed excel file | KingoftheKassel | Excel Programming | 1 | 06-10-2016 08:34 AM |
An error occurred while importing this file <image file> | melvinjn | Drawing and Graphics | 1 | 01-19-2015 01:32 AM |
How to convert a text file to an Excel file with the data format automatically? | mradmin | Excel | 6 | 10-16-2013 10:34 AM |
extracting data from Table to text file | Anirudh_Dsp | Word Tables | 1 | 05-23-2010 07:48 AM |