Dear all,

Is it possible to load partially a text file depending on specific
criteria in it ?

Example:

text file contains (delimitor is ^)

aaaaa^bbbbbbb^ccccc^^eeeee^fffff
rrrrrr^eeee^ssssss^bbbb^jjjjj^ooooooo
kk^^mmm^hhhhhhhhhhhhhh^uuuuuuu^wwwwww
xxx^pppp^qqqq^bbbb^yyyyy^kkkkk

I want to load into excel only the records that contains "bbbb" in the
4th field (column)
so in this example only record 2 and 4 should be loaded.

Any way to achieve this ?

Many thanks in advance !

Ronald.

Maybe you can open the text file into another workbook/worksheet (just use
file|Open). Then you could parse your data (delimited) to separate the fields.

Add a header row if you need to.

Then apply data|filter|autofilter and show only the bbbb rows. C

Then copy those visible cells to the real location.

Close the imported workbook without saving.

" wrote:

Dear all,

Is it possible to load partially a text file depending on specific
criteria in it ?

Example:

text file contains (delimitor is ^)

aaaaa^bbbbbbb^ccccc^^eeeee^fffff
rrrrrr^eeee^ssssss^bbbb^jjjjj^ooooooo
kk^^mmm^hhhhhhhhhhhhhh^uuuuuuu^wwwwww
xxx^pppp^qqqq^bbbb^yyyyy^kkkkk

I want to load into excel only the records that contains "bbbb" in the
4th field (column)
so in this example only record 2 and 4 should be loaded.

Any way to achieve this ?

Many thanks in advance !

Ronald.

--

Dave Peterson

First of all many thanks for you reply!

I'm aware of that option.
Int that case you need to load all of the records.
My idea was, if you can decide at the moment you load the record, if
it should be put in the sheet or not it will decrease the 'mass load'
when the text file contains a lot of records...

regards,

Ronald.

open the text file, read it in line by line, use Split(line,"^") to create
an array, check the 4th value

dim arr as variant


'read line
arr = Split(sLine, "^")

'array is zero-based so check element 3
if arr(3) = "bbbb" then
'write to sheet
end if
'loop



Tim



wrote in message
ups.com...
Dear all,

Is it possible to load partially a text file depending on specific
criteria in it ?

Example:

text file contains (delimitor is ^)

aaaaa^bbbbbbb^ccccc^^eeeee^fffff
rrrrrr^eeee^ssssss^bbbb^jjjjj^ooooooo
kk^^mmm^hhhhhhhhhhhhhh^uuuuuuu^wwwwww
xxx^pppp^qqqq^bbbb^yyyyy^kkkkk

I want to load into excel only the records that contains "bbbb" in the
4th field (column)
so in this example only record 2 and 4 should be loaded.

Any way to achieve this ?

Many thanks in advance !

Ronald.

Sub loadBBB(textFile As String, sheet As Worksheet)
Dim InputData As String
Dim i As Long
Dim sArray As Variant
Open textFile For Input As #1
i = 1
While Not EOF(1)
Line Input #1, InputData
sArray = Split(InputData, "^")
If "bbbb" = sArray(3) Then
sheet.Cells(i, 1).Value = InputData
i = i + 1
End If
Wend
Close #1
End Sub

Sub testLoadBBB()
Dim book As Workbook
Dim sheet As Worksheet
Set book = ThisWorkbook
Set sheet = book.Sheets("Sheet1")
sheet.Select
Selection.Clear
Dim filePath As String
filePath = "C:\code\input_file.txt"
loadBBB filePath, sheet
End Sub

Use a query, with suitable SQL SELECT.

NickHK

wrote in message
ups.com...
Dear all,

Is it possible to load partially a text file depending on specific
criteria in it ?

Example:

text file contains (delimitor is ^)

aaaaa^bbbbbbb^ccccc^^eeeee^fffff
rrrrrr^eeee^ssssss^bbbb^jjjjj^ooooooo
kk^^mmm^hhhhhhhhhhhhhh^uuuuuuu^wwwwww
xxx^pppp^qqqq^bbbb^yyyyy^kkkkk

I want to load into excel only the records that contains "bbbb" in the
4th field (column)
so in this example only record 2 and 4 should be loaded.

Any way to achieve this ?

Many thanks in advance !

Ronald.

Tim, Gimme,

Many thanks for your help, will test this right away !!

NickHK,

Sorry, don't have sql functionality on excel....

Yes you do. dataImport External DataNew Database Query

NickHK

"Ronald" wrote in message
ps.com...
Tim, Gimme,

Many thanks for your help, will test this right away !!

NickHK,

Sorry, don't have sql functionality on excel....

Tim, Gimme,

Works perfect, thanks !!!
It didn't at the beginning but that was because the array (sArray)
starts at 0 and not at 1.
Therefor my criteria was on the field before the one I wanted to
check.

again, many thanks !

Ronald.