Thread: CSV & Cleaning Data Automatically

Hello Forum

My skills are limited and hence help is most appreciated.

I am working with Affiliates and datafeeds and I am trying to clean the data from a CSV comma file before importing the data and want to create an automated process to clean the file each time the data is refreshed / imported.

For example:

10,000 products (rows) in a merchant datafeed
I want to compare 2 values in 2 columns say AA and AB for each row
The automated rule I want to create is:

If AA = AB then delete the row.

Thereby creating a data file that only contains products (rows) where the cell value in AA is different to the cell value in AB.

Once this file is saved with this rule, hopefully, the output is that when a fresh import of the data feed is run the file is automatically examined for the above rule and only those products (rows) that have a difference in the values in column AA and AB are imported.

Can anybody please help ?

I am new to the forum and have searched but not quite identifying how I can achieve the above.

Many thanks in anticipation.

I'm sure somebody here will be able to help, but a sample workbook of your data and desired output will undoubtedly be useful.

Hi there

Thanks for the speedy response.

I guess the problem I have is that the data is 3rd party (being a merchants feed) and I don't believe I can post - doesn't help I know.

In general terms:

File is standard unzipped csv file that contains header columns such as merchant, product url links, product ID, description, thumb url, price, rrp etc.

Each product row then has cells mapped to the above - some being url's and some being values.

This file then calls the affiliate merchant server (via my software) and imports items and at present it will import all the items in the data feed.

What I want to do is set a rule for this csv file against 2 columns as per post, therefore it will only pull through products that meet the rule (the macro [?] would delete rows and therefore products that do not meet the rule during the import process).

Sorry that I cannot attach file to be more helpful.

Thanks

Regards

You possibly don't need to clean or delete the data.

Have you tried Microsoft Query? This lets you retrieve only the data that meets your chosen criteria.

Please see this page: Use Microsoft Query to retrieve external data

If that suggestion doesn't pan out, try attaching some dummy data.

Thanks all

Query look up is not possible as the only access to the server is via the csv feed file.

Further to Stephen R's recommendation, I have created a small dummy csv file (saved in xls).

To reiterate and further to my post, I want to create a macro / programme rule to only pull through rows (products) where AC is less than AD.

Therefore in this example the required output will only be rows 27-29.

This rule, thereafter, will search the entire file and import only the rows (products) that meets this rule via my import process which will be refreshed twice a week.

Thanks in advance for help.

Cheers

Sorry, to do this before data is imported is beyond my ken (and it seems to me by definition to be outside the purview of Excel). This code will do it once your data are in Excel.

Code:

Sub x()

Dim r As Long

Application.ScreenUpdating = False
For r = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
    If Cells(r, "AC").Value >= Cells(r, "AD").Value Then Cells(r, "AC").EntireRow.Delete
Next r
Application.ScreenUpdating = True

End Sub

Thanks for the reply ... much appreciated.

I'll give this a go... it may work fine as the datafeed will probably pull all rows across as a default standard workflow from the host server and then hopefully the rule will work within the import stage by only pulling items that meet the rule...

Fingers crossed !!

Thanks again