tamada167
asked on
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.
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.
shanesuebsahakarn
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".
ASKER
how to import the file into a temporary table or link to the spreadsheet?
File->Get External Data->Link Tables or Import.
ASKER
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
this wizard is unable to access information in the file D:\test.xls" please check that file exists and is in the correct format
ASKER
Shanesuebsahakarn,
finally linked the excel file, how to make a append query to append the data that i need.
finally linked the excel file, how to make a append query to append the data that i need.
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.
How is the data in those cells formatted? We need to identify just the rows you want and ignore the rest.
ASKER
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
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
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
This will get you the number:
Mid$([MyField],Instr([MyFi
Combining this into a query:
SELECT Mid$(Left$([MyField],instr
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!
Mid$(Left$([MyField],instr
This will get you the number:
Mid$([MyField],Instr([MyFi
Combining this into a query:
SELECT Mid$(Left$([MyField],instr
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!
ASKER
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
FROM sheet1
WHERE (((sheet1.[total made]) like "total made count for employee:*"));
return no value and employee's name when run the above query
ASKER
do i need to put in the employees' name in the criteria field in the query?
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>cou
It won't work unless the spacing is as you've put it (and no leading spaces).
total<space>made<space>cou
It won't work unless the spacing is as you've put it (and no leading spaces).
ASKER
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
e: Delgado, Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
will try it and let you know tomorrow
ASKER
shanesuebsahakarn,
It works, Thank you very much.
It works, Thank you very much.