scattered data import into on worksheet

GyverX

New Member
Joined
Jul 30, 2014
Messages
6
I have been researching the solution to this problem for several days with no results that match my problem. I am hoping it is possible and I have come here in hopes that someone will know.

I am using Excel 2007 but have access to Excel 2010.

My boss has been using an Excel sheet as a template to fill out a sheet with information on it. This template is not actually a template in the terms we understand. They basically designed a sheet to look like a form and then saved it and then filled it out each time then needed it to and saved each file on its own.

They (We) have an estimated 300,000 files going as far back as 2005.

What I am wanting to do is create a database. (Im using Access 2007) but I want to grab as much information as possible from these sheets. The data is all over the place, but if I can get the data I need into columns, then I can sort out what is correct and what is not and then port that into a database so I do not have to recreate over 10,000 vendors.

There are 28 cells that have info in them. I need to get that info in to there own columns so I can sort them and clean things up from there.

The question is...

Is it possible to get the info from these files into columns in one or a couple worksheets?

Thanks for reading.
I am willing to answer the questions needed to get the answer.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
Are the 28 cells in the exact same layout in all 300,000 files? By layout I mean cell location/address.
 

GyverX

New Member
Joined
Jul 30, 2014
Messages
6
Are the 28 cells in the exact same layout in all 300,000 files? By layout I mean cell location/address.
For the most part yes, They are separated by year right now, with some small changes but If I can get it to do it once then changing the code should not be that hard to accommodate the difference.

I have a membership to lynda.com so I have that resource but im still looking through the Excel info to see if the info is in there.
 

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
If they're all in the same place, then this is a fairly simple matter of using the FileSystemObject to loop through all files in a folder(s), then you open each file, pull the 28 cells you need, and write them either to a temporary array or to a blank worksheet, one row at a time. But I don't understand what you mean about changing the code, if there are 300,000 files how are you going to know which are in layout #A vs layout #B?
 

GyverX

New Member
Joined
Jul 30, 2014
Messages
6
If they're all in the same place, then this is a fairly simple matter of using the FileSystemObject to loop through all files in a folder(s), then you open each file, pull the 28 cells you need, and write them either to a temporary array or to a blank worksheet, one row at a time. But I don't understand what you mean about changing the code, if there are 300,000 files how are you going to know which are in layout #A vs layout #B?
Well, If I am going to to it file by file then when the rows dont match up then I know I have to change the cell arrangement.

As far as changing the code, I was looking at some coding to pull all the info from multiple files. If the information that was being pulled from the files were wrong then I would only have to change the cell coding to get the right cells. Most of the data is in the right place for all them. There might be a cell difference from a handful of files.

I will try what you have suggested, Thank you very much.
 

GyverX

New Member
Joined
Jul 30, 2014
Messages
6
I do believe that I have found a solution with out coding. I ran across an Excel add-on called "RDBMerge" at RDBMerge, Excel Merge Add-in for Excel for Windows This is a great and small add-on that does exactly what I needed.
In order to what I needed from specific fields I typed in the cell number instead of a range eg; "b1,b5,h22 ect." For those that come across this thread, I wanted to let them know what I found.

Just to give an Idea of what I have done so far.. The info I needed I did get. I just did a merge of 1 out of 5 employees of files from 2005 to 2012. I came up with 7703 rows. I did the operation on my laptop so I could leave it alone. This uses a macro type of operation so you have to consider that Key strokes are being operated so you can really do anything until its done or you will mess the operation by using your keyboard.

I hope this helps someone.
Thanks
Jason
 

Watch MrExcel Video

Forum statistics

Threads
1,101,923
Messages
5,483,730
Members
407,406
Latest member
ishipra

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top