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
Microsoft Excel

Avatar of undefined
Last Comment
RPUKsupport

8/22/2022 - Mon
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?
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
Phillip Burton

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
RPUKsupport

ASKER
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.
RPUKsupport

ASKER
Phillip Burton

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
RPUKsupport

ASKER
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
Phillip Burton

And (I think finally) are the CSV sheets in a constant format?
RPUKsupport

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Phillip Burton

Please find attached.

It works for the files that you have provided.
ResultsWithMacros.xlsm
RPUKsupport

ASKER
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
Phillip Burton

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
RPUKsupport

ASKER
Excellent, thank you very much, work a treat
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.