The following is an explanation of how we process returns files sent from SPX. This process generates an individual ticket for each order that is returned, resulting in easier processing through FreshDesk. Start by making a new folder titled "BatchMMDDYY". For example, "Batch100815" for October 8, 2015.


1. We'll start with the file that we get from SPX. These are CSV Files in plain text that can be parsed by either a text editor (e.g. Notepad) or by Excel. We will be using Excel for this. Copy the SPX file into the BatchMMDDYY folder.


 

2. Open the CSV file in Excel. Open Excel, and then open the file manually. Don't just drag the file into Excel; it will not be formatted correctly. Note: by default, Excel will not "see" the CSV files. You must select "All Files" from the dropdown in the bottom right hand corner of the file explorer when opening the file. Always choose "All Files" going forward so that you will be able to see the relevant file without problems.



3. Once the file is open, you will be presented with the following dialog box:



4. The only thing you need to do here is make sure that "Delimited" is selected. Hit Next. On the next page, de-select "Tab" and select "Other". Enter the Pipe symbol in the box provided (Shift + \), which looks like |.  This is the symbol SPX uses to delimit the different fields in the CSV file. Hit next once more and then hit finish. If you did everything right, you will have a spreadsheet in excel where each column represents one of the delimited categories in the file from SPX. See below:



5. Now we're going to run some Visual Basic (VB) code. To do this, open the VB editor by hitting Alt + F11 while in Excel. From here, click the "Insert" menu option and select "Module" from the dropdown list.



6. Now, paste in the following text:

 

 

 

 

Sub OrganizeReturn()
'
' OrganizeReturn Macro
'
 
'
activesheet.name = "Sheet1"
    Range("C:C,D:D,F:F,H:H,I:I,J:J,K:K").Select
    Range("K1").Activate
    Selection.ClearContents
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    ActiveCell.FormulaR1C1 = "=Con"
    Columns("A:A").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A2:E999")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("E:E").Select
    ActiveCell.FormulaR1C1 = "=CONCATENATE(C[-3], ""-"", C[-2], ""-"", C[-1])"
    Range("E1").Select
    Selection.AutoFill Destination:=Range("E1:E999")
    Range("E1:E999").Select
    Columns("E:E").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D:D,B:B").Select
    Range("B1").Activate
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("D:D,B:B,C:C").Select
    Range("C1").Activate
    Selection.ClearContents
    Selection.Delete Shift:=xlToLeft
    Range("A1").Select
Dim w1 As Worksheet, wR As Worksheet
Dim r As Long, lr As Long, nr As Long, n As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
wR.Range("A1").Resize(, 7).Value = w1.Range("A1").Resize(, 7).Value
wR.Range("B1").ColumnWidth = w1.Range("B1").ColumnWidth + 20
lr = w1.Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To lr
  nr = wR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  n = Application.CountIf(w1.Columns(1), w1.Cells(r, 1).Value)
  If n = 1 Then
    wR.Range("A" & nr).Resize(, 7).Value = w1.Range("A" & r).Resize(, 7).Value
  ElseIf n > 1 Then
    wR.Range("A" & nr).Resize(, 6).Value = w1.Range("A" & r).Resize(, 6).Value
    wR.Range("B" & nr) = Join(Application.Transpose(w1.Range("B" & r & ":B" & (r + n) - 1)), Chr(10))
  End If
  r = r + n - 1
Next r
wR.UsedRange.Columns.AutoFit
wR.UsedRange.Rows.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

 

 

 

 


Simply close the window with the macro in it by hitting the red X in the upper right hand corner

7. Finally, run the VB script we setup before. Hit Alt + F8 to open the macro dialog box and choose "DelColConcatData". Hit Run. You should see a big change in how the spreadsheet is formatted. Now, each order gets its own cell with the associated quantity returned


8. Now, you should have a spreadsheet that looks like this:


Note that each Order # on the left is associated with all of the items that were returned with it.

9. Next, we're going to save the file to our BatchMMDDYY folder as a "CSV (Comma Delimited)". Name it in this format: "ReturnBatchMMDDYYCSV". Say "Okay" when prompted. At this point you can close Excel, we don't need it anymore.

10. Now we're going to convert our CSV file (the one we just made) to a JSON. JSON is the format that Freshdesk requires for mass ticket uploading. Start by opening http://www.convertcsv.com/csv-to-json.htm.

