Solved

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

Posted on 2004-09-10
15
228 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

749 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