• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 239
  • Last Modified:

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

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
tamada167
Asked:
tamada167
  • 9
  • 6
1 Solution
 
shanesuebsahakarnCommented:
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
 
tamada167Author Commented:
how to import the file into a temporary table or link to the spreadsheet?
0
 
shanesuebsahakarnCommented:
File->Get External Data->Link Tables or Import.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
tamada167Author Commented:
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
 
tamada167Author Commented:
Shanesuebsahakarn,

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

0
 
shanesuebsahakarnCommented:
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
 
tamada167Author Commented:
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
 
shanesuebsahakarnCommented:
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
 
tamada167Author Commented:
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
 
tamada167Author Commented:
do i need to put in the employees' name in the criteria field in the query?
0
 
shanesuebsahakarnCommented:
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
 
tamada167Author Commented:
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
 
shanesuebsahakarnCommented:
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
 
tamada167Author Commented:
will try it and let you know tomorrow
0
 
tamada167Author Commented:
shanesuebsahakarn,

It works, Thank you very much.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now