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.

Fordraiders

https://www.experts-exchange.com/questions/26701701/import-multiple-text-files-in-to-table.html?sfQueryTermInfo=1+10+30+file+fordraid+text

I don't know if this will help or not...but I did this in access awhile back..

fordraiders

ceneiqe

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.

ceneiqe

ASKER

I received message on INACTIVE THREAD for this.

I am still waiting for experts to respond !!!

Michael Fowler

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

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

Open in new window

Example-2.7.10--1-.xls

ceneiqe

ASKER

There is an error in
"Dim FSO As New FileSystemObject"

Message

Compile Error:
User-defined type not defined.

Michael Fowler

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

ceneiqe

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

ceneiqe

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.

ceneiqe

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

ASKER CERTIFIED SOLUTION

Michael Fowler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ceneiqe

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

SOLUTION

Michael Fowler

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ceneiqe

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.

SOLUTION

ceneiqe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Michael Fowler

great. Let me know if their are any further issues

Michael

ceneiqe

ASKER

I have increased your points from 125 to 200.

ceneiqe

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.