?
Solved

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

Posted on 2004-09-10
15
Medium Priority
?
232 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

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 600 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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

764 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