PDA

View Full Version : Solved: Text File Comparison Question



vzachin
11-17-2011, 11:37 AM
hi,

i have a text file that i receive. then i receive a newer version of the text file with added data. is there an easy way to extract the new data? the text file contains 50 columns and well over 100,000 + rows of data. i'm using Excel 2003.

i need to key in on the first column, which will contain either 8 or 11 characters to see what is added. each entry in this column is unique.

the newer file will always contain new data. all i need to know is what has been added.

if i have Excel 2010, this would not be a problem for me.

i have attached 2 examples for the old and new text files.

thanks

zach

mdmackillop
11-17-2011, 02:27 PM
I'm having trouble importing the text files. The spaces are not splitting the data into columns. Have you a small sample of a real file?

vzachin
11-18-2011, 08:20 AM
malcolm,

i have updated the files. hope this is better to use
thanks for looking at this

zach

vzachin
11-22-2011, 12:05 PM
hi,

i'm worried that my post has been overlooked. so i'm posting to push this back up the chain. i would really appreciate it if someone can tell me if my request is feasible or not.

thanks
zach

mohanvijay
11-23-2011, 01:13 AM
try this

this code works on first attached text files not second one



Dim O_Fi As FileDialog
Dim Txt_Path As String
Dim O_FSO As Object
Dim O_Txt As Object
Dim WS_Main As Worksheet
Dim WS_Addi As Worksheet
Dim Last_Rw As Long
Dim Fi_Rng As String
Dim T_Str As String, U_Val As String
Dim Hld_Data As Variant
Dim O_Rng As Range
Dim RW_Ctr As Long
Txt_Path = ""
Set O_Fi = Application.FileDialog(msoFileDialogFilePicker)
O_Fi.Filters.Add "Text Files", "*.txt", 1
O_Fi.Title = "Select New text file"
If O_Fi.Show = -1 Then
Txt_Path = O_Fi.SelectedItems(1)
End If
Set O_Fi = Nothing

Set O_FSO = CreateObject("Scripting.Filesystemobject")
Set O_Txt = O_FSO.opentextfile(Txt_Path)
Set WS_Main = ThisWorkbook.Sheets("Sheet1") 'change Sheet name to your suit
Last_Rw = WS_Main.Cells(Rows.Count, 1).End(xlUp).Row
Fi_Rng = "a1:a" & Last_Rw
RW_Ctr = Last_Rw + 1
If RW_Ctr > 65536 Then
Set WS_Addi = ThisWorkbook.Worksheets.Add
RW_Ctr = 1
Else
Set WS_Addi = WS_Main
End If
With O_Txt
.readline
.readline

Do Until .atendofstream = True

If RW_Ctr > 65536 Then
Set WS_Addi = ThisWorkbook.Worksheets.Add
RW_Ctr = 1
End If

T_Str = .readline

Hld_Data = Split(T_Str, Chr(9))

U_Val = CStr(Hld_Data(0))

Set O_Rng = WS_Main.Range(Fi_Rng).Find(U_Val, , , xlWhole)

If O_Rng Is Nothing Then

WS_Addi.Range("a" & RW_Ctr & ":ax" & RW_Ctr).Value = Hld_Data
RW_Ctr = RW_Ctr + 1

End If

Loop

.Close

End With
Set O_Txt = Nothing
Set O_FSO = Nothing
Set WS_Main = Nothing
Set WS_Addi = Nothing

vzachin
11-23-2011, 08:04 AM
mohanvijay, thanks for the reply.

i don't see how i can compare the file. all i need is what has been added

thanks
zach

mohanvijay
11-23-2011, 09:33 PM
first select the old text file all the item in old text wiil list and then
select new text file after the last row only new added records will display

vzachin
11-27-2011, 06:51 PM
mohanvijay,

i didn't understand it at first but now i do. thanks for your code.

zach