.

Searching multiple Excel pricelists

At least here in Moldova, many computer stores publish their price lists as Microsoft Excel (.xls) files on their web sites, often in a zip archive. To compare the prices for a product in several stores, one usually has to download and unpack the price lists for each store, then search through each one manually.

I made myself something to automate this last year; here it is in case someone finds it useful.

A batch file is used to clean the current directory, download the price lists, unpack the zipped ones, and start the conversion VBscript.

@echo off
::Clean-up
del *.zip *.xls *.csv *.doc Thumbs*.db
::Download
wget "http://www.ollegroup.com/prices/ollegroup.zip" "http://hardware.md/price/sv-center.zip" "http://cosmo.md/download/Cosmo.zip" "http://www.doxyterra.md/doxy.zip" "http://www.matrix.md/mx-price.zip" "http://atompc.net/upload/atompc.zip"
::Download - nanoteh
wget -O nanoteh.zip "http://nanoteh.com/modules/market/price.zip"
::Download - Neuron
wget -O neuron.xls "http://neuron.md/pricelist.aspx"
::Download - Shop-it
set COMMAND="wget http://www.shopit.md/site/index3.php -O - 2> nul | grep -oE prices/[0-9]+hsmd\.zip"
for /f %%i in ('%COMMAND%') do set SHOPIT=%%i
wget http://www.shopit.md/%SHOPIT%
::Unpack
for %%a in (*.zip) do 7z e %%a
::Convert
start /wait convert.vbs

convert.vbs is a VBscript which converts the .xls files to .csv:

Dim xlAppSource
Set xlAppSource = WScript.CreateObject("Excel.Application")
'xlAppSource.Visible = TRUE

Dim fso ' file system object
Dim f ' file
Dim fl ' folder
Dim fc ' file collection
Dim scriptfile ' path and filename of the running script
Dim scriptpath ' path of running script

scriptfile = WScript.ScriptFullName

Set fso = WScript.CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFile(scriptfile)
scriptpath = f.ParentFolder
Set f = Nothing

Set fl = fso.GetFolder(scriptpath)
Set fc = fl.Files

For Each f in fc
  If Right(f.name, 4) = ".xls" Then
    xlAppSource.Workbooks.Open(scriptpath & "\" & f.name)
    For Each Workbook In xlAppSource.Workbooks
      Dim NewPath
      NewPath = scriptpath & "\" & Left(f.name, Len(f.name)-4) & ".csv"
      Workbook.SaveAs NewPath, 6
      Workbook.Close(True)
    Next
  End If
Next

After running the script, simply grep the .csv files for the item you’re looking for:

C:\Users\Vladimir\Documents\Price-lists> grep -i 6GB.*1333 *.csv
Hrdw_price.csv:"DDR-3 6Gb 1333MHz  Kingston (Kit of 3x2GB) PC3 10600, CL9",183,MEM,,,,,,,,,,,
OlleGroup.csv:,"6GB DDR3 1333MHz Kingston (Kit of 3x2GB) PC3 10600, CL9, ""KVR1333D3N9K3/6G"" Retail",121.00,24,,,,,,,,,,,,,,
Price (15.12.2009).csv:"6GB DDR3 1333MHz  Transcend (Kit of 3x2GB) PC3 10600, CL9, ""TS1333KLU-6GK"" Retail",186.00,y.e.,24

Comments