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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top