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

TinTin72

New Member
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

Active Member
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

New Member
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 NumberQty
abcde#1239
12345#abc56
zyx321#b2173

<tbody>
</tbody>


Price File 1 (.CSV)

Part NumberQty
abcde#1230
xxxxxxxxxxxxxx
xxxxxxxxxxxxxx

<tbody>
</tbody>


Price File 2 (.CSV)

Part NumberQty
12345#abc389
xxxxxxxxxxxx
xxxxxxxxxxxx

<tbody>
</tbody>


Price File 3 (.CSV)

Part NumberQty
zyx321#b211
xxxxxxxxxxxx
xxxxxxxxxxxx

<tbody>
</tbody>



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

Well-known Member
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

New Member
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

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

before:

Part Number
Qty
abcde#1239
12345#abc56
zyx321#b21
73

<tbody>
</tbody>

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

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

<tbody>
</tbody>
 

Comfy

Well-known Member
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:

Some videos you may like

This Week's Hot Topics

Top