Solved

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

Posted on 2004-09-10
15
224 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

706 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now