Problem extracting the excel data into Access

[sriramus]

Hi All,

I have a consolidation task to be done.

I have attached an example spreadsheet

My spreadsheet sheet will be in the same format but with 5 more worksheets and each worksheet consisting of more than 200 rows. Think this is a input spreadsheet to 300 customers and they fill in the details and send it back as an attachment to a common email Id.
My task is to consolidate all the attachments. Its not possible to do it directly into an excel sheet as the columns exceed 1000 and the rows exceed 10,000.
I thought I will extract the data from each attachment into a MS Access Database and then query the Database for the required information.
Do you think this is the right way of doing it or is there any other way.
I suppose I can extract the data from each attachment with VBA code. I tried importing data from excel to MSAccess and it was not satisfactory.
Please let me know if I am approaching the right way and any material which helps me to work with this task.

Thanks in Advance.
(http://www.upload2.com/?cmd=_viewer&file=f836925e997c31229e5281d05723cedf.xls&s=425f7)

[XLGibbs]

Access won't accept 1000 columns either...the limit of 255 applies, so even if you imported into access you would likely have to have 4 tables, joined by a common primary key across all tables.

If there are 1000 fields filled out by customers in separate excel files, this is a daunting task, as your table structure in Access will have to be fairly solid to properly handle the data.

You would like want to use Excel VBA to consolidate and package the 300 customer filled out sheets into a workbook with 4 sheets, each one set up in the way needed to import to the Access tables (i.e. Same column names etc)

Having the necessary Access structure is key, which then gets applied to your consolidation excel file for structure. Then, it is possible to extract each of the 300 files relevant data into this template.

Sounds like a LOT of work.

[matthewspatrick]

Its not possible to do it directly into an excel sheet as the columns exceed 1000 and the rows exceed 10,000.


sriramus,

Why so many columns? If your database is even close to normalized, you shouldn't ever need anywhere close to that many columns on a table. (Indeed, more than about 20 columns on a table is usually evidence of design flaws...)

Patrick

[sriramus]

sriramus,

Why so many columns? If your database is even close to normalized, you shouldn't ever need anywhere close to that many columns on a table. (Indeed, more than about 20 columns on a table is usually evidence of design flaws...)

Patrick

Thanks for your time taken to reply me.
Can you tell me the right way to approach this task, Is the task easier if i use GUI (VB) to get the same information from all 300 customers and update my backend (Access) database dynamically as the customer finishes his data entry and click on the save button.
If i adopt the above method i suppose i can query the database for the required information and extract the data into excel.

Please let me know what you think about this piece of task.

[XLGibbs]

I think the operative issue here is how much data on each of these 300 separate pieces of the pie need to be extracted. If you need the information summarized in excel, and it exists in excel...leave Access as the VB GUI out of it.

You can write VBA in excel to do the needful on one sample file, then that routine can be inserted into a loop to cycle through all the 300 files.

Regardless if each of the 300 pieces have 1000 columns, it would be a nightmare to create the necessary forms in VB only to then put it in access (duplicating work, since the Access DB would have to be there in advance with structure), only to subsequently put it back into excel where it came from.

I am still lost on 300 pieces, 10000 rows and 1000 columns per piece. that seems inflated.

[matthewspatrick]

I am still lost on 300 pieces, 10000 rows and 1000 columns per piece. that seems inflated.

As am I.

About the only way that makes sense to me is if a given attribute (say, "color") were assigned multiple columns, so that you indicate 'purple' by having an X in the 'purple' column, and so on for 'orange', 'yellow', 'white', 'green', 'teal', etc. columns. Replicate that with a few dozen attributes, and I guess you could get to 1000 columns. And that would go right back to the comments about normalization.

Patrick

[XLGibbs]

I suppose, not that we are in any place to doubt the poster's needs I would be taking a closer look at whatever it is these 300 customrs need to fill out. I am trying to figure out what this is that they are filling out that requires a 10 million points of interest to be available.(10K by 1K).

Since the information is already in excel, granted over 5 sheets and 200 columns each...it would be more economical time wise to simply deal with excel on the issue with respect to data manipulation.