Export random excel data to access table 1

[Finedean]

Hi,
is there a way to map/ export random excel data to an access table?
Thanks in advance.
dean

 

[kooley]

If you are looking to just bring data from a spreadsheet or even an entire workbook, start in Access & go to File, Import and navigate to your Excel file. It is very user friendly & walks you through. You may find that depending on the formatting of your Excel file, you may have to do the import a couple times to get exactly what you are expecting within Access.

Hope this helps!

 

[barny2006]

if the table already exists in access, the first row of the data in excel should have the same column names as the table, otherwise the import will error out.
if the table doesn't exist in access, you can import it into a new table. but the first row has to have some kind of valid column names. you can also do this with vb script:
here's the code to do it in vb script:

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open _
   "Provider = Microsoft.Jet.OLEDB.4.0; " & _
   "Data Source = " & db_path
xcl_file_path = "c:\folder\sample.xls" 
table_name = "sample_tbl_in_access"

sql_string = "SELECT * INTO " & table_name & " FROM [Excel 8.0;HDR=Yes;" & _
        "Database="& xcl_file_path & "].[sheet1$]"

 

[Finedean]

Thank you kooley and Barny2006 for your help. Importing from excel to Access is not an issue. I apologize for posting the wrong issue.

What I am trying to do now is import some data (not all data) from excel to an access table.
I need to import the following form excel to an access table:
Plan ID xxx
Participant count 132
Computed asset balance: $1,528,172.03
Computed fee $2,865.32

Here is an example of the excel sheet where the data is being imported from:

Plan ID: xxxx Artemide, Inc. 401(k) Profit Sharing Plan
Report period: 7/01/2006 to 9/30/2006
Run date: 10/9/2006 1:12:23PM
Fee schedule: 75BP/4500 75BP / 4500 Annual
Distribution fee amount: $0.00 Count: 1 Amount due: $0.00
Loan fee amount: $0.00 Count: 0 Amount due: $0.00
Transfer fee amount: $0.00 Count: 7 Amount due: $0.00
Withdrawal fee amount: $0.00 Count: 0 Amount due: $0.00
Fee rate: $0.00
Participant count: 132
Minimum asset fee assessed: $0.00
Computed asset balance: $1,528,172.03
Rate: 0.001875 basis points
Computed fee: $2,865.32

 

[gkrenton]

I'm no expert & I may be not understanding what you want but what it sounds like you want to do is to link your excel spreadsheet to your DB?
Linking the spreadsheet is convienent esp if it updates regularly & you need/want to pull data into some query or table I'd just link the excel spreadsheet into the table listing
File, get external data, link tables.

If the data doesn't change & you just need the core data to use somewhere else in your access DB then do the same but select Import & then a wizard will walk you through creating an access table with this data in it.

gina

 

[Finedean]

Thanks.
What I would like to accomplish is import ONLY the following from excel:

Plan ID Part count Computed Asset balance Computed fee
xxxx 132 $1,528,172.03 $2,865.32

as you can see that excel sheet data is scattered all over. it is not organized into columns and rows.

I hope this clear.

 

[gkrenton]

I'm sort of a self taught hacker at access, but guess I'd still bring in the spreadsheet & then create a query/view within Access that reports only that data within the linked table that I wanted to report on/do db comparisions. Isn't all Excel data ultimately in a cell which is defined by row & column?

Guess it also depends on what & how you're trying to use the 4 pieces of data you're mentioning.

My 'easy' solution is to bring in the table so that I can then selectively use the data within the spreadsheet with the DB functions.

What is it you're doing with the data once it's in Access? That may help identify what makes the most sense to bring in the data.

- gina

 

[PaulBricker]

Just a thought and it may not be worth anything, but can you create a reference between cells on different worksheets and only bring the data you want to the second worksheet. You would use an expression like
=Sheet1!A5
Then you would just import the second worksheet.
It's a long shot but it might work.

Paul

 

[Finedean]

Hi everyone,
First of all I really appreciate you taking the time to help me.

I am on the next stage because I solved one problem. I have no clue how to do this one.
If I have an access table like this:

Field1 Field2

Plan ID: 123 Namebbb
ParCount 130
Compfee 158798
Computed asset balance 12587
Date 6/30/2006
Fee rate 0
Asset Fees 0

Plan ID: 456 Name DDDD
ParCount 129
Compfee 158799
Computed asset balance 12588
Date 7/30/2006
Fee rate 0
Asset Fees 0

Plan ID: 023 Namebbb
ParCount 132
Compfee 158794
Computed asset balance 12570
Date 6/30/2006
Fee rate 0
Asset Fees 0
ect...

is it possible to have another access table that contain only the following:

PlanID ParCount Compfee Computed asset balance
123 130 158798 12587
456 129 158799 12588
023 132 158794 12570


Thank you in advance

 

[gkrenton]

You can create a select query which makes a view & only include those fields you're interested in by only choosing those fields from Table1.

Or you can do a 'create' table where you create another table from the data within this table.

Either solution will give you the new table/view it may be more a matter of what you're doing with the data after you create the table or view as to which method makes the most sense.

- gina

 

[Finedean]

Hi Gina,
When I run select query:
SELECT Sheet1.Field1, Sheet1.Field2
FROM Sheet1
WHERE (((Sheet1.Field1) Like "Plan ID*" Or (Sheet1.Field1) Like "Computed asset balance*" Or (Sheet1.Field1) Like "Computed Fee*"));

this is what I get:

Field1 Field2
Plan ID: xxx Namebbb
Computed fee: 0
Computed asset: 1445365.99
Computed fee: 2710.06
Plan ID: 1231NameCCCCC
Plan ID: 169 Quad
Computed fee: 0
Computed asset: 2388263.51
Computed fee: 2865.92

Iam looking to put it in this format:
PlanID Computed fee Computed asset Computed fee
xxx Namebbb 0 1445365.99 2710.06
1231NameCCCCC
Plan ID: 169Qu 0 2388263.51 2865.92

Thanks

 

[gkrenton]

I think what your saying is you want a horizontal layout?
Where the column headings are the planID, Computed asset & Computed Fee data.

A couple ways sort of come to mind.
First if that's all you really need & all the Data is in excel you could create a pivot chart in excel in a seperate worksheet & skip the access piece all together.

If you are using this data in such a way that it needs to be in access then I'd suggest maybe create a 'table' (say PlanIDinfo) with the columns you need. Then create an 'append' query where you take the data from the table that holds the core information & link the to the column headings in your newly created table 'PlanIDInfo'.

The only 'gotcha' on this solution is you'll want to delete all data from the table 'PlanIDInfo' before re-running the append query since otherwise it just adds the rows into the table.

There is also a way to 'pivot' data in Access but I've never done it so can't say if it would solve your dilemma or not.

- Gina