kamur
asked on
Txt files to Excel report
Hello,
I have three txt files, say file1, file2 and file3.
File1 has format like:
Name1 Data1
Name2 Data2
Name3 Data3
Name 4 Data4
File2 also has the same format, just that sometimes Names could be more or less than file1
Name1 text1
Name3 text3
Name4 text4
Name5 text5
(here Name2 is missing, but Name5 is added)
File3 also has the same format, just that sometimes it can have Names that are more or less than file1 and file2.
Name1 variable1
Name2 variable2
Name6 variable6
(Name3, Name4, Name5 missing and Name6 added).
I want to capture all the Names present in all the three files and format it into one output file with,
Name, Data, Text, Variable columns and show these as 4 columns in an excel spreadsheet.
My output in excel will look like:
Name1 Data1 text1 variable1
Name2 Data2 - variable2
Name3 Data3 text3 -
Name4 Data4 text4 -
Name5 - text5 -
Name6 - - variable6
Please advise.
Thanks
I have three txt files, say file1, file2 and file3.
File1 has format like:
Name1 Data1
Name2 Data2
Name3 Data3
Name 4 Data4
File2 also has the same format, just that sometimes Names could be more or less than file1
Name1 text1
Name3 text3
Name4 text4
Name5 text5
(here Name2 is missing, but Name5 is added)
File3 also has the same format, just that sometimes it can have Names that are more or less than file1 and file2.
Name1 variable1
Name2 variable2
Name6 variable6
(Name3, Name4, Name5 missing and Name6 added).
I want to capture all the Names present in all the three files and format it into one output file with,
Name, Data, Text, Variable columns and show these as 4 columns in an excel spreadsheet.
My output in excel will look like:
Name1 Data1 text1 variable1
Name2 Data2 - variable2
Name3 Data3 text3 -
Name4 Data4 text4 -
Name5 - text5 -
Name6 - - variable6
Please advise.
Thanks
HI,
The other way u can do if u do not want use database then use array/collection for each file and load the data into it with one for final output.
U just have to add a logic to search in array/collection to not get duplicate.
Enjoy
anand
The other way u can do if u do not want use database then use array/collection for each file and load the data into it with one for final output.
U just have to add a logic to search in array/collection to not get duplicate.
Enjoy
anand
Some Remarks on this little procedure (copy and paste it: tools-macro-visual basic editor, doubleclick on this sheet)
1: dim strArray(5,3) --> it's possible to adjust the first number to the maximum in different names !
2: Open "c:\file1.txt" --> set to the correct path (also file2 & file3)
3: I assumed that's ok that I set the data from cell A1 until....
4: I assumed that the data in the textfiles where separated by a ";" (fe: name1;data1)
Sub ImportFiles()
Dim strArray(10, 3) As String
Dim sTextLine As String
Dim iFileNum As Integer
Dim i, j As Integer
Dim found As Boolean
i = 0
iFileNum = FreeFile
' IMPORT THE FIRST FILE INTO AN ARRAY
Open "c:\file1.txt" For Input As #iFileNum ' Open file.
Line Input #iFileNum, textline
Do While Not EOF(iFileNum) ' Loop until end of file.
' Read line into variable.
strArray(i, 0) = Left(textline, InStr(textline, ";") - 1)
strArray(i, 1) = Mid(textline, InStr(textline, ";") + 1)
i = i + 1
Line Input #iFileNum, textline
Loop
Close #iFileNum ' Close file.
' IMPORT THE SECOND FILE INTO AN ARRAY
iFileNum = FreeFile
Open "c:\file2.txt" For Input As #iFileNum ' Open file.
Do While Not EOF(iFileNum) ' Loop until end of file.
Line Input #iFileNum, textline ' Read line into variable.
j = 0
found = False
While j < UBound(strArray)
If strArray(j, 0) = Left(textline, InStr(textline, ";") - 1) And strArray(j, 0) <> "" Then
found = True
num = j
j = UBound(strArray)
End If
j = j + 1
Wend
If found = True Then
strArray(num, 2) = Mid(textline, InStr(textline, ";") + 1)
Else
strArray(i, 0) = Left(textline, InStr(textline, ";") - 1)
strArray(i, 1) = Mid(textline, InStr(textline, ";") + 1)
i = i + 1
End If
Loop
Close #iFileNum ' Close file.
' IMPORT THE THIRD FILE INTO AN ARRAY
iFileNum = FreeFile
Open "c:\file3.txt" For Input As #iFileNum ' Open file.
Do While Not EOF(iFileNum) ' Loop until end of file.
Line Input #iFileNum, textline ' Read line into variable.
j = 0
found = False
While j < UBound(strArray)
If strArray(j, 0) = Left(textline, InStr(textline, ";") - 1) And strArray(j, 0) <> "" Then
found = True
num = j
j = UBound(strArray)
End If
j = j + 1
Wend
If found = True Then
strArray(num, 3) = Mid(textline, InStr(textline, ";") + 1)
Else
strArray(i, 0) = Left(textline, InStr(textline, ";") - 1)
strArray(i, 3) = Mid(textline, InStr(textline, ";") + 1)
i = i + 1
End If
Loop
Close #iFileNum ' Close file.
i = 0
j = 0
While strArray(i, 0) <> "" And i < UBound(strArray)
For j = 0 To 3
Me.Cells(i + 1, j + 1) = strArray(i, j)
Next j
i = i + 1
Wend
End Sub
1: dim strArray(5,3) --> it's possible to adjust the first number to the maximum in different names !
2: Open "c:\file1.txt" --> set to the correct path (also file2 & file3)
3: I assumed that's ok that I set the data from cell A1 until....
4: I assumed that the data in the textfiles where separated by a ";" (fe: name1;data1)
Sub ImportFiles()
Dim strArray(10, 3) As String
Dim sTextLine As String
Dim iFileNum As Integer
Dim i, j As Integer
Dim found As Boolean
i = 0
iFileNum = FreeFile
' IMPORT THE FIRST FILE INTO AN ARRAY
Open "c:\file1.txt" For Input As #iFileNum ' Open file.
Line Input #iFileNum, textline
Do While Not EOF(iFileNum) ' Loop until end of file.
' Read line into variable.
strArray(i, 0) = Left(textline, InStr(textline, ";") - 1)
strArray(i, 1) = Mid(textline, InStr(textline, ";") + 1)
i = i + 1
Line Input #iFileNum, textline
Loop
Close #iFileNum ' Close file.
' IMPORT THE SECOND FILE INTO AN ARRAY
iFileNum = FreeFile
Open "c:\file2.txt" For Input As #iFileNum ' Open file.
Do While Not EOF(iFileNum) ' Loop until end of file.
Line Input #iFileNum, textline ' Read line into variable.
j = 0
found = False
While j < UBound(strArray)
If strArray(j, 0) = Left(textline, InStr(textline, ";") - 1) And strArray(j, 0) <> "" Then
found = True
num = j
j = UBound(strArray)
End If
j = j + 1
Wend
If found = True Then
strArray(num, 2) = Mid(textline, InStr(textline, ";") + 1)
Else
strArray(i, 0) = Left(textline, InStr(textline, ";") - 1)
strArray(i, 1) = Mid(textline, InStr(textline, ";") + 1)
i = i + 1
End If
Loop
Close #iFileNum ' Close file.
' IMPORT THE THIRD FILE INTO AN ARRAY
iFileNum = FreeFile
Open "c:\file3.txt" For Input As #iFileNum ' Open file.
Do While Not EOF(iFileNum) ' Loop until end of file.
Line Input #iFileNum, textline ' Read line into variable.
j = 0
found = False
While j < UBound(strArray)
If strArray(j, 0) = Left(textline, InStr(textline, ";") - 1) And strArray(j, 0) <> "" Then
found = True
num = j
j = UBound(strArray)
End If
j = j + 1
Wend
If found = True Then
strArray(num, 3) = Mid(textline, InStr(textline, ";") + 1)
Else
strArray(i, 0) = Left(textline, InStr(textline, ";") - 1)
strArray(i, 3) = Mid(textline, InStr(textline, ";") + 1)
i = i + 1
End If
Loop
Close #iFileNum ' Close file.
i = 0
j = 0
While strArray(i, 0) <> "" And i < UBound(strArray)
For j = 0 To 3
Me.Cells(i + 1, j + 1) = strArray(i, j)
Next j
i = i + 1
Wend
End Sub
ASKER
Dhaest,
I am getting the results as:
Name1 Data1 text1 variable1
Name2 Data2 variable2
Name3 Data3 text3
Name4 text4
Name5 text5
Name6 variable6
text4 and text5 are in wrong column.
Thanks
I am getting the results as:
Name1 Data1 text1 variable1
Name2 Data2 variable2
Name3 Data3 text3
Name4 text4
Name5 text5
Name6 variable6
text4 and text5 are in wrong column.
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Dhaest. It worked great.
Can I ask one more question on this...If I want to add a title at the top for each column, would that mess up the whole logic? Just a thought, not needed if it would be a major rewrite of the above code.
Thanks for the quick replies.
Regards
Can I ask one more question on this...If I want to add a title at the top for each column, would that mess up the whole logic? Just a thought, not needed if it would be a major rewrite of the above code.
Thanks for the quick replies.
Regards
You only have to adjust the last part of the code:
i = 0
j = 0
While strArray(i, 0) <> "" And i < UBound(strArray)
For j = 0 To 3
Me.Cells(i + 1, j + 1) = strArray(i, j)
Next j
i = i + 1
Wend
Change this: Me.Cells(i + 1, j + 1) = strArray(i, j)
Into: Me.Cells(i + 2, j + 1) = strArray(i, j)
i = 0
j = 0
While strArray(i, 0) <> "" And i < UBound(strArray)
For j = 0 To 3
Me.Cells(i + 1, j + 1) = strArray(i, j)
Next j
i = i + 1
Wend
Change this: Me.Cells(i + 1, j + 1) = strArray(i, j)
Into: Me.Cells(i + 2, j + 1) = strArray(i, j)
are you interest to use programming to combine it become one ?
if yes , then read in line by line from file save it into database with Name and data.
upon complete , read line by line from file 2, search the Name with database . if exist update the field call text otherwise append new record for such name and text.
upon complete file 2, read line by line from file 3 , search the Name with databse , if exist update the field call variable , otherwise append new record for such name and variable.
Hope can give ideal for you .