Search flat-file (ascii/txt), return 1 line of delimted data

AH·C

New Member
Joined
Nov 27, 2005
Messages
4
I'm trying to figure out the following:

1) Take the Lookup Value in Cell B2, i.e. "0001324"

2) Find the match in a text file with PSV laid out thusly over 1.8 million lines-- PartNumber may, or may not, be in ascending order:
PartNumber|ItemName|SoS|Unit|Price|Core
9999999|Fluid, Brake,Silicon|E|GAL|$56.00|N
0000001|Gadget,Widget|A|DZ|$99.99|N
0001324|Starter,..Pre-Lubed|A|EA|$1500.00|Y*
0001322|Starter,Standard|B|EA|$3570.00|Y


*2 Periods represent 2 spaces, due to text TRIM feature in posting.
Bonus: ensure that there is only one space after each comma in text string. (Not required, but would be nice feature)


3) Return the matching line across Cells that may or may not match the text file order.
i.e: ItemName to D2, SoS to C2, Unit to E2, Price to G2 & Core to F2

4) Loop until there are no more Lookup Values in Column B.
Note: Since the Invoice could be one page or 20, I'm thinking of using
the PrintArea to establish the length of Col B and backing off "x"
number of Rows to get to the actual last row of the invoice table itself,
or would simply determing the last cell of worksheet be more efficient?


TIA

Andy
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Andy

In the absence of another reply:

You would be much better advised to use Access to do what you require due to the size of your text file (I presume your data is 1.8million records?). I also suspect that you suggested searching the text filebecause you are aware of the row limit in an Excel spreadsheet (approx 65,000). This limitation would mean that should you persevere with an Excel-based solution, then you would probably have to go with reading in a chunk of the text file at a time (doesn't have to be written to a worksheet, it could be imported as a filescript in memory and processed still within memory) but it will be slow - much slower than utilising Access.

Do you have access to Access?

Best regards

Richard
 

AH·C

New Member
Joined
Nov 27, 2005
Messages
4
Richard,

Correct on all accounts.

Yes I have access to Access (2K, XP & 2K3), however, my question would remain the same, since I'm relatively clueless about that app. I found several examples that export the entire table, rather than selecting and returning the single matching recordset.

I was leaning towards the text file, since I figured it was more "universal" than access. I take it from your reference to "filescript in memory" that there is no practical/efficient method for simply reading line by line until a match is found.

Poking around with Access, I did several imports using the wizard, sometimes getting error messages. But I think I'm getting the hang of it, since I didn't have errors on the last couple versions. I'm not too sure about the ID Key, since my errors revolve around that. If my part numbers are unique, then I should be ok indexing the PN as non-duplicating TEXT Fields (preserving the leading 0s)?

In anycase, if you can assist, it would be greatly appreciated.

Thanks & best regards,

Andy
 

Watch MrExcel Video

Forum statistics

Threads
1,118,530
Messages
5,572,669
Members
412,481
Latest member
nhantam
Top