Import Large Excel File

[standenman]

I am trying to import a very large Excel file - 205,000 KB. I click on "new data source" and select my excel file and go through the wizard. This is what I get at the end. Is this related to the file size? Is there another way?

 

[ebs17]

205,000 KB are around 205 MB. An Access file can be up to around 2 GB in size.

Strictly speaking, you cannot import an Excel file, but rather Excel tables from this Excel file, i.e. individual worksheets or ranges from it.

 

[standenman]

I understand. When I go to External Data>New Data Source>From File>Excel I choose a target excel file the Wizard takes me through and creates a new table for that excel data - if that target file is another small excel speadsheet. But like I said when I target that large file I get the error mentioned in my original question.

 

[CJ_London]

the error implies it cannot find the table - have you checked the spelling?

 

[tvanstiphout]

The error message means that the table NPI* does not exist, and indeed we don't see it in the Navigation Pane.

 

[standenman]

Just so I am clear. I do not have a table in MS Access "waiting" for the date from the excel spreadsheet. But like I said for any other excel file, I do not need an access table: the wizard creates the table.

 

[CJ_London]

I do not need an access table: the wizard creates the table.

In that case, the error implies you have selected 'append a copy of the records to the table' rather than 'Import the source data to a new table' in the first screen. SInce either method will ultimately bring you to the same screen you are showing and the description of your actions are ambiguous as to which method you used, you need to provide more information

 

[standenman]

But I an not selecting append. I actually got it to work by creating a totally new MS Access database. Go figure! thanks for your help!

 

[Pat Hartman]

When Access does things like this, and you know you are not making a mistake, always close Access. Even consider rebooting before you try again.

 

[standenman]

Thanks

 

[raziel3]

Import or Link? I would create a Link to the Excel file.

One thing to also note is that if you have the Excel file open, Access cannot read from it. Close the Excel file and retry the wizard.

 

[Pat Hartman]

Import or Link? I would create a Link to the Excel file.

Initially, you can do either. It depends on the ultimate goal. If the data eventually gets imported, you can do it directly if it is always fairly clean or you can link to it (using alias' for the bad names) and that allows you to do a little validation/correction on the way in.

 

[CJ_London]

One thing to also note is that if you have the Excel file open, Access cannot read from it

Another reason for using sql rather than a linked table.

Not only can you connect to the excel file regardless of whether it is open or not, but with the right imex setting you can edit excel from access and the changes are immediately visible in excel.

Editing is limited, you can’t delete a row for example, just make all the values blank and you can’t add/change column headings - although these can be handled through automation.

but you can add new rows and change existing values- just take care about data types, excel doesn’t care, but access does

 

[raziel3]

Another reason for using sql rather than a linked table.

I prefer to link to the Excel file to maintain the integrity of the Excel data. If the Excel is the main source of the data (Call it Workbook A) I would create a new Excel file, Power Query the data and fix the formats etc (Call that Workbook B) then link Workbook B to Access.

That way Workbook A is always available for Editing and Updates but to get the latest data into Access you would need to open Workbook B hit "Refresh All" save and close. Now when you open up Access the latest data is available. I then have an Upsert query to add and edit the data in the Access Table (which should be a mirror of Workbook A).

The great thing about this setup is that once the main Excel sheet (Workbook A) is on Onedrive, the Power Query will be able to connect to it no matter where you are. The bad thing is Access cannot link to Onedrive files so Workbook B has to be a locally stored file.

Edit: the OneDrive files I am referring to is over the web, not locally stored ones.

 

[CJ_London]

It is just a matter of preference - there is often more than one way to achieve the requirement. Don't know what you mean about maintaining Excel integrity

Access cannot link to Onedrive files

I don't have a problem with connecting to an excel file on OneDrive either using a linked table or sql

 

[raziel3]

Don't know what you mean about maintaining Excel integrity

I'm not tying to offend anyone . This is just my experience. People are more comfortable using Excel so I try not to interfere with their workflow. If they are stubborn to move away from their methods I try not to change it, I just work around them. The original data source always remains unchanged.

Using Excel's Power Query you can connect to another spreadsheet or db using the Web Path, Access cannot do that. The file has to be a UNC path.

 

[standenman]

Thanks to all for your responses. I will look at it tomorrow.