Solved

importing and manipulating data in excel from csv files

Posted on 2014-11-12
13
89 Views
Last Modified: 2014-11-14
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
Comment
Question by:RPUKsupport
  • 7
  • 6
13 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40437799
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
 

Author Comment

by:RPUKsupport
ID: 40439864
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40439903
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
 

Author Comment

by:RPUKsupport
ID: 40439936
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
 

Author Comment

by:RPUKsupport
ID: 40439941
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40439956
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:RPUKsupport
ID: 40439971
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40439978
And (I think finally) are the CSV sheets in a constant format?
0
 

Author Comment

by:RPUKsupport
ID: 40440013
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40440145
Please find attached.

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

Author Comment

by:RPUKsupport
ID: 40440255
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
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40440280
Please find attached.
ResultsWithMacros2.xlsm
0
 

Author Closing Comment

by:RPUKsupport
ID: 40442548
Excellent, thank you very much, work a treat
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now