How to search for a value in Excel
I have a list of numbers in a notepad file and I want to take each number, line by line, and see if they exist in an excel file. I'm trying to find out which numbers don't exist and save those numbers in a new file.
For example: list.txt would look like this: 123456789 987654321 111111111 I don't need to know the location or anything...just whether the number exists. Thanks for any help :) |
Are you trying to write something 'in Excel', to do this, or do you want to parse an Excel spreadsheet, to search for your text? If the latter, I suggest starting with Perl, and use the Spreadsheet::ParseExcel module, or any of dozens of other similar modules. If you want help with programming in Excel, I will wish you luck and bow out of the conversation.
--- rod. |
Writing an OpenOffice.org macro could be another option.
But please tell us what programming language you intend to use. |
I've been tooling around with a VBS script and it seems to be ok so far. Here is what I have so far:
Code:
Const xlValues = -4163 As far as the code...I have many things to work on, such as writing all the values that don't exist into a file but its a good start I think. Thanks for any input guys. |
Take a look at writing a small bash script using "grep". I remember vaguely studying this. Though there probably a macro available somewhere.
|
I'm not real familiar with bash scripts...could you give me a link to some references? Thanks
|
Quote:
I am assuming your text file is list.txt and your excel file is excel.csv Code:
for i in `cat list.txt`; Code:
sort -u newfile > final_result |
This is all fine, as long as you know that the values being searched for are textual data. If the data are some sort of binary formatted data, then grep will not find it, or will very likely return false positive hits. A spreadsheet is not simply a flat text format file, and to treat it as such would be asking for errors. Some kind of programming language that 'knows' Excel files, either built-in, or by way of specially crafted add-on packages should be used. On the surface, the original poster's intention to use Visual Basic seems appropriate, as one would expect there to exist some kind of Excel-specific parsing code in VB.
--- rod. |
A short script to check if a number exists in excel spreadsheet
Here is my small script. Your notepad file is list.txt, your excel file is data.csv (I assume). This script will go thru all entries in list.txt, and print only those entries that are NOT in data.csv .
Code:
var str data ; cat "data.csv" > $data Code:
script myfind.txt Code:
script myfind.txt >> data.csv I wrote this in biterscripting. You can use other languages by following the same logic. To try this in biterscripting, install biterscripting. The installation directions are at biterscripting.com/install.html . Biterscripting is free and installs quickly. Randi |
Hmm. In my mind, 'Excel Spreadsheet' != 'CSV file'. Sure, it can be exported as a CSV file, but it loses functionality, and needs to be done manually. If it were as simple as a CSV file, it would probably be a bash one-liner.
So what is the raison d'être for this biterscript that I've never heard of before? --- rod. |
Hey,
Looks like the OP is using a windows environment full and full. He has a nice solution in VB script. (May or may not work). Next he wants to parse a PDF file. As far as I know, VB Script doesn't have an interface for it. I suggest that he get the APIs for PDF. Or maybe some kind person can point him to a perl module to read PDF. (Like theNbomr's first post). End |
I have hit a big snag! I'm convinced that VB Script is the easiest way to eventually do what i'm trying to do but...
1. I still can't figure out how to collect the values from a pdf file. 2. Is there a way I can take my .txt file and look for the values in multiple files, perhaps search in a folder? |
Offerd -
To summarize previous posts: 1. "Excel" is NOT "CSV" An Excel file has a (closed!) binary format - you need special libraries or programs to read it. A CSV file is a simple text format: you can easily do it in any language. A PDF file is another (different!) example of a closed, binary format. You'll need something else to work with it. 2. You didn't tell us what platform you were on, or what languages you were comfortable using. It appears you're on Windows... ... so you don't have ready access to tools (like Perl, bash, or awk) that'll make your life easier. You can install any/all of these on Windows, but Windows is rather hostile to the *nix philosophy of "small tools that do one thing well, and play together well". Of Perl, bash and awk, you'll probably have the best luck with Perl. Or some other "scripting" language, like Python. 3. If you insist on a scripting language like VBS (or "WSH", or ".bat" files, or whatever particular variant you have) ... you probably owe it to yourself to *at least* get the most capable variant. And that's Windows Powershell: http://www.microsoft.com/windowsserv...l/default.mspx 'Hope that helps .. PSM |
Thanks Paulsm4
However, I am so close to finishing this using VB Script in Windows. I know of a program (that I can purchase) to be able to extract the data I need from the pdf files so the only problem left is to search through multiple files. I am still trying to figure out the "for each file in folder" coding. Here is what I have: Code:
Const xlValues = -4163 I need to change around the loop so that if the value isn't found in the first file, it continues to the next file in the folder until its checked all the files...and then if it still isn't found, writes it to "outfile" and then starts over from the first file for the next value in "text.txt" I hope that makes sense haha. |
Something like this perhaps?
Code:
' Create Filesystem object |
All times are GMT -5. The time now is 02:38 PM. |