Solved

How to get data from excel file and store it to the table/query

Posted on 2004-09-10
15
226 Views
Last Modified: 2008-03-04
Hi Everybody
I have a excel file that contain at lot of information about each employees activities and what they did, there is a line “total made count for employee: employee’s name = 19”,  how to use query or code to get the “employee’s name” and “19”  to the table/query.


0
Comment
Question by:tamada167
  • 9
  • 6
15 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12029488
Import the file into a temporary table (or link to the spreadsheet(, and then you can run an append query to append the data that you are interested in. For example, you can just append data where the field starts with the text "Total made count".
0
 

Author Comment

by:tamada167
ID: 12029599
how to import the file into a temporary table or link to the spreadsheet?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12029625
File->Get External Data->Link Tables or Import.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:tamada167
ID: 12029703
couldn't link to the file, keep getting the following msg.
this wizard is unable to access information in the file D:\test.xls" please check that file exists and is in the correct format
0
 

Author Comment

by:tamada167
ID: 12029869
Shanesuebsahakarn,

finally linked the excel file, how to make a append query to append the data that i need.

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12030657
How many columns does your spreadsheet contain (that you want to get data from, that is)?

How is the data in those cells formatted? We need to identify just the rows you want and ignore the rest.
0
 

Author Comment

by:tamada167
ID: 12047732
the spreadsheet has many columns, but the data that I needed is in the first column. this column has many rows(around 750 rows) but there only 20 rows of data that I want. all these rows contain "total made count for employee: employee’s name = #”
employee's name only appear once.
example,
total made count for employee: Delgado, mike = 19
total made count for employee: Brace, Nancy = 10
total made count for employee: Ellis, frank = 9
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12047882
OK, assuming that the lines always start with "total made count for employee: ", you can use this expression to get the employee name:
Mid$(Left$([MyField],instr([MyField],"=")-2),32)

This will get you the number:
Mid$([MyField],Instr([MyField],"=")+2)

Combining this into a query:
SELECT Mid$(Left$([MyField],instr([MyField],"=")-2),32) As EmpName, Mid$([MyField],Instr([MyField],"=")+2) As EmpValue FROM MyTable WHERE [MyField] Like "total made count for employee:*"

Change MyField and MyTable to the name of your field and table. Then run this query to see if it gives you the right values. If it does, you can just turn it into an Append query and there you go!
0
 

Author Comment

by:tamada167
ID: 12048085
SELECT Mid$(Left$([total made],InStr([total made],"=")-2),32) AS EmpName, Mid$([total made],InStr([total made],"=")+2) AS EmpValue
FROM sheet1
WHERE (((sheet1.[total made]) like  "total made count for employee:*"));
 
return no value and employee's name when run the above query
0
 

Author Comment

by:tamada167
ID: 12048106
do i need to put in the employees' name in the criteria field in the query?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12048133
No, you don't need to put a criteria in - does it actually return the employee name? I've assumed that the text is exactly as you've given, that is:

total<space>made<space>count<space>for<space>employee:<space>Delgado, mike<space>=<space>19

It won't work unless the spacing is as you've put it (and no leading spaces).
0
 

Author Comment

by:tamada167
ID: 12048413
ok, there extra space between : and the employee. now got the name and their number, but there "e:" come before each name, know why?
e: Delgado, Mike
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 150 total points
ID: 12048521
Ok, change it to this:

SELECT Mid$(Left$([MyField],instr([MyField],"=")-2),34) As EmpName, Mid$([MyField],Instr([MyField],"=")+2) As EmpValue FROM MyTable WHERE [MyField] Like "total made count for employee:*"

Does that do it?
0
 

Author Comment

by:tamada167
ID: 12048534
will try it and let you know tomorrow
0
 

Author Comment

by:tamada167
ID: 12056258
shanesuebsahakarn,

It works, Thank you very much.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Normalization of a table 19 74
unable to create table-based data macro in MS ACCESS 2013 11 25
Calculation in Access 5 26
Criteria for Date for DCount 4 23
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question