ceneiqe
asked on
Create a macro to compile data in multiple .txt files into one .xls file
Objective : to compile the data from text to excel, removing all unwanted data and columns for analysis.
txt files:
1) Example-2.7.10--1-.txt
2) Example-2.7.10--more-than-1-.txt
Excel file :
Worksheets in "Example 2.7.10 (1).xls"-see attached:
Example-2.7.10--1-.xls
1. "Example 2.7.10 (1)" = raw format, without clearing out the unwanted fields.
2. "Example 2.7.10 (final results)" - already formatted, what the final results should be displayed. (can also refer to "Example 2.7.10 (1)" to see where the fields are derived from)
3. Similarly when there are more than 1 records in a text file, it will be compiled like what is shown in "Example 2.7.10 (final results2)" and the data adds on.
(NOTE : each date will have 1 txt file and there can be more than 1 records in 1 txt file, meaning more than 1 records per date. So example, I can have 60 txt files- 2 months of txt files and then the records are compiled in an excel sheet)
4. Unique keys = Combination of (Ordernr + line + item)
ie. there should not be a row that repeat the same (Orderrnr + line + item)
You may also wish to refer to :
1. https://www.experts-exchange.com/questions/27230653/Create-a-macro-to-convert-multiple-txt-file-to-xls-file-and-place-all-data-in-xls-file-PART-2.html
2. https://www.experts-exchange.com/questions/27235569/Create-a-macro-to-convert-multiple-txt-file-to-xls-file-and-place-all-data-in-xls-file-PART-3-Columns.html
where my objective is the same but format of the .txt files differ.
txt files:
1) Example-2.7.10--1-.txt
2) Example-2.7.10--more-than-1-.txt
Excel file :
Worksheets in "Example 2.7.10 (1).xls"-see attached:
Example-2.7.10--1-.xls
1. "Example 2.7.10 (1)" = raw format, without clearing out the unwanted fields.
2. "Example 2.7.10 (final results)" - already formatted, what the final results should be displayed. (can also refer to "Example 2.7.10 (1)" to see where the fields are derived from)
3. Similarly when there are more than 1 records in a text file, it will be compiled like what is shown in "Example 2.7.10 (final results2)" and the data adds on.
(NOTE : each date will have 1 txt file and there can be more than 1 records in 1 txt file, meaning more than 1 records per date. So example, I can have 60 txt files- 2 months of txt files and then the records are compiled in an excel sheet)
4. Unique keys = Combination of (Ordernr + line + item)
ie. there should not be a row that repeat the same (Orderrnr + line + item)
You may also wish to refer to :
1. https://www.experts-exchange.com/questions/27230653/Create-a-macro-to-convert-multiple-txt-file-to-xls-file-and-place-all-data-in-xls-file-PART-2.html
2. https://www.experts-exchange.com/questions/27235569/Create-a-macro-to-convert-multiple-txt-file-to-xls-file-and-place-all-data-in-xls-file-PART-3-Columns.html
where my objective is the same but format of the .txt files differ.
ASKER
thanks , but not really. cos i believe the text files in your questions are properly aligned while miine is not and needs the help of macro to make it in one line and then put all data in excel.
Most important thing is to get all the data i need in 1 line and then compile together.
Whether in excel or access doesn't matter since i can copy the data (or export) in access into excel if it is done in access.
Most important thing is to get all the data i need in 1 line and then compile together.
Whether in excel or access doesn't matter since i can copy the data (or export) in access into excel if it is done in access.
ASKER
I received message on INACTIVE THREAD for this.
I am still waiting for experts to respond !!!
I am still waiting for experts to respond !!!
Here is a macro that works with your example file.
You will need to tweak the mid functions to extract the maximum length of each field to ensure all data is picked up
Michael
You will need to tweak the mid functions to extract the maximum length of each field to ensure all data is picked up
Michael
Option Explicit
Sub getData()
Dim FSO As New FileSystemObject
Dim textFile As TextStream
Dim textLine As String
Dim currentRow As Long
Dim count As Long
' Get file name from the user
Dim fileName As String
fileName = getFileName
' Get first empty row
currentRow = Range("A" & Rows.count).End(xlUp).Row + 1
Set textFile = FSO.OpenTextFile(fileName)
count = 0
' Loop through each line of text
Do Until textFile.AtEndOfStream
textLine = textFile.ReadLine
If textLine <> "" Then
If Left(textLine, 2) = "of" Then
Range(DTE & currentRow).Value = CDate(Mid(textLine, 10, 11))
ElseIf count = 1 And Left(textLine, 3) <> "---" Then
Range(SA & currentRow).Value = Trim(Left(textLine, 3))
Range(PACK & currentRow).Value = Trim(Mid(textLine, 4, 9))
Range(ORDER & currentRow).Value = Trim(Mid(textLine, 14, 10))
Range(LINE & currentRow).Value = Trim(Mid(textLine, 25, 5))
Range(ITEM & currentRow).Value = Trim(Mid(textLine, 31, 3))
Range(COL1 & currentRow).Value = Trim(Mid(textLine, 57, 2))
Range(COL2 & currentRow).Value = Trim(Mid(textLine, 60, 2))
Range(SASS & currentRow).Value = Trim(Mid(textLine, 64, 10))
Range(REQ & currentRow).Value = Trim(Mid(textLine, 76, 3))
Range(DEL & currentRow).Value = Trim(Mid(textLine, 80, 2))
currentRow = currentRow + 1
ElseIf Left(textLine, 3) = "---" Then
count = count + 1
If count > 1 Then Exit Do
End If
End If
Loop
End Sub
Function getFileName() As String
Dim fd As FileDialog
Dim selection As Variant
Dim fileName As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.AllowMultiSelect = False
If .Show = -1 Then
getFileName = .SelectedItems(1)
Else
getFileName = "-1"
End If
End With
End Function
Example-2.7.10--1-.xls
ASKER
There is an error in
"Dim FSO As New FileSystemObject"
Message
Compile Error:
User-defined type not defined.
"Dim FSO As New FileSystemObject"
Message
Compile Error:
User-defined type not defined.
The error you have described just means the reference is missing, I am guessing you copied my code into a new workbook.
To fix this select Tools>References in visual basic editor and check the box for Microsoft Scripting Runtime
Michael
To fix this select Tools>References in visual basic editor and check the box for Microsoft Scripting Runtime
Michael
ASKER
but i tried several times and there is this error:
Message
Compile Error:
User-defined type not defined.
the following is highlighted :
Function getFileName() As String
Dim fd As FileDialog
Message
Compile Error:
User-defined type not defined.
the following is highlighted :
Function getFileName() As String
Dim fd As FileDialog
ASKER
Ok now it worked after downloading your revised .xls instead of just copying the code.
i realised you have 2 modules in your revised .xls.
but i can only click on selected txt file which is very time consuming.
i have about 200 txt files so it is not possible to click on just 1 file.
i was thinking of just chossing the folder where all the txt files resides and then all data will populate into excel.
i realised you have 2 modules in your revised .xls.
but i can only click on selected txt file which is very time consuming.
i have about 200 txt files so it is not possible to click on just 1 file.
i was thinking of just chossing the folder where all the txt files resides and then all data will populate into excel.
ASKER
and also the date should follow if the txt file contain more than 1 data.
for example :
Unit...................... .......... .Date
AUS .......................... .... 02/07/2010
NZL .......................... ..... date did not appear
it should be:
AUS .......................... .... 02/07/2010
NZL .......................... .... 02/07/2010
for example :
Unit......................
AUS ..........................
NZL ..........................
it should be:
AUS ..........................
NZL ..........................
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, but :
1. The full item name is not shown - only the 1st 3 characters which is not right.
2. For some lines, the corresponding req and del qty are truncated.
see attached Example 10.03.11 (more than 1).txt file
Example-10.03.11--more-than-1-.txt
truncated results:
Example-2.7.10--1-.xls
1. The full item name is not shown - only the 1st 3 characters which is not right.
2. For some lines, the corresponding req and del qty are truncated.
see attached Example 10.03.11 (more than 1).txt file
Example-10.03.11--more-than-1-.txt
truncated results:
Example-2.7.10--1-.xls
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks, let me check the codes again and revert.
As for the points, no worries, i will usually add more at the end when the solution is accepted when i think there is more work done than required.
As for the points, no worries, i will usually add more at the end when the solution is accepted when i think there is more work done than required.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
great. Let me know if their are any further issues
Michael
Michael
ASKER
I have increased your points from 125 to 200.
ASKER
i accepted one of my comments (without pts) as part of the solution as the data can be extracted completely with the slight change in the code.
I don't know if this will help or not...but I did this in access awhile back..
fordraiders