Import Partial Excel File into Database

Hello everyone,

I have created a database with approximately 30 columns, 1st column being a primary key. At the end of the database there are 5 columns. We will call them VWXYZ.

One of my users wants to be able to update VWXYZ via importing an Excel document to the existing table. He does NOT want fields 1-25 updated. He is editing his Excel document to only include the primary key and then VWXYZ with the same exact column names. When he imports, it sets 2-25 to blank and updates VWXYZ to the updated information.

How can he import an Excel document that only updates VWXYZ?


Post your answer or comment

comments powered by Disqus
I tried import an excel file into access and I get an error message:

"An error occurred trying to import a file: EmployeeFileEnrollment.xls. The file was not imported.

What is wrong with my file?

Hello,

I need to code the process of importing the excel file into the Access. The excel file name and location is known and therefore I should open a window so the user would choose the file to be improted himself.

Please help.
Thaks.

Hi,
I'm having problems with importing an excel file into my database. I had a similar problem a few months ago but some how i kind of got around it at the time. Unfortunately, its come back to haunt me again!

Basically , ive created a database for a team of novice users, who will need to import an excel file every week into the database.Ive tried to keep it as simple as possible. Therefore, Ive created a command button which should import this Excel file (which is provided every week by another department and contains over 12,000 records!) into the database. Below is the code i am using to import this file:

	Code:
	Dim importExtractFileLocation As String

    importExtractFileLocation = "y:ExtractExtract2.xls"
    
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, "Extracts", importExtractFileLocation, True
    
MsgBox "Importing Extracts File complete.", vbOKOnly + vbInformation, "Import"

I do not get any errors when i run this code. In the status bar, it shows the progress of the file being imported, but unfortunately, part way through it just stops all of a sudden without doing anything. It doesnt import a single record and doesnt show any error message or anything.

I tried changing the formatting of the file, tried moving location of the file etc, but nothing is making a difference at the moment. Has anyone else come across this problem or know why this may be occur? Is there anyway of getting around this without converting it to a text file etc.

I need to get this sorted out ASAP, as im already running late on the project. Therefore, i would really really appreciate it, if anyone can please help me out with this.

Many thanks,
Kind regards,
Amber

What is the best way to import an Excel file into multiple tables in access?

Everytime I import an Excel file into Access it contains many fields with no data. I tried selecting the pertinent data in the spreadsheet and naming the range (or defining the range) but that did not change how the import wizard imported the file.

Any suggestions? I would really like to be able to use this but those extra lines are killing me.

Many Thanks,
bvtterflygirl

Hi all, I'm trying to get the following done:
I have code which import Excel files into my database with the "DoCmd.TransferSpreadsheet". which works great.
But I'm now splitting the database and I want to have the Excel files which a user selects in his frontend database, imported in my backend database. I don't see the possibility in the TransferSpreadsheet as this is set to the CurrentDb.
My temporary solution is to import in the frontend and CopyObject to the Backend, but does anyone have a direct solution?

Thanks, Rob

I have over 1,300 separate Excel files in a folder that need to be imported into an Access database table. This is what I have and it's not working. Any ideas would be greatly appreciated.

Private Sub Command0_Click()
Set fs = Application.FileSearch
With fs
.LookIn = "bri-netUsershhuntAranesp Merge File Bkup 3"
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."

For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Workbooks.Open Filename:=.FoundFiles(i)

Next i

Else
MsgBox "There were no files found."

End If

DoCmd.TransferSpreadsheet acImport, 8, "tblMerge", "bri-netUsershhuntAranesp Merge File Bkup 3.FoundFiles(i)", True, ""

End With

End Sub

I have an Access database with related tables and lookups.
The data for the tables is in a large Excel file with about 100 columns and 30,000+ records, which will be imported into one flat Access table.
This table has to be imported into smaller related tables.
Each record contains all the data on one person. A record maybe 2,000 rows down may be on the same person, but is a different record with different dates and figures. The "same person" part of the record wil be in one table and the rest of it will be in 3 or 4 related tables.
How will the related records match up if there is no matching key field in the original table?
Is this doable and if so, how? Access 2010 Windows 7
Thank you in advance, and for all the useful info from this great forum!
Jojo

I have recently upgraded to Access 2002, I also still have Access 97.

