|
degbads (m)
|
Hi programmers, please test your knowledge of excel on this, i need to extract information from specific cells, each in about 200 excel documents, i can't do this manually, i could use some help here.
Thanks
|
|
|
|
|
|
a wizzy (m)
|
sounds silly but makes alot of sense, find a way broo,
|
|
|
|
|
|
adewaleafolabi (m)
|
I never liked excel  but i think maybe a macro might be useful or a visual basic app might be of help |
|
|
|
|
|
ThePhantom (m)
|
Simple VBA Script will do that. Give me a little more details and I will post the code for you
|
|
|
|
|
|
ebot64 (m)
|
your explanation of the problem is not very clear. If you can send me a mail describing the problem in details with an attachment of an example Excel sheet containing the data to be extracted, your solution may just be a mail away using VB.net 2005. I am sure VSTO (Visual Studio Tools for Office) would solve the problem. You can reach me on 08034443497 and ceo@ebotech.biz or ebot64@yahoo.comwww.ebotech.biz |
|
|
|
|
|
degbads (m)
|
Guys thanks for your replies, i'll just explain d problem a little further, i have thousands of excel files like the one attached, i need to extract the filled data from specific cells (B1 - B7 of each excel document) and use it in populating a database, but i have thousands of these files, so i can't manually copy and paste, so i need something, anything, code, software, application etc to help me do this.
Please expecting your replies
Thnkx a million
|
|
|
|
|
|
Ibime (m)
|
Wow, that is a hard one. Good luck. I suggest you take a whole day off and do it - but you will have Corpal Tunnel syndrome in your wrist bones by the time you are done. If you had done the originals in Access or SQL, you wouldn't have all these problems. They are all in different files so I don't know of any method to extract the same information from thousands of files at once but there should be one. Hopefully someone can help you with your problem . . . .
|
|
|
|
|
|
Kobojunkie
|
@Poster, if al your excel files have the same format, I believe what you need done is relatively easy.
|
|
|
|
|
|
degbads (m)
|
Guys I keep ' hearing' its easy or its possible but nobody's really saying anything. please someone say something, i need to earn me a raise
thnks
|
|
|
|
|
|
Kobojunkie
|
You need to write a small application that checks for all the excel files within a directory folder, opens and extracts each data in the same way from the excel files and then writes the information to the database table you created to have it written to.
|
|
|
|
|
|
degbads (m)
|
You need to write a small application that checks for all the excel files within a directory folder, opens and extracts each data in the same way from the excel files and then writes the information to the database table you created to have it written to.
Thanks kobojunkie, but thats d point, i want to know what application to write and what language to write it in . |
|
|
|
|
|
Kobojunkie
|
You can write it in Any Language you want to. As long as you have the ability to read Excel files and can write to your current database. Application is just another word for functional code block.
|
|
|
|
|
|
degbads (m)
|
thanks again Kobojunkie, but d point is i'm still coming up in programming, can u give me the code or snippet
|
|
|
|
|
|
Kobojunkie
|
thanks again Kobojunkie, but d point is i'm still coming up in programming, can u give me the code or snippet
Are you a programmer??? If yes, what language do you code in? |
|
|
|
|
|
degbads (m)
|
hi kobojunkie, thanks again, i'm currentlylearning php and javascript, the other programs i know are old ones Qbasic and Fortran,thanks
|
|
|
|
|
|
Kobojunkie
|
OKay, I have little knowledge of PHP at this point but if you follow the logic outlined so far, you should be able to write this easily in PHP.
|
|
|
|
|
|
Akolawole (m)
|
@Poster
I am sure i have linked about 3 excel files sometimes ago by just cross-referencing.
It can be done with no real programming.
|
|
|
|
|
|
Abidemi_A
|
Hi degbads, I can solve your problem using c# or vb.net , but you need to answer the following ques; 1. Which DBMS engine are you using for the output? 2. Do you have all the excel files within the same folder 3. Are there other excel files aside from those that are your data source within the folder(s). You can contact me on info@adten.netCheers |
|
|
|
|
|
|
|
Ibime (m)
|
There are several software that you can use to merge workbooks but they usually cost about $10. Use the trial version I have linked below. It only works for a maximum of five spreadsheets. If it works, you can purchase it if you wish. If you don't like it, just type in "Merge Excel workbooks" into google and it will give you several software that can do it but they all cost about $10.00 http://merge-workbooks.download-242-23985.datapicks.com/ |
|
|
|
|
|
degbads (m)
|
There are several software that you can use to merge workbooks but they usually cost about $10. Use the trial version I have linked below. It only works for a maximum of five spreadsheets. If it works, you can purchase it if you wish. If you don't like it, just type in "Merge Excel workbooks" into google and it will give you several software that can do it but they all cost about $10.00 http://merge-workbooks.download-242-23985.datapicks.com/Thanks ibime i tried the software but it only gathered all the sheets in to one workbook, which isn't what i need i need all the data from certain cells in about 1000 docs gathered in to a single work sheet. @Poster
I am sure i have linked about 3 excel files sometimes ago by just cross-referencing.
It can be done with no real programming.
Thanks also akolawole please how did u do this. Hi degbads, I can solve your problem using c# or vb.net , but you need to answer the following ques; 1. Which DBMS engine are you using for the output? 2. Do you have all the excel files within the same folder 3. Are there other excel files aside from those that are your data source within the folder(s). You can contact me on info@adten.netCheers i need to the data to be on an excel document i believe from that i can convert to any necessary format, all the files and only the files are within the same folder. thnks |
|
|
|
|
|
Kobojunkie
|
Can you show us what code you have so far? I am assuming you have done some work on this and maybe still unable to solve it for one reason or another.
|
|
|
|
|
|
ocherome (m)
|
Hi degbads, I will do it for you. i have started writing the codes i do PHP. If you have them in same workbook, then easy will do it on excel macro using VB my email: admin@naija121.comcontact me i will for futher guild line; everything will be posted here for everybody's benefit Thanks. |
|
|
|
|
|
degbads (m)
|
Thanks Ocherome,
Thats very sweet, looking forward to your post.
|
|
|
|
|
|
|
|
JustJoined (m)
|
This short script in Visual FoxPro might help
**--------------This creates a folder on you c: drive for all the work
m.DataDrive = 'c:'
m.DataPath = '\nairaland'
SET DEFAULT TO c:
IF !DIRECTORY(m.DataDrive)
MESSAGEBOX('Please make sure you have a C: drive mapped')
QUIT
ELSE
IF !DIRECTORY(m.DataDrive+m.DataPath)
MD nairaland
CD nairaland
ENDIF
ENDIF
**----------------Creates a temporary VFP table
CLOSE ALL
CREATE TABLE tmpDUMP FREE (fldName c(100), fldValue c(200))
**----------------Appends from excel sheet
APPEND FROM ? TYPE XL5 SHEET sheet1
**----------------If the excel sheets are uniform and data placed same way in all the documents
**----------------You can do this,
DIMENSION rowval(RECCOUNT())
x = 1
GO TOP
SCAN WHILE !EOF()
rowval(x) = ALLTRIM(fldValue)
x = x+1
ENDSCAN
**------------------------You can do whatever you want here e.g.
**------------------------Insert into SQL Server or reformat and export
IF EOF()
m.cnn = [DRIVER=SQL SERVER;SERVER=] + "127.0.0.1" + [;DATABASE=nairaland;uid=sa;pwd=nairaland]
lnConnHandle=SQLSTRINGCONNECT(m.cnn)
vSQL = "Insert Into dbo.nairalandSample (lastname,firstname,sex,age,country) Select "+"'"+rowval(1)+"','"+rowval(2)+"','"+rowval(3)+"','"+rowval(4)+"','"+rowval(5)+"'"
=SQLEXEC(lnConnHandle,vSQL ,'xx')
ENDIF
|
|
|
|
|
|
ocherome (m)
|
Open microsoft workbook, the same that contains the 1000 sheets, ie you ll be workin on the 1001 sheet now.
on the 1001 sheet is plain and blank, what we will do is to read in the specific cells from 1 to 1000 sheet into the 1001 sheet.
from the sample sheet i got form you. i assume that the rest of the 999 is in that format then we will start.
Game plan: 1001 sheet will contain columns FIRNAME LNAME SEX AGE COUNTRY PHONE ADDRESS
this will give us 1000 line of what we want, then we will use a program to read it into our database.
1) on the 1001 sheet click view => toolbars => control toolbox
on the toolbox you will click on the command button (of course the tooltip will show you that) click once and goto the
far right draw this button on your sheet1001 (the name is commandbutton1)
2) doudle clk on it it will take you to the vb editor
Note: to return back here doulde click on the button
between
Private Sub CommandButton1_Click()
End Sub
you will write these codes
|
|
|
|
|
|
MisterMan (m)
|
Hello. I have been 'offline' for so long. I just read this post now. I wonder if you gotten a solution (I coundn't read through to know if you have gotten the soln). If you have not, just indicate here, and I will see what I can do. Cheers and goodbye
|
|
|
|
|
|
MisterMan (m)
|
Hi JustJoined,
Seems you program in FoxPro. Man, nothing runs like FoxPro. Too bad MS finally decided to 'kill' FoxPro. They really miss the boat there.
|
|
|
|
|
|
ocherome (m)
|
ATTACHED HERE IS THE SOLUTION OF THE SAMPLE GET BACK TO ME IF ANY PROBS
|
|
|
|
|
|
MisterMan (m)
|
Here is my own solution. I thought you would e-mail me so to be able to download the stuff. I have created an executable file. First off, you will need to install, then other things run expressly. In most cases, just click on the default. After Installing, go to the folder where you install the program and double click. It asks for the folder where the Excel files are, and in minutes, it completes.
Because I don’t know what you really want to do with it, I saved the results both in .XLS file and FoxPro’s .dbf file. You just save the Excel file and use it the way you want. A sample screenshot is attached.
One caveat however. The phone number is in number format. I have not been able to lay my hand on the documentation in VBA to convert the number to string. The problem actually is the way Excel sees the number. It reports it as number whereas it is actually a string. Apart from that, it runs OK.
|
|
|
|
|
|
MisterMan (m)
|
Here is my own solution. I thought you would e-mail me so to be able to download the stuff. I have created an executable file. First off, you will need to install, then other things run expressly. In most cases, just click on the default. After Installing, go to the folder where you install the program and double click. It asks for the folder where the Excel files are, and in minutes, it completes.
Because I don’t know what you really want to do with it, I saved the results both in .XLS file and FoxPro’s .dbf file. You just save the Excel file and use it the way you want. A sample screenshot is attached.
One caveat however. The phone number is in number format. I have not been able to lay my hand on the documentation in VBA to convert the number to string. The problem actually is the way Excel sees the number. It reports it as number whereas it is actually a string. Apart from that, it runs OK.
|
|
|
|
|
|
![Nairaland [Nigerian Forum] Home](http://www.nairaland.com/Themes/default/images/english/home.gif)
Author
sample.xls (17 KB - downloaded )
