Manual: How to filter files.index.csv

Filtering and analyzing large datasets can be a challenge. Whether you’re trying to pinpoint specific product sheets or need to extract data based on unique identifiers, knowing how to navigate and filter this information efficiently is crucial. In this blog post, we’ll guide you through the process of using Excel to filter data from Icecat’s CSV files.

Source:

Open Icecat: https://data.icecat.biz/export/freexml/<Lang_Code>/files.index.csv.gz

Full Icecat: https://data.icecat.biz/export/level4/<Lang_Code>/files.index.csv.gz

The list of the codes can be checked here: https://iceclog.com/icecat-locales-and-language-code-table/

Structure of files.index.csv

• path – The path to the productsheet, can be in level4/ or freexml/ folder.
• product_id – Icecat internal unique identifier.
• updated – last modification time in format YYYYMMDDHHMMSS
• quality – product quality (Icecat, Supplier). For deleted products quality is REMOVED.
• supplier_id – Brand ID, can be matched with SuppliersList.xml.gz
• prod_id – MPN
• catid – Category ID, can be matched with CategoriesList.xml.gz
• m_prod_id – Mapped MPN for the same product. If there is> 1 mapped MPN, the product row is duplicated for each new mapped MPN. What is mapping?
• ean_upc – GTIN
• on_market – If the product is available on the market in the current locale. 1 for true, 0 for false. Only active products can be found here: on_market.index.csv.gz
• country_market – Active markets for the product, separated with “;”.
• model_name – A brief marketing name of a product.
• product_view – Product rating
• high_pic – Main product image
• high_pic_size, high_pic_width, high_pic_height – Main product image resolution
• m_supplier_id – ID of the mapped product
• m_supplier_name – Brand of the mapped product
• ean_upc_is_approved – If GTIN is confirmed by its provider.
• Limited – Brand limitation on the product
• Date_Added – Date when the product first appeared in the Icecat database.
• ean_upc_format – How many digits the GTIN contains

How to filter data by one value

Excel

1. Open the empty datasheet. Then click on Data → Import → From Text/CSV

1. Choose your CSV file and click on “Transform Data” to open the PowerQuery:

1. Then use filters as needed and click on Close & Load to import the filtered data into an Excel file.

How to filter data by the list of values in Excel

1. You need to have the data stored in a one-column Excel file, like this:

2. Follow the steps shown above and once the files.index.csv is uploaded click on New Source

3. Upload the list of values as a second source

4. Transform the column of values into List entity using Transform → Convert To List

5. Return to files.index.csv Data, then use the Excel formula to filter the values based on the list of values you provided. For instance, here is the formula to filter the SupplierID on the external list: = Table.SelectRows(#"Changed Type", each List.Contains(Sheet1, [supplier_id]))

6. The general formula is: = Table.SelectRows(#"Changed Type", each List.Contains(<LIST_NAME>, [<COLUMN_TO_MATCH>]))