1

I have a very large Excel file around 166,000 rows and 356 columns and I am trying to import this file into Access but this is giving me an error that the file is not in the right format. I realized that I could save the file as Excel 2003 but it would then only include, 65000 rows and 256 columns. Is there any way to circumvent this problem? I could upload this into SQL server but I was trying to figure out a quick way to do this.

Improve this question

1 Answer 1

Reset to default
1

Access has a limit of 255 fields (columns) per table so your Excel sheet is too wide to fit in a single Access table. One possible workaround would be to import <256 columns into Table1 and the rest into Table2 with a common unique field in both tables (e.g., the Primary Key of the original Excel "table", if there is one) so you can link them together.

Edit

If this is a one-time import then you could just create two copies of the original Excel file, delete columns in Copy1 until you have <256 of them, then in Copy2 delete all columns except: 1) the common linking column(s), and, 2) the columns you deleted in Copy1. You could then import Copy1 and Copy2 separately.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

do you know how to split the file or import less than 256, as it does not allow me to just import only 255 columns
Consider creating two named ranges in the sheet, each of which includes less than 256 columns. Then in Access, import the first named range into one table, and import the other named range into another table.
@HansUp I tried something like that before updating my answer. In an Excel sheet, I selected the data in columns 1 and 2 and named it "range12", then I selected the data in columns 1 and 3 and named it "range13". When I went to do the import in Access and selected "Named Ranges" only "range12" was available to select. I guess Access doesn't like ranges that are not contiguous. (I was trying to cover the case where the common "linking" columns were on the left side of the Excel sheet, where key values often live.)
We haven't seen any details about the sheet data other than number of columns and rows. If there is a suitable pkey column, he can copy it to the right side of the sheet, then make 2 named ranges where all the member columns are contiguous.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.