Access data import query

[excel_1317]

Is it not possible to import an excel file in access which has more than 256 columns?

 

[Joe4]

Access only allows 256 columns.
If you have more than that, then must likely your data table is not normalized, and you may have problems working on it in Access without normalizing it first anyway.
So why does it have so many columns?

If it is something where you have data for different weeks of the year, and you have a different column for each week, normalizing the data would change the structure so instead of have 1 record with 52 columns, you would have 52 records with 2 columns (one for week number, and the other for whatever you are tracking by week).

 

[excel_1317]

Access only allows 256 columns.
If you have more than that, then must likely your data table is not normalized, and you may have problems working on it in Access without normalizing it first anyway.
So why does it have so many columns?

If it is something where you have data for different weeks of the year, and you have a different column for each week, normalizing the data would change the structure so instead of have 1 record with 52 columns, you would have 52 records with 2 columns (one for week number, and the other for whatever you are tracking by week).

I am an excel user and havent use access before. Our vendor had provided us data dump which we need to convert into meaningful database. Access could be a solution but i dont know how to use it. If you say i can provide you with sample data dump to test. Many Thanks

 

[alansidman]

Since you haven't worked with Access in the past, perhaps you need to understand data normalization. You may have a data dump, but that doesn't mean the data is normalized. You may have to manipulate the data to get it normalized for it to work in Access. Read the link below on Relational Databases and Data Normalization.

Fundamentals of Relational Database Design

 

[excel_1317]

Since you haven't worked with Access in the past, perhaps you need to understand data normalization. You may have a data dump, but that doesn't mean the data is normalized. You may have to manipulate the data to get it normalized for it to work in Access. Read the link below on Relational Databases and Data Normalization.

Fundamentals of Relational Database Design

thank you for this link.. but since 2 days i have been studying the same thing.. tables.. relationships.. queries..

but the data dump i have is just not getting into shape to use with access... i would be vry thankful if anyone normalize the data for me.

 

[alansidman]

Cross posted at: Unable to import excel file in Access 2007

Please read this: Excelguru Help Site - A message to forum cross posters

 

[excel_1317]

Cross posted at: Unable to import excel file in Access 2007

Please read this: Excelguru Help Site - A message to forum cross posters

Thank you for letting me know the rules for cross posting. i will keep in mind in my future posts.

 

[Joe4]

Regarding cross-posting, our policy is outlined in rule #10 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html
In a nutshell, we allow it here, but just ask that you provide a link to the other post.

Regarding your question, Access may be the tool you want to use, but it sounds like your data may need to be cleaned up (normalized) a bit first before you can import it into Access.
When I am confronted with these type of situations, I first determine what the normalized data in Access should look like. Once I have figured that out, I go back to the sender and see if they can conform to that format. If not, you may need to use something else first (like an Excel macro) to get it in the format you need.