Cleaning up shipping files (2002)

Topic

New Reply

I

Reply | Quote

Replies

Arage

The short answer is YES . The slightly longer one is MAYBE .

It all rather depends on what needs to be achieved specifically and how strongly structured your data is.
Can you post a (sanitized if necessary) example of what you receive, and what you want it to become.

Reply | Quote

Hi Andrew,
Here

Reply | Quote

OK – should be straightforward.
A design question though.
Presumably your ideal solution would automatically detect the right kind of attachment – if so, how?

Reply | Quote

I can only assume that there is something in code that could make such a specification.

Reply | Quote

Arage

OK – try the following code.
Cut and paste this to the Outlook “ThisOutLookSession” code space.
Note that by default Outlook disables macros and doesn’t tell you it has – you’ll need to enable them to at most ‘Medium’ security.

I’ve had to make some assumptions for the purposes of design that you will need to review.

This routine triggers on the NEWMAIL event, and looks for mail with file attachments
If it sees file types CSV TXT or 001 it then asks if it should convert them.
You can change the file types looked for by modifying the variable ‘MyExts’ – you can add more by just typing .EXT inside the quotes where EXT is your new extension.
For testing I also used directory C:TEMP to create reformatted files in – change MyPath as needed for your network drive.
I have coded changes to data as follows –
Column 2 has 001 added
Columns 4 & 5 have quotes
Column 4 has R00 at front
Column 5 has S045 at front
The LAST column has date extended. Note that I specifically coded it as the last, rather than 10th column because this protects the code if you text fields ever contain commas
The program is designed so that similar changes could be made without a big deal

Public Myolapp As Variant
Public I As Long
Public J As Long
Public MyExt As String
Public MyExts As String
Public MyFile As String
Public MyPath As String

Private Sub Application_NewMail()

‘ Identifies incoming mail that has an attachment that matches our target type
‘ if so, asks, and translates it to new format and save

‘ Change the following two constants as required to get things working right
‘ First is simply a list of valid extensions to trigger reformating action

MyExts = “.CSV.TXT.001”

‘ Second is the path for re-formatted files to go to

MyPath = “C:TEMP”

‘ Preliminary setup to view new mail

Set Myolapp = CreateObject(“Outlook.Application”)
Set myNameSpace = Myolapp.GetNamespace(“MAPI”)
Set Myolapp.ActiveExplorer.CurrentFolder = _
myNameSpace.GetDefaultFolder(olFolderInbox)

‘ Identify unread mail and deal with it

With Myolapp.ActiveExplorer.CurrentFolder
For I = 1 To .Items.Count
If .Items(I).UnRead = True Then
For J = 1 To .Items(I).Attachments.Count
MyFile = .Items(I).Attachments(J).DisplayName
MyExt = Right(MyFile, 4)
If InStr(1, MyExts, MyExt, vbTextCompare) > 0 Then
If MsgBox(“Reformat file ‘” & MyFile & “‘?”, buttons:=vbOKCancel) = 1 Then
MyFile = MyPath & “” & Left(MyFile, Len(MyFile) – 4) & “._B4”
.Items(I).Attachments(J).SaveAsFile MyFile
Reformat MyFile
End If
.Items(I).UnRead = False
End If
Next J
End If
Next I
End With

End Sub
Sub Reformat(target As String)
Dim I As Long
Dim J As Long
Dim DateCol As Long
Dim MyRec As String
Dim MyFields(100) As String

‘ This would have been easier in Excel but it cannot refrain from re-formating quotes
‘ so – lets just do it the hard way
‘ Just read the file line by line, break it out into fields, and then rewrite

Open target For Input As #1
Open Left(target, Len(target) – 4) & “.CSV” For Output As #2
Line Input #1, MyRec
While Not EOF(1)
‘ it is easier and less bug-prone to split into fields than try to be clever
J = 1
I = InStr(1, MyRec, “,”)
While I > 0
MyFields(J) = Left(MyRec, I – 1)
MyRec = Mid(MyRec, I + 1)
J = J + 1
I = InStr(1, MyRec, “,”)
Wend
MyFields(J) = MyRec

‘ This is the section that makes the requested changes

MyFields(2) = Left(MyFields(2), Len(MyFields(2)) – 1) & “001”””
MyFields(4) = “””R00” & MyFields(4) & “”””
MyFields(5) = “””S045” & MyFields(5) & “”””
‘ Set the column for date as the last column.
‘ Why? to protect from possible commas in the text fields
MyFields(J) = Left(MyFields(J), Len(MyFields(J)) – 3) & “20” & Right(MyFields(J), 3)

‘ then we put it all back together and write it out
For I = 1 To J – 1
Print #2, MyFields(I); “,”;
Next I
Print #2, MyFields(J)
Line Input #1, MyRec
Wend
Close #1
Close #2

End Sub

Reply | Quote

Question: despite the attachment suffix (csv, txt,001), they are all comma seperated text files?
if this is true then you can treat them the same way, the “type” doesn’t matter, you can write somethig to read and edit all of them

Hope this helps

Reply | Quote

Yes, save for the errors that occur, all values are comma separated. A few more of the types of error I need to know if I can fix this way include:

-deleting an entire record (b/c it contains shipping info for an item my company doesn

Reply | Quote

Oops

Reading between the lines of this message I detect that the changes requested are not as ‘mindless’ as I coded.

I begin to think that things like “S045” only have to be added if they are not already there.

My suggestion is that we send direct emails for the fine-tuning – rather than bother the board.

Reply | Quote