Link to home
Start Free TrialLog in
Avatar of RPUKsupport
RPUKsupport

asked on

importing and manipulating data in excel from csv files

Hi
I have an extract from a third party piece of software that dumps its data after a process has ran to a csv file. I want to import this csv into a workbook with at maybe 3 sheets, one to keep all of the historical data that comes in via the csv, one for the process data that i actually need (about 40% of data is required from the initial import) and then the main sheet that would basically tell me the results of the last process.

The question is where do i begin, I have been told a macro is the best way to do this, but the imported data has varying amounts of rows on every import, although the first 16 rows are to be deleted every time, so it would need to include variables to accommodate this.
Sorry for the lack of information, but im not sure what else i can put down at the moment.

Ultimately, i just want the user to press a button and everything is done, and the results are on screen for them and print out

thanks in advance
Avatar of Phillip Burton
Phillip Burton

Yes, a macro is the way to do this. However, you have not given sufficient information to actually write the code.

I would suggest attaching one of the files. I'm not sure what you want in the "main sheet" though - just a one liner?
Avatar of RPUKsupport

ASKER

I have uploaded 2 files, 1 is the extract CSV file with lots of data. The second is what i would like to see. I need to do the following:
* import data from csv

* clean the data, keep only the following:
From the "Solution Label" column only Big Press, Small Press, Tank 1, Tank 2
And then, only the data from the "element" column that equals Cd 214.439  (example in Results.xlsx)

*I then want this data to be saved on a sheet, appending after every import.

The results I would like on the results sheet, appearing in order (no results if Solution label was not present during that particular import). the imported results should then highlighted in red if any value is greater than the threshold (currently at 0.035)

I hope that gives you enough information
Results.csv
Results.xlsx
Not really - you have previously said that you wanted three sheets - 1 to contain historical data, 1 to contain about 40%, and then a results page.

You have now provided 2 sheets, which presumably are the last 2 sheets above, and then you then seem to want a smaller subset, based on cell Results!C4 - but what happens if that changes.

I'm not much the wiser.
Hi, yes you are correct sorry.
Sheet 1 should be total import history

Sheet 2 will be condensed history (I have the extract down to the bare minimum now) and this should contain as previously said "From the "Solution Label" column only Big Press, Small Press, Tank 1, Tank 2
And then, only the data from the "element" column that equals Cd 214.439  (example in Results.xlsx)"
The results!C4 can be broken down slightly, where 214. are always constant, the next 3 numbers have changed  maybe once every couple of years but are always within the range of 214.001 and 214.999 I will always require this field because of what the results represent.
In the results spreadsheet, cells C6:C9, will there only be one value per import to go in there, i.e. is there only ever going to be one value for TANK1, Cd 214.439 per import?
maximum of one value per import for tank1, however there may be no results for tank 1 but might be for tank2 and small press, so a dash or blank would do for no results, anything but a number
And (I think finally) are the CSV sheets in a constant format?
As you can see (enclosed are 2 exports from different times) they are pretty much.
The only differences are the first line (that we dont need) and the amount of results
Results.csv
Results-2.csv
Please find attached.

It works for the files that you have provided.
ResultsWithMacros.xlsm
I have ran it, is there any way to specify where the default opening location will be? and could this be a network share?
Also, the results page, is there any change that if the numbers on there exceed the threshold value, then they could be highlighted in red?
Other than that, its spot on
ASKER CERTIFIED SOLUTION
Avatar of Phillip Burton
Phillip Burton

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Excellent, thank you very much, work a treat