While in Access 2002 I tried to import an excel spreadsheet into access. It kept causing my database to shutdown. "Microsoft Access has encountered a problem and needs to close. We are sorry for the inconvenience"....When I tried to import the same excel spreadsheet into my Access 97, it worked just fine. In fact the way I worked around this was to then imort the file from the Access 97 database into my Access 2002 database.

I then sent a table from Access 2002 to excel, saved it and then tried to import that back into Access 2002 - same problem.

I've looked on the Microsoft web site - didn't find anything - so I think I must have something in Access not set up correctly. Any thoughts? Marie

Hello everyone. I am a newbie in access. I would actually like to import like only certain rows and columns from an excel file into a table into access. As the excel data would be changed dynamically, I have decided to use a linked table instead. Is it possible for the linked table just to display the specific rows and columns i want?Hope someone can help me! Thanks

Hi.

I have thousands of excel files in one directory. I want to build up the Access table from excel files.
Each excel file has one worksheet, but I don't need every column and low of the worksheet.

I am trying to import a FILTERED Excel worksheet of every excel files in that directory into an access table using visual basic code.


I was able to build up (someone helped me) visual basic code to import multiple excel files to one single Access table without FILTERING.

Someone can say that I can just import without filtering and do query in Access.
The problem is that each excel worksheet has too many row and columns that I don't need it, and I need to import thousands of excel files to one access table. Eventually I will face Access table size problem.


I need to improve my visual basic code to import only selected rows and columns that I want to import into Access table.

my worksheet is in the following format

ID Month Year Day OPEN HIGH LOW ...VOL..... SETTLE TradeDate.
OD 7 2011 15 ................... 546 7/15/2011
OD 8 2011 18 ..................658 7/15/2011
..
FD 7 2011 15 .................... 987 7/15/2011
..
..
SM 7 2011 15 .................... 632 7/15/2011
...
KW 7 2011 15 .................... 838 7/15/2011
....

All I need to import is to find "FD" and "SM" in the 1st column (ID) and get the rest of the row (or even better one is to get only ID, Month, Year, Day, Settle, and Tradedate data in that row)

Anyone could help me to filter the data in worksheet and import into Access table?
(I use Access 2007 version)

thank you very much.


FYI, the following is the visual basic code that WORKS for importing multiple excel files to one single Access table without FILTERING.


Sub Import_multiple_excel_files()

Const strPath As String = "C:Price DataNYMEX2011_test" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number

'Loop through the folder & build file list
'strFile = Dir(strPath & "*.csv")
strFile = Dir(strPath & "*.xlsx")

While strFile ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'creating a new table called importedLMP_t
For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acImportDelimi, , _
"importedNYMEXinAccess", strPath & strFileList(intFile), True
'Check out the TransferText/TransferSpreadsheet options in the Access
'Visual Basic Help file for a full description & list of
'optional settings
Next
MsgBox UBound(strFileList) & " Files were Imported"
End Sub

Hi,

I have multiple excel files in the same format but data for different dates. i would like to import those into a single access file with an additional field of date. how can i do this programmatically, since the number of files is large i.e. more than 100.

Best Regards,
Arjun Bhandari

Hi there,
I'm a complete newbie to access and would appreciate some help, or lots of help!
My problem is as follows.
I receive a monthly report on sales and would like to have this data imported into an access database, and have addtional columns added to the access database for each additional month. Also, i would like access to append new rows for new sales data.
For example,

month 1 report
Area Sales
A 200
B 300
C 100

month 2 report
Area Sales
A 100
B 200
C 600
D 400


For the initial month (month 1), i just import the excel spreadsheet into access and that's fine.
So now, in access I have a table like this
Area Sales
A 200
B 300
C 100

However, for month 2 data, i would like to import the excel and have access append/change its data to the form below

Area Sales month 1 Sales month 2
A 200 100
B 300 200
C 100 600
D 0 400

In this case, the new column 'Sales month 2' is added in access along with the sales figures and a new row for Sales area 'D' is added. For month 1, sales area D is automatically set to 0, since it did not exist in month 1.

And so on, for the next months. Any available sample access files that already do this? mind sharing?
Thanks a lot.

Dear all,
Please advise on what is the best way to import Excel file into Access database. I have a Excel file that need to update everyday and import to access database(by using DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9) method. The problem i have is:
1) There is always empty records imported from the Excel file into the table.
2) Sometime Access will detected extra colume whereby is invisible in Excel file.

