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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,802
Messages
5,833,751
Members
430,229
Latest member
TestableEmu263

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
Top