Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

importing and manipulating data in excel from csv files

Posted on 2014-11-12
13
Medium Priority
?
104 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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 2000 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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

730 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