Please advise any good solution or any other method i can take in order to solve this import from excel problem?

Thanks in advance.

Hi everyone,

I have an error to import an excel file into ms access 2.0(1993). I am developig a vb6 application.Back end is ms access 2.0. 5000 records are saved in the excel sheet. I need to import to access 2.0. I am using ms access 2003. I could not edit or modify the 2.0 database in the ms access 2003. And also, I could not import the excel file into ms access 2.0.

Please guide me.

Thanks in advance,

J. Mohan

Hello,

I have some data in excel which I am importing it into MS Access 2002. Each excel file (one worksheet per file) is imported into separate tables.

I want to combine all my imported tables into one table. Is that possible, if yes then how and if no then what can be done to get single table after impoting data from various excel files.

Cheers,
Mandeep

Greetings from Guelph. First, my apologies if this has been covered already. I have searched through the forum and the Using Microsoft Access 2002 and 2003 books by QUE publishing but have not found an answer.
Each week I import an Excel file into Access 2002. Each week the file will have approx. 5,000 rows. The same fields will always be in the same order with consistent data formats, the same field names, field lengths etc.
Each week I want to normalize the imported table in Access to reduce space and make the database more efficient.
My question is, can I append the latest week's normalized table to that of the previous week's so that I end up with one master normaized table to run queries and reports against, rather than having a separate table for each week?
Any suggestions would be appreciated.
I have worked with and studied Access in books and online courses since 2002 but this is my first crack at setting up a database from scratch.
Thanks!

Hi,

Is the record is already existing in access, but a few fields are blank, is there any way to import an excel file that just updates those blank fields for the specific record?

For example,

I have an excel file that includes files that are ready to ship out. My access database already has those file names, but does not have the shipping box number. Is there any way to import the excel file into my database, have it find those specific file names and update the shipping box number field? The excel file won't be adding any new records, just appending existing records.

Any help is greatly appreciated.

Thank you.

Im familiar with Ms Access, but have never used VBA or Scripts. I have 37 Excel files with the same data and would like to import into one file. Data will be received on a monthly basis into the same directory and I would like to automatically upload the data into the same file in Access.

Help please, Tx

I need to create a Macro in Access that will allow me to import an excel file into an access database.

The requirements are:
-The code should allow me to choose whether the first row has fields
-Browse the Excel file (cannot be specific because this excel file may change)
-Choose the Excel file and specific sheet to transfer (cannot be specific because this sheet may change)
-Skip 'n' rows in order to get to the data of the excel table
-Transfer the sheet into an access table
-Delete original file that was imported

I attached what i have so far but it's missing two requirements: Skipping rows and being able to choose a specific sheet instead of just a folder that contains the excel file.

Hope you can help!

Thanks

Private Sub btnUpdate_Click()

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String, strBrowseMsg As String
Dim blnHasFieldNames As Boolean

blnHasFieldNames = False
strBrowseMsg = "Select the folder that contains the EXCEL files:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If

strTable = "EMT"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & "" & strFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

Kill strPathFile
strFile = Dir()
Loop
End Sub

How to embed a Microsoft excel file into Microsoft access database.

Hi,

I am trying to replicate the process of importing excel file into ms access as a table. Is there any way of doing it??

Currently, I am manually getting it done via External Data--> Import Excel Spreadsheet.

What I would like to achieve is to have a macro whereby a click of a button will get the spreadsheet into access as a table.

Thanks

Hello,

I have this task.
I need to convert the excel file into the text file before I import it into the the Access table.

While I convert it into the text file I need to take into consideration the scientific numbers that spreadsheet contatin, so they would be converted properly.

I do not know how to make the conversion of excel file to text file. I also do not know how to deal with the scientific numbers while converting them into the text.

If anyone knows or have an example or knows the sources where I could read on that, I would be greatful if you shared it with me.

Thanks a lot.

Hi all,

as the title says i want to create a script that will import all excel files in a give directory and put them all into 1 temp table within access. All the excel spreadsheets are in exactly the same format.

I can import a single spreadsheet into access no problems but as far as i can see the doCmd.TransferSpreadSheet command is for a single spreadsheet and im gonna try and put it into a loop but im positive there must be a better way to do this.

anyhelp is much appreciated!

Cheers


Not finding an answer? Try a Google search.