Capture details in multiple files into Excel sheet?

PsychomIKE

PsychomIKE

PsychomIKE

Associate
Joined
20 Oct 2005
Posts
112
Hi, hope this is the right forum.

I have to capture details of customers of ours from within hundreds of files across several different folder levels and put them in a spreadsheet - details are ContactName, ContactEmail and CompanyName.

Is there an automatic way of doing this?

Thanks
 
Well if its spread across multiple directories then I certainly wouldn't want to have to write some vba to open them all and move the data to a new sheet.

First thing to do would be to try and consolidate the location, i.e. put them all in one folder. If they all have similar names i.e. CS Data 1 2 3,4, 5, just use windows search, copy them all and move them into one folder.

Once you have them in one place you could write a macro which would open them up copy the data from the old sheet to the new sheet.

I don't envy you though as I doubt it is as starightforward as it seems.

May have to get some temps in to do it manually....
 
is it a one time only thing?

and is each file formatted exactly the same? ie same columns, in the same place, the sheet is named the same?
 
Shouldnt be too bad then. I would suggest a sheet with all the paths and just loop through the procedure in vba and do the same to each one. If you send me an example I can do it for you
 
If they are simple text files in the same format it should be fairly easy to read in the info you need using FIND, split the line using FOR and then set variables to pump out in csv format and then that at least gets all the info in a file you can pull in to Excel and manipulate.

Would need to see an example of the format of the input files though. But I am assuming something like...


SomeInfo=afhjkahefjakehf
ContactName=Joe Blogs
ContactEmail=[email protected]
CompanyName=Blogs Inc
SomeotherInfo=aifheiofhoiae
randomdatadownhere?randomdatadownhere?randomdatadownhere?
randomdatadownhere?randomdatadownhere?


So you could FIND on 'ContactName=' delim on '=' and then set the second token as your variable. Then just ECHO %ContactName%,%ContactEmail%,%CompanyName% out to a txt file with a .csv extension and pull into Excel.
 
Hi guys, cheers for helping out with this. You're knowledge far surpasses mine :)

chaosphy has pretty much nailed the format of the files though I wouldn't know where to begin with implementing your suggestions. Excel idiot here!
 
is that an exact copy from the file? So no delimiters other than returns? Better off posting the actual file
 
So the contents would be

ContactName Joe Bloggs
ContactEmail [email protected]
ContactPhone 556 556 990
ContactFax
CompanyName Joe Bloggs Inc
ContactName Joe Bloggs
ContactEmail [email protected]
ContactPhone 556 556 990
ContactFax
CompanyName Joe Bloggs Inc
ContactName Joe Bloggs
ContactEmail [email protected]
ContactPhone 556 556 990
ContactFax
CompanyName Joe Bloggs Inc
ContactName Joe Bloggs
ContactEmail [email protected]
ContactPhone 556 556 990
ContactFax
CompanyName Joe Bloggs Inc
 
They are text files renamed .PFL and the data is in numerous files across numerous directories.

i.e.

6453654272joebloggs.pfl
5427542772johnsmith.pfl
4324988903jondoe.pfl
 
You must log in or register to reply here.
Back
Top Bottom