Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 105
  • Last Modified:

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
0
RPUKsupport
Asked:
RPUKsupport
  • 7
  • 6
1 Solution
 
Phillip BurtonCommented:
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?
0
 
RPUKsupportAuthor Commented:
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
0
 
Phillip BurtonCommented:
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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
RPUKsupportAuthor Commented:
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.
0
 
RPUKsupportAuthor Commented:
0
 
Phillip BurtonCommented:
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?
0
 
RPUKsupportAuthor Commented:
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
0
 
Phillip BurtonCommented:
And (I think finally) are the CSV sheets in a constant format?
0
 
RPUKsupportAuthor Commented:
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
0
 
Phillip BurtonCommented:
Please find attached.

It works for the files that you have provided.
ResultsWithMacros.xlsm
0
 
RPUKsupportAuthor Commented:
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
0
 
Phillip BurtonCommented:
Please find attached.
ResultsWithMacros2.xlsm
0
 
RPUKsupportAuthor Commented:
Excellent, thank you very much, work a treat
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now