How to extract info from csv files to excel Solved/Closed

Question

Hello, 

I have 74,340 .csv files I need to extract information from Col. A-C & Rows 1-143 into an excel workbook, Sheet1. Every .csv file has different name/timestamp although I noticed something common right before .csv. Example:
dance_you_12.12.12_PinkA.csv
dance_you_12.12.13_PinkB.csv
dance_you_12.12.08_PinkC.csv
dance_you_12.12.45_BlueA.csv
dance_you_12.12.22_BlueB.csv
dance_you_12.12.19_BlueC.csv
dance_you_12.12.50_RedA.csv
dance_you_12.12.59_RedB.csv
dance_you_12.12.47_RedC.csv
dance_you_12.12.38_GreenA.csv
dance_you_12.12.21_GreenB.csv
dance_you_12.12.17_GreenC.csv
dance_you_12.12.11_YellowA.csv
dance_you_12.12.05_YellowB.csv
dance_you_12.12.03_PurpleA.csv

1.  Can I do this without opening up every csv file and pull range of info I need while closed? 

2. For EVERY .csv file I grab info and copy to workbook Sheet1 I have 4 calculations + 3 data values that I need to grab and put into the specified color tab before I can move on to next .csv file. These cell locations are static, B25, B27, B28, B155-158 which I will do a copy and paste special into COl. I of perspective color tabs (Pink,Blue,Red, Green, Yellow, Purple). So, is there a way to do this for every .csv file that will put 7 data values into correct specific color tab? So, PinkA-C should go to "Pink" tab, BlueA-C should go to "Blue" tab, etc.

Thanks for any help with this. I really am clueless how to go about this task.

Weenie

Configuration: Windows 7 / Internet Explorer 9.0

rizvisa1 · Answer

for #1, the file needs to be opened by code in order to read data

for #2, since the name seem to have color attached just after the last _, one can extract that information to goto to right tab

weenie · Answer

I understand opening and getting info I need and I think it might be easier to calculate my forecast formula on each fiile I open then copy the exact cells I need which would be 7 specific cells. I will not be needing to save the files just close them out even though I am calculating a  formula from data which I then will pull off values into my worksheet. But now How do do this on a loop?
I am still confused on how to go about opening 74,00 csv files in a loop. An example of the files names are as follows:
Pmuck_pac.2011.12.09_13.53.04_PinkA.csv
Pmuck_pac.2011.12.09_13.53.04_PinkB.csv
Pmuck_pac.2011.12.09_13.53.04_PinkC.csv
Pmuck_pac.2011.12.09_13.53.04_BlueA.csv
Pmuck_pac.2011.12.09_13.53.04_BlueB.csv
Pmuck_pac.2011.12.09_13.53.04_BlueC.csv
Pmuck_pac.2011.12.09_13.53.04_YellowA.csv
Pmuck_pac.2011.12.09_13.53.04_YellowB.csv
Pmuck_pac.2011.12.09_13.53.04_YellowC.csv
Pmuck_pac.2011.12.09_13.53.04_RedA.csv
Pmuck_pac.2011.12.09_13.53.04_RedB.csv
Pmuck_pac.2011.12.09_13.53.04_RedC.csv
Pmuck_pac.2011.12.09_13.53.04_PurpleA.csv
Pmuck_pac.2011.12.09_13.53.04_PurpleB.csv
Pmuck_pac.2011.12.09_13.53.04_GreenA.csv

What does change is the date & military time eveything else is repeated in 15x blocks of:
Pmuck_pac...................._ColorsA,B,C.csv
Not sure what is an easy method to opening up files in 15x blocks or all PinkA then Pink B, etc thatt way the system does not crash trying to open so many files (74,000). Any help with the code would be helpful. I have code to insert my FORECAST function into the csv file then pulling data from those cells and copy to a workbook but now How do I tackle the mountain of opening csv files 74,000 times besides manually 74,000 times.
Thanks,
Weenie

How to extract info from csv files to excel

2 responses

Similar discussions

Subscribe To Our Newsletter!