Solved: Reading data from .txt file to excel in the format as in attachment

[satya_c_dash]

Hi,

i am new to excel....... And i am looking to read data from a .txt file into sheet 1 named as Automate as in attachment......
Then do a look up taking valuee E2..E3 etc from automate and making a comparison with the data in lookup workbook
And then having the result work book in the format as in the attachment which is a combination of Automate and the result of the lookup done.

Would be nice if any1 in the group can help regarding this .
The attchment contains the input sample .txt file automate_sample_data.txt and the excel format i require with all the different values after doing the lookups.

Satya

[OBP]

I am not quite sure what you want to do, do you want to open the text file using Excel and get the result you show?

[mdmackillop]

Hi Satya,
Welcome to VBAX.
It will simplify things if the Picture column can be moved to the left of the spreadsheet. Can this be done?
Typically with imported data there are non-printing characters which mess up the vlookup. Test for this with =len(A1) etc. eg X(1) is 12 charaters in Automate and 4 characters in Lookup.

[satya_c_dash]

Hi Mack,

Got ur point ... But Picture is just a header in the automate spread sheet we can ignore it or we can add the same header in lookup spread sheet which ever is convienient

My requirements is to have a code which does the following
1)to read the .txt file into autoamate spread sheet
2)Do a lookup by picking data from the picture column of automate with that of look up spread sheet.(If it is convienient to have the picture column in the extreme left so be it )
3)And if found then have a result spread sheet in the format as in the attachment.

Hope u understood what i am trying to put across...

[satya_c_dash]

Can any1 in the group help me with my requirement.... the attachment is at the begining

My requirements is to have a code which does the following
1)to read the .txt file into autoamate spread sheet
2)Do a lookup by picking data from the picture column of automate with that of look up spread sheet.(If it is convienient to have the picture column in the extreme left so be it )
3)And if found then have a result spread sheet in the format as in the attachment.

[malik641]

Here ya go

Hope this is what you were looking for.

Just run the macro "ImportTextFile" and check out the formulas I have for ya

[satya_c_dash]

Yeah this is what i was looking for thank you very much for ur help MALIK641.

Would go through the code to get some hang abt coding in Excel......
Was of great help...
too good

[malik641]

Yeah this is what i was looking for thank you very much for ur help MALIK641.

Would go through the code to get some hang abt coding in Excel......
Was of great help...
too good

Your welcome


The code for the import I had in my Excel 2000 Power Programming with VBA, although I had to make some alterations. Here's some explanation on what's happening:

[VBA]'Every variable must be declared
Option Explicit

Sub ImportTextFile()
'Declare variables
Dim AutomateWS As Worksheet
Dim ImpRng As Range
Dim r As Long, c As Long, i As Long
Dim txt As String, char As String
Dim vData As Variant

Set AutomateWS = ActiveWorkbook.Sheets("Automate")
Set ImpRng = AutomateWS.Range("A1")

'Uses the active workbook's path to open the text file
'and opens the file.
Open ThisWorkbook.Path & "\automate_sample_data.txt" _
For Input As #1 'Input # is used to only read from the text file _
(reading each variable seperated by a comma) _
and not to right to it. #1 means it is one text file. _
If you were to use a second text file, it would _
be #2.

'Initial values
r = 0
c = 0
txt = ""

Application.ScreenUpdating = False
'Loops until End Of File of Input #1 is found.
Do While Not EOF(1)
'Each vData is an entire line of data
Line Input #1, vData
'From 1 to lentgh of data line + 1
For i = 1 To Len(vData) + 1
'Sets char to the individual character at i
char = Mid(vData, i, 1)
'Checks for a comma or if i has exceeded the length of vData
'and if yes then place txt (data string) into a cell, increasing
'the column number afterwards.
If char = "," Or i > Len(vData) Then
ImpRng.Offset(r, c) = txt
c = c + 1
txt = ""
Else
'Checks if char = "
If char <> Chr(34) Then txt = txt & Mid(vData, i, 1)
End If
Next i
'Sets column back to 0 and increases row by 1
c = 0
r = r + 1
Loop
Close #1

Application.ScreenUpdating = True

End Sub
[/VBA]
I forgot to mention that if the data itself includes a comma, the output will not be what you want.