Macro to read data from csv files based on criteria

**arbrahul** · 01-16-2011, 01:20 PM

I need to create a macro to read data from a csv file and paste it into an excel report generated on daily basis. Could anyone demonstrate this with some example ?

My reporting task is done manually on regular basis and need to make it automatic.
One button click should read data from a csv file with the row data and paste the data into the report, add new rows to the tables in the sheet which shows daily statistics, and automatically populate the graphs. Could anyone help me some sample macros ?

**watersev** · 01-16-2011, 01:44 PM

hi, can you post example of the file and result you should get in excel? There are many examples of code on the forum to deal with csv file

**arbrahul** · 01-19-2011, 02:29 PM

Here are the sample data attached. The CSV (Attached as .txt, please rename it) file has the details in row format. I need to copy and paste the content from it to the excel sheet after checking certain conditions.

1. Fron csv file I need to copy and paste :

DailyPerformance
ServerName AvgPerformance
SERVER1 213
SERVER2 252
SERVER3 208

this part to the Trend data sheet of the excel (Need to add a new row and increment the date in the column1 previous row.). Values under the corresponding columns for each server.

2. Need to copy paste:

PerformanceReportOrderby10Minutes ServerName: SERVER1
Time AvgPerformance
1/1/2000 0:00 116
1/1/2000 0:10 115
1/1/2000 0:20 122
..................
..................
..................

This part to the performance values (properly - 1 column for time, second for server1, 3rd for server2, 4th column for server3)

Could anyoneplease help me ? I dont know anything in macro

**watersev** · 01-20-2011, 09:57 AM

check attachment, from Tools menu run macro "Server_data". Csv file it was tested on is attached in zip-archive.

However I do not think I understand what else you'd like to do (with graph part).

PS. Edited to add possibility of no value after coma.

**arbrahul** · 01-21-2011, 11:24 PM

Originally Posted by watersev

check attachment, from Tools menu run macro "Server_data". Csv file it was tested on is attached in zip-archive.

However I do not think I understand what else you'd like to do (with graph part).

PS. Edited to add possibility of no value after coma.

Thank you very much... I really appreciate your help.. I will will definitely go through the code and try to study some VBA

Thank you once again.

**MarvinP** · 01-22-2011, 01:07 AM

Hi abrahul,

I'm getting into this conversation late but have a suggestion on how you keep your data. I believe you can do a lot more with it if you have 4 Columns: Server Name, Date, Time, Performance. You are not keeping the date with your original data but putting it on when you do your Trend Sheet.

I've created a pivot table with only 3 of the above 4 as it is a single day. If you kept your data like I suggest you could do Pivot Tables with it. This would allow you to group youir data by Hour or Day or Month. You could filter and show specific times of the day or single or multiple servers together on your graph. You could easily do trend lines and then show by filtering which server might need upgrading or splitting into other servers.

Adding new Servers to the data would be no problem as they would simply go on the bottom of the data with "Server4" . If you keep your current structure you will need to add a Server4 to the right and problems happen with data import and reprogramming.

See the attached file where I've made a Pivot Table and "fixed" your data on the Performance Page. I hope it gives you a few ideas.

**arbrahul** · 01-23-2011, 12:56 PM

You have helped me a lot.... I dont know anything about excel pivot tables, macros etc... My requirement does not complete here.... The sample report i have given is not the full version.. it only a prat of it.... I actually have 5 such CSV files and need them to be copied to the excel report (i have given you has only 3 worksheets but the actual one has around 10 sheets with a lot of graphs). This is a daily report and I would like to completely automate it my using macro. Would you mind helping me with all my requirements (If you agree, it would be a great help for me but trouble for you ) ? If you can help me with all the requirements i could post the samples and request you for help... And if you agree i could give you my chat id (gtalk, yahoo, skype or anything and could do it through chat ). I am actually looking for more help online and as you it is very difficult for me to do the complete automation as I am a beginner.

Thank you very much once agani for your extended help. You are a star, I like helpful people like you.

**MarvinP** · 01-23-2011, 03:15 PM

Hi arbrahul,
Hydrabad? Version of Excel? Is it really 2003 or do you have a newer version. In 2007 and now 2010 working with Pivot Tables is a lot easier. Update you profile that shows on this page. We use it to try to determine if our answers are to the correct version.

A lot of the help we can provide, now that you mention more csv files is determined on what the csv data looks like. Is it all the same format? Are the daily csv files the same names? If they were then automating the process (using vba) would be different than if they were named with a date/time name convention.

What your final answer is also determines how much we can help you. If your manager needs full data or just a summary or a graph or a trendline the way you structure your data from the beginning is all important. Pivot Tables may or may not be the "better" method based on what you need for your outcome.

I guess the bottom line is we need to understand your problem a little better. You need to study up on Pivot Tables (hopefully not from 2003 Excel) and record some macros and look at the VBA editor. I beleive your problem of automating your daily process can be automated but like my signature line "one test is worth a thousand opinions".

**arbrahul** · 01-23-2011, 11:02 PM

I liked your Signature.
I am from Trivandrum (India) and I am using microsoft Office 2007. All the sample CSVs and final report are attached here.

As I already mentioned we have 5 CSV files. Name of the files will be the same everyday. But will be in different folders.

Once finished, my macro enabled excel report should ask me to browse for the files, and I will browse for the files for the previous day. Then it should create the finished report for me (you may feel than I am thinking of too much and dont know anything- sorry for that). Currently copying and pasing of the data is done manually.

Attached Sample CSVs report, requirement in a notepad.

**MarvinP** · 01-23-2011, 11:23 PM

Hi, You are in very south India.

I do a similar thing daily with CSV files. I open them and copy and paste all the data I need from them to my Excel worksheets and then close them. I have it where I click a command button, which asks me to find and click on the correct filename. As soon as I do this it opens the csv file and copies and pastes it to my sheet.

You should change your profile on this forum to say you are using 2007 Excel instead of 2003 as it helps us understand your problems better and give better answers.

It is possible. Sounds like an achievable goal.

**arbrahul** · 01-24-2011, 12:08 AM

I have updated my excel version, it is 2007

Macro to read data from csv files based on criteria

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro to read data from csv files based on criteria

Re: Macro to read data from csv files based on criteria

Re: Macro to read data from csv files based on criteria

Re: Macro to read data from csv files based on criteria

Re: Macro to read data from csv files based on criteria

Re: Macro to read data from csv files based on criteria

Re: Macro to read data from csv files based on criteria

Re: Macro to read data from csv files based on criteria

Re: Macro to read data from csv files based on criteria

Re: Macro to read data from csv files based on criteria

Re: Macro to read data from csv files based on criteria

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions