PDA

View Full Version : get data from txt file



jaango123
11-10-2010, 06:44 AM
Hi all,
Can anyone help me in the below automation.

What we expect is, from the above txt file the values between pipe symbol(it can be blank/no value) should go in consecutive columns in an excel sheet. The sample expected output in the excel for the first line of txt file is also uploaded. Like this the second row should continue with the next data from the text file. Any help/hint is highly appreciated.

p45cal
11-10-2010, 08:04 AM
I recorded a macro and made slight adjustments:Sub blah()
Sheets.Add After:=Sheets(Sheets.Count)
Set qtbl = ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Documents and Settings\JohnDoe\My Documents\ErrorPolicy\Sampletext.txt" _
, Destination:=Range("$A$1"))
With qtbl
.Name = "Sampletext"
.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 = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(9, 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) 'added a few 1s to allow for more fields.
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
qtbl.Delete
End Sub
This adds a new sheet and loads it with data, skipping the data on each line before the first pipe symbol. You will probbly want to tweak it to allow the user to choose which file to use, or to allow the user to choose a folder and load all the text files into separate sheets. A start, anyway.

jaango123
11-10-2010, 11:15 PM
Thanks a lot. I will check on this.

amateur
11-15-2010, 11:30 AM
I solved your problem using MID() and FIND() functions.
Start the macro and browse the text file containing your datas.

4905