Results 1 to 7 of 7

Thread: Extrapolate Data from 3 CSV files using a Key field in a 4th CSV file. (Newbie with little knowledge)
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Mar 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extrapolate Data from 3 CSV files using a Key field in a 4th CSV file. (Newbie with little knowledge)

    Hi All, i really hope someone can help me with this as it means i can stop wasting 3 hours a night doing it manually.

    I have 3 CSV files that are price files and have fields such as Part Number and Quantity.
    I have 1 CSV file that is my upload file with the same fields but showing data from the 3 price files.

    I need to update the quantity field in my upload file using the figures in the 3 price files. The way i would prefer it to be done is by having the manufacturer part number acting as the key between the Upload File and the Price File and if the fields match insert the quantity in the Price file into the Upload file.

    The purpose behind this is that i have 3 different distributors of products who stock holding changes on a daily basis so i need to ensure our website is always up to date with the correct information on the quantities of different product available.

    Is there someway i can do this in an automated fashion? If so can someone explain it to me in small words as i know nothing about macros or how to insert, save, run, configure etc etc.

    Any and all help to get my life back in the evenings would be greatly appreciated.

    NB: The total amount of products between the 3 price files is around 10,000 line items with around 2-300,000 units of stock that changes massivly on a daily basis.

    I can include sample files if it helps.

    Martin

  2. #2
    Board Regular
    Join Date
    Jul 2014
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extrapolate Data from 3 CSV files using a Key field in a 4th CSV file. (Newbie with little knowledge)

    hi,
    this can be done with a macro, could you post small tables(please put in table format) holding some sample/dummy data? include the row numbers and columns too. Maybe this would be a lot of work, if you can upload some files or pm them this would be great.
    Another question, when do you want this to be activated? manually, a button, upon opening workbook,...

  3. #3
    New Member
    Join Date
    Mar 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extrapolate Data from 3 CSV files using a Key field in a 4th CSV file. (Newbie with little knowledge)

    Quote Originally Posted by Dendro View Post
    hi,
    this can be done with a macro, could you post small tables(please put in table format) holding some sample/dummy data? include the row numbers and columns too. Maybe this would be a lot of work, if you can upload some files or pm them this would be great.
    Another question, when do you want this to be activated? manually, a button, upon opening workbook,...
    Upload File (.CSV)

    Part Number Qty
    abcde#123 9
    12345#abc 56
    zyx321#b21 73


    Price File 1 (.CSV)

    Part Number Qty
    abcde#123 0
    xxxxxxxxxxx xxx
    xxxxxxxxxxx xxx


    Price File 2 (.CSV)

    Part Number Qty
    12345#abc 389
    xxxxxxxxx xxx
    xxxxxxxxx xxx


    Price File 3 (.CSV)

    Part Number Qty
    zyx321#b21 1
    xxxxxxxxx xxx
    xxxxxxxxx xxx



    So above are examples of the Upload File (which is the file that needs to be updated from the Price Files) and the Price Files from 3 different distributers.
    The upload file has ALL the Part Numbers and Quantities from all 3 Price Files making circa 10,000 Rows (line items).

    What i need to be able to do is update the quantity field in the Upload file from the Price Files using Part Number as the Primary Key. The Qty field varies significantly daily so i have to update this myself manually every day and it takes hours and hours of my evening to get it ready for the next day.

  4. #4
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,376
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extrapolate Data from 3 CSV files using a Key field in a 4th CSV file. (Newbie with little knowledge)

    Is there a reason why you can't just combine the three price files and upload the result?

    Why do you have to transfer the data to an "Upload File"?

  5. #5
    New Member
    Join Date
    Mar 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extrapolate Data from 3 CSV files using a Key field in a 4th CSV file. (Newbie with little knowledge)

    [QUOTE=Comfy;4110208]Is there a reason why you can't just combine the three price files and upload the result?

    Why do you have to transfer the data to an "Upload File"?[/QUOTE


    Yes. The reason is that i need to remove all items that are showing 0 stock before upload. I cant just delete them from the price file if they are showing as 0 as this wont update the main upload file and the upload file will still show stock in stock.

    The variation between out of stock, new stock, end of life, can be several thousand a day so that is why i have been doing it manually. but if i can automate it then the upload file is easier to manage. What i can do with the upload file is reset all stock to 0. Use whatever process we figure out on here to up date the stock quantity fields from the Price files and then i can sort the upload file by Number In Stock and see what items are at 0 and the ones unlikely to be replenished i can remove making space for alternate stock.

  6. #6
    Board Regular
    Join Date
    Jul 2014
    Posts
    336
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extrapolate Data from 3 CSV files using a Key field in a 4th CSV file. (Newbie with little knowledge)

    how is the output calculated, can you adjust the numbers for the example?
    Upload File (.CSV)

    before:

    Part Number Qty
    abcde#123 9
    12345#abc 56
    zyx321#b21 73

    after, based on the price1-3 files you gave in the example:
    Upload File (.CSV)

    Part Number Qty
    abcde#123 ?
    12345#abc ?
    zyx321#b21 ?

  7. #7
    Board Regular
    Join Date
    Dec 2009
    Location
    Canterbury, UK
    Posts
    3,376
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extrapolate Data from 3 CSV files using a Key field in a 4th CSV file. (Newbie with little knowledge)

    Are you able to rename the files as you please?

    Either way we can setup a query to pull all the data from the three files into one sheet in your workbook.

    You can then use a vlookup etc to pull the data into your "Upload" sheet.

    Because MS Query is not very nice (IMO) we can create the initial query using VBA.

    I recorded this:

    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    '
    
    
    Dim file1 As String
    Dim file2 As String
    Dim file3 As String
    
    
    file1 = "`H:`\CSV1.csv" 'Note the use of ` before and after the directory
    file2 = "`H:`\CSV2.csv" 'Note the use of ` before and after the directory
    file3 = "`H:`\CSV3.csv" 'Note the use of ` before and after the directory
    
    
        With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DefaultDir=C:\;Driver={Driver da Microsoft para arquivos texto (*.txt; *.csv)};DriverId=27;Extensions=txt,csv,tab,asc;" & _
                "FIL=text;MaxBufferSize=2048;MaxScanRows=25;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;", Destination:=Range("$A$1")).QueryTable 'No idea why the driver is in Portuguese?
            .CommandText = "SELECT * FROM " & file1 & " UNION ALL SELECT * FROM " & file2 & " UNION ALL SELECT * FROM " & file3
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = "CSV_Data"
            .Refresh BackgroundQuery:=False
        End With
    End Sub
    Which should be enough for this.


    1. Make the appropriate changes to the code (file names locations etc)
    2. Run the code on a blank sheet
    3. Everytime you want to import new data click Refresh All (providing the latest files are in the same place with the same name.
    Last edited by Comfy; Mar 24th, 2015 at 07:27 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •