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

[TinTin72]

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

 

[Dendro]

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,...

 

[TinTin72]

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)

[TABLE="width: 200"]
<tbody>[TR]
[TD]Part Number[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]abcde#123[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]12345#abc[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]zyx321#b21[/TD]
[TD]73[/TD]
[/TR]
</tbody>[/TABLE]


Price File 1 (.CSV)

[TABLE="width: 200"]
<tbody>[TR]
[TD]Part Number[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]abcde#123[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]xxxxxxxxxxx[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]xxxxxxxxxxx[/TD]
[TD]xxx[/TD]
[/TR]
</tbody>[/TABLE]


Price File 2 (.CSV)

[TABLE="width: 200"]
<tbody>[TR]
[TD]Part Number[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]12345#abc[/TD]
[TD]389[/TD]
[/TR]
[TR]
[TD]xxxxxxxxx[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]xxxxxxxxx[/TD]
[TD]xxx[/TD]
[/TR]
</tbody>[/TABLE]


Price File 3 (.CSV)

[TABLE="width: 200"]
<tbody>[TR]
[TD]Part Number[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]zyx321#b21[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]xxxxxxxxx[/TD]
[TD]xxx[/TD]
[/TR]
[TR]
[TD]xxxxxxxxx[/TD]
[TD]xxx[/TD]
[/TR]
</tbody>[/TABLE]



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.

 

[Comfy]

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"?

 

[TinTin72]

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.

 

[Dendro]

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

before:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Part Number
[/TD]
[TD]Qty
[/TD]
[/TR]
[TR]
[TD]abcde#123[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]12345#abc[/TD]
[TD]56[/TD]
[/TR]
[TR]
[TD]zyx321#b21
[/TD]
[TD]73
[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD]Part Number
[/TD]
[TD]Qty
[/TD]
[/TR]
[TR]
[TD]abcde#123
[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]12345#abc[/TD]
[TD]?
[/TD]
[/TR]
[TR]
[TD]zyx321#b21[/TD]
[TD]?
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Comfy]

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:

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.