Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need macro to import parts of Purchase Order in custom csv format into excel (Part 1)

Posted on 2012-09-17
20
Medium Priority
?
518 Views
Last Modified: 2012-09-25
I have sample csv format Order-34865510V-MincomCSV-edited.txt that is used for purchase order to our company.
The problem is that file contains too many information that we don't need.

As you can see, there's no header information, the format (from our buyer) is:
- PO Header (with 94 fields <fields started with 0> separated by , (comma))
- PO Item 1 (with 42 fields separate by ,)
- PO Item 2 etc
Note:
Asterisk (*) is used to separate each item.
Comma (,) is used for data separation
" for encapsulation and escape character.

From that csv files, I just need
- PO Order Number (field 1 just after PO Header) in this case 34865510V
- Store number (field 27) in this case 0524
- GTIN number for each products (field 18 after *POITEM),  for PO Product 1 : 2885730000005
- Weight of each products (field 6 after *POITEM), for Product 1 : 6

And considering I have so many purchase order, I'm thinking to put all csv files into the same folder and let the macro run through all csv files in that folder and after finished with it, move it to "processed" folder.

So in short,
- Need a macro to process all csv files in some directory (need user input for the directory name)
- Input the data into corresponding spreadsheets in the same workbook (Part 2)
- Move the processed csv files into a subfolder called "Processed on "today date""

Continue to part 2.
Book1.xlsx
0
Comment
Question by:m888
  • 11
  • 9
20 Comments
 
LVL 46

Expert Comment

by:aikimark
ID: 38410179
I didn't find an acceptable regexp pattern for this, so I'll suggest the following.
    'Note: strPO contains the CSV text you posted
    Dim strPO As String
    Dim strHeaderDetail() As String
    Dim lngLine As Long
    Dim strLineParsed() As String

    strHeaderDetail = Split(strPO, "*POITEM,")
    strLineParsed = Split(strHeaderDetail(lngLine), ",")
    Debug.Print "PO Order Number:", strLineParsed(1)
    Debug.Print "Store number:", strLineParsed(39)

    For lngLine = 1 To UBound(strHeaderDetail)
        strLineParsed = Split(strHeaderDetail(lngLine), ",")
        Debug.Print "Weight:", strLineParsed(5)
        Debug.Print "GTIN number:", strLineParsed(17)
    Next

Open in new window

0
 

Author Comment

by:m888
ID: 38412053
And how do I import all the csv files in a directory ?

I tried macro recording and ended up with this code
Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;G:\Download\IDM\Order_34865510V_MincomCSV_edited.txt", Destination:=Range( _
        "$A$1"))
        .Name = "Order_34865510V_MincomCSV_edited.txt"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 850
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileOtherDelimiter = "*"
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38412211
although you posted a workbook, it does not contain the data in your vendor file. I think there are some missing mappings in your problem description.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:m888
ID: 38412346
I'm sorry, but I don't understand what you meant by "missing mappings"

I'm new with this excel macro script, so if possible I need a complete macro script to solve Part1 and Part2 of my problem together.

The problem is:
- Need a macro to process all csv files in some directory (need user input for the directory name)
- Input the data into corresponding spreadsheets in the same workbook (Part 2)
- Move the processed csv files into a subfolder called "Processed on "today date""
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38413568
iterating files is not a problem, so I tackled the difficult part of the task first (parsing the data)

At this point, I'm looking at the second part of the problem in detail.
>>corresponding spreadsheets
In order to move data from the vendor file into worksheets, there needs to be a well defined 'mapping' of data in the vendor file and where it is to be placed in any particular worksheet. Since you use the word "corresponding", consider the details of that to be what I call "mapping"
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38414400
I've looked at your sample vendor files and the workbook you posted in the second part to this question.  

Will your worksheets contain the same GTIN values that are found in the POITEM segments?
Will there always be ten POITEM segments? (I've noticed the same GTIN values appearing in the four worksheets)
0
 

Author Comment

by:m888
ID: 38415989
yes. there will be same GTIN values for all stores.
But not all store will order the same item (unlike in the sample workbook) each time.
I did change the quantity ordered for each csv though.

And no, there won't be always ten POItem.
There will be a lot more, I just use ten because that's the smallest order we got (so easier to test?)
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38416207
>>so easier to test?

While it might be easier to test, the solution you get is likely to not be flexible.  When you post sample data, it should be representative of the problem, not cherry picked to be 'easy'.
0
 

Author Comment

by:m888
ID: 38416352
I uploaded another csv file containing 30 POItems with store id that matched with store 2.
Order-34898704V-MincomCSV-edited.txt
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38417944
How is the import process supposed to treat a 30 item list against the 10 item GTIN list in the worksheet?

=========
Since we are in part 1, I think it is time to suggest there may be a different way to think about importing the data.  Rather than doing a matching update, based on cell values, the import process might create worksheets from scratch.
0
 

Author Comment

by:m888
ID: 38418054
Actually in the actual workbook / sheets there will be enough GTIN data to compare it with.
We have 93 items in the actual workbook.
This is just a sample workbook similar with the actual one.
You will get more confused when you see actual workbook :)

