Transfer specific columns from Excel to specific columns in Access Table

Question

I am attempting to transfer specific columns from an excel document to specific columns to a table in my Access database. For example my excel sheet might contain the following:

Date  Last  First  Gender   Month  School  Grade 
10/1  Rode  Danny  Male     Sept.  Ro ISD     10 
10/2  Dode  Manny  Male     Sept.  Ro ISD     11

And My Student Table for my database may contain the fields:

Type Source Phone Email Last First School Major School Grade

I only wish to export the excel columns labeled: Last, First, School, Grade and put them in their respective columns in my student table. I've looked at the DoCmd.TransferSpreadSheet in VisualBasic through access but am unsure if this is a solution or if there is another way. Any help would be greatly appreciated as it would savce me soooo much time! Thanks!

Microvolt

Answer 1

There are many ways to accomplish this. A quick method would be to import all of the data from the Excel spreadsheet into a table (i.e. "MyExcelImportTable"). Then you could Create and run a query that would append just the data you want.

INSERT INTO MyStudentTable(Last, First, School, Grade)
SELECT Last, First, School, Grade FROM MyExcelImportTable

Jahdai

@alpha_nom, from menu: Create-->Query Design. Then on the Design tab (which should now be open), on the far left, chose "SQL". Then paste or type code into the window. Then click "Run" icon (should be on left of tab strip). – Jahdai

@alpha_nom: I forgot to mention that after opening the design tab, you will need to close the "Show Table" dialog box that pops up before you will see the "SQL" button appear at above/left. – Jahdai

Would there be a way for me to add sql code in the query where it tells me what rows where not appended to the database because of duplicate emails ? So far Access only adds the non duplicates but it doesn't tell me which ones were unable to be added because they were duplicates. – Microvolt

@alpha_nom, there is no way to have the same insert query tell you what was not added. You would have to write a select query that compares what is in table A with table B. That said, if the keyword "Distinct" was not included in the insert query, I wouldn't think Access would limit the insertion to just distinct values. Are you sure that is what is happening? – Jahdai

Answer 2

Consider directly querying from Excel worksheet as both can interface to the Jet/ACE SQL Engine. Below assumes your worksheet's data begins in A1 with column headers. Integrate below action query in a VBA DAO/ADO call or directly place it in Access' query design (SQL mode).

INSERT INTO myAccessTable ([Last], [First], [School], [Grade])
SELECT [Last], [First], [School], [Grade] 
FROM [Excel 12.0 Xml;HDR=Yes;Database=C:\Path\To\Excel\File.xlsx].[SheetName$]

Byram

Answer 3

DoCmd.TransferSpreadSheet will only transfer a whole sheet. There are a couple of ways you could transfer what you need:

1. Copy the required columns to a second sheet, and transfer that sheet

2. Save the data as a CSV, and import the CSV, where you can specify columns

Nagging