11. Hit "Browse" and select the CSV file that you just made. Make sure that all of the other options in the box below match what is in this screen capture.


12. At the bottom of the page, click the box next to "Custom JSON via Template"


In the resulting dialog box, enter the template below so that it matches the picture EXACTLY.


 

{
"helpdesk_ticket":{
"description":"Items-Qty: \n {f2}",
"subject":"AutoReturn Order {f1}",
 "email":"autoreturn@puriumcorp.com",
"priority":1,
"status":2
 }
}

 


Note that there is nothing entered in the "Bottom" and "Top" boxes, and there are NO SPACES OR EMPTY LINES at the end of the template. It needs to look exactly like the picture above.

Click "Convert CSV to JSON via Template". You should see all of the orders in JSON format in the box at the bottom of the page. They will look like this. Each entry must be exactly 9 lines of text.

 

{
  	"helpdesk_ticket":{
    	"description":"Items-Qty: \n 11090M-1-\n9815M-2-\n8410-1-\n3551-2-\n2260-2-\n2208-2-\n2110-2-\n2060-2-\n15512-2-\n15511-1-",
     	"subject":"AutoReturn Order 599218",
        "email":"autoreturn@puriumcorp.com",
    	"priority":1,
   	"status":2
 }
},
{
  	"helpdesk_ticket":{
    	"description":"Items-Qty: \n 11090M-1-\n8410-1-\n3551-1-\n2260-1-\n2208-P65-1-\n2110-P65-1-\n2060-1-",
     	"subject":"AutoReturn Order 637006",
        "email":"autoreturn@puriumcorp.com",
    	"priority":1,
   	"status":2
 }
},

 

13. Now, rename the JSON array that we have created by entering the new name in the box where it says "convertcsv". The naming format for this is "ReturnBatch100815JSON". Save it to the same ReturnMMDDYY folder we've been using. 


14. Now we're going to delete every comma from the JSON file we just made. Open the file in Notepad and delete the indicated commas, but NOT the commas that are not indicated.


{
"helpdesk_ticket":{
"description":"Items-Qty: \n 11090M-1-\n9815M-2-\n8410-1-\n3551-2-\n2260-2-\n2208-2-\n2110-2-\n2060-2-\n15512-2-\n15511-1-",
"subject":"AutoReturn Order 599218",
"email":"autoreturn@puriumcorp.com",
"priority":1,
"status":2
}
}, <--Comma here must be deleted
{
"helpdesk_ticket":{
"description":"Items-Qty: \n 11090M-1-\n8410-1-\n3551-1-\n2260-1-\n2208-P65-1-\n2110-P65-1-\n2060-1-",
"subject":"AutoReturn Order 637006",
"email":"autoreturn@puriumcorp.com",
"priority":1,
"status":2
}
}, <--Comma here must be deleted



15. Once all the commas have been deleted and the changes have been saved, close the JSON file. You will need to create a new folder in our BatchMMDDYY folder. Name the new folder "IndividualRecords". This is where we are going to put the individual return order files that we are generating next. Open "Text File Splitter" (there's a shortcut on my desktop). For the "Text File to be Split", select the JSON file from which we removed those commas earlier. For the "Output Folder", select the "IndividualRecords" folder we created a moment ago. It needs to be totally empty for this program to work. After selecting these options, the UI should look like the picture below. If it does, hit start.


This will split the JSON file into numerous files, one for each order that is going to be uploaded to Freshdesk as a ticket.



Make sure that the process was successful by opening the "IndividualRecords" folder that you just filled with files and by checking a few of the files to make sure that they look right. They should still look like the examples above.

16. Last step: there is a .bat file attached to this solution. Simply place it into the IndividualRecords folder that we just made with all the individual orders in it and double click on it. It should start chugging away on each entry while it uploads them to Freshdesk. Important! Make sure that you closely observe the uploading process; if you notice that the terminal window looks like this at any point, that means that one of the files you were trying to upload failed. Usually that is the result of a comma that was left in the array. You will need to go back and check the original file that was split up to find out where the comma was and then re-upload that entry by hand.


17. After all that, go to Freshdesk and make sure that all the tickets uploaded. Just check the number of files in the IndividualRecords folder against the number of new returns. If the numbers match, you're almost certainly good to go.