As long as the macro works with the zip file. I'm happy to accept that as solution.
Should be able to change it to match the actual workbook (given time)
I am not keen on creating new worksheets because that's the format our employees & factory used to work with.
0
 

Author Comment

by:m888
ID: 38418058
Please ignore the csv file that contains 30 items :)
0
 

Author Comment

by:m888
ID: 38427619
aikimark, any news re this ?
just wondering since there's no reply from you since 3 days ago.

thank you if you are!
0
 
LVL 46

Accepted Solution

by:
aikimark earned 2000 total points
ID: 38428645
Open up your book1.xlsx workbook and open a new workbook.  Insert a module into the new workbook and add the following code.  Change the path in the string literal for the cVendorFilename constant to one of your vendor files.  Run the routine and inspect the results.

Option Explicit

Public Sub Q_27867545()
    'Merges data from single vendor file into worksheet(s), based on G1 data = store number
    Dim intFN As Integer
    Dim strVendorData As String
    Dim strHeaderDetail() As String
    Dim lngLine As Long
    Dim strLineParsed() As String
    Dim wks As Worksheet
    Dim rng As Range
    Const cVendorFilename As String = "C:\Users\AikiMark\Downloads\Imported CSV\Order_34865513V_MincomCSV_edited.txt"
    Dim strPO As String
    Dim strStore As String
    Dim dicItems As Object
    Const cWeight As Long = 1
    Const cGTIN As Long = 2
    
    Set dicItems = CreateObject("scripting.dictionary")
    
    intFN = FreeFile
    Open cVendorFilename For Input As #intFN
    strVendorData = Input(LOF(intFN), #intFN)
    Close #intFN
    
    strHeaderDetail = Split(strVendorData, "*POITEM,")
    strLineParsed = Split(strHeaderDetail(lngLine), ",")
    strPO = strLineParsed(1)
    strStore = strLineParsed(39)
    
    ReDim vPOItems(1 To UBound(strHeaderDetail), cWeight To cGTIN)
    For lngLine = 1 To UBound(strHeaderDetail)
        strLineParsed = Split(strHeaderDetail(lngLine), ",")
        dicItems.Add strLineParsed(17), strLineParsed(5)
    Next
    For Each wks In Workbooks("book1.xlsx").Worksheets
        If wks.Range("G1").Value = Val(strStore) Then
            wks.Range("C3").Value = strPO
            For Each rng In wks.Range(wks.Range("A6"), wks.Range("A6").End(xlDown)).Cells
                If dicItems.exists(rng.Text) Then
                    rng.Offset(0, 4).Value = dicItems(rng.Text)
                Else
                    Debug.Print "GTIN not found: " & rng.Value
                End If
            Next
            Exit For
        End If
    Next
    dicItems.RemoveAll
End Sub

Open in new window


Note: there can only be one Option Explicit statement

Having the code in a separate workbook allows you to close/open the book1.xlsx file between tests and to rerun the tests independently of any code changes you make.
0
 

Author Comment

by:m888
ID: 38431300
Thank you!
Inspecting it now.
0
 

Author Comment

by:m888
ID: 38431774
That's great. Thank you so much.
Managed to get it to work with my current workbook!

Need some update though.

- If there's a row between GTIN, the script stop working.
Is there any way to check for this ? It's not important though, can edit the worksheets to delete empty GTIN row (used for product category name)

- By "Note: there can only be one Option Explicit statement", do you mean I can't execute the same macro again after the first use? (basically closing and reopening the workbook before i use the macro?)

I don't think so, because based on google search that statement is used to make sure that all the variables are declared?
But just wanted to make sure.

- Need that multiple file import function if possible :)

- And also, after particular csv files have been done, they will be moved to subfolder called "Processed" and add current date in front of the file name. Example :
From G:\Puchase Order\Imported CSV\Order_35008850V_MincomCSV.txt
to G:\Puchase Order\Imported CSV\Processed\2012_09_todaydate_Order_35008850V_MincomCSV.txt

Other than that, it works beautifully!
Thanks again!
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38432417
When you posted a workbook, you did not include a worksheet with empty GTIN numbers.  Please post another workbook with representative examples of such missing/empty cells.

Depending on your VB project options, all new modules and forms may require variable declaration and include an Option Explicit statement.  Since my code includes that statement, I included the note that only one such statement was allowed.  It only affects variable declaration and not the number of times you can execute the routine.

What about the Quantity column?  Doesn't the vendor file include that data?

>>multiple files and post-processed handling
Since you created a second question, I'm going to address that part of your problem in the other question thread. It was more important to get the single file processing correct first.
0
 

Author Comment

by:m888
ID: 38432522
Don't worry about the missing cells.  But Basically it's the same worksheets with a row inserted between 2 items to put category label, but i will change it to the side so there won't be any missing row.

The weight is actually going to quantity column and then the weight column will be entered manually with actual weight. I've already modified the macro so it will go into the weight column.

So i think i will close this question and accepted your previous to last comment as the answer.
Awaiting the solution for part 2!
Thanks!
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38432538
Since you've changed the code, please post it in this thread.
0
 

Author Comment

by:m888
ID: 38432662
I just change this line

rng.Offset(0, 4).Value = dicItems(rng.Text)
         
To

rng.Offset(0, 3).Value = dicItems(rng.Text)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question