Solved

Create a Binary file from a text file via vba

Posted on 2013-06-24
16
462 Views
Last Modified: 2013-06-27
I need vba code to create a binary file from text file

I have a text file..

Pipe delimited format

material_no|alt_material_no_Green
4TM77|4YM42
5E103|5E104
5E081|5E080
2F141|4ZF10
4KK81|4KK82
2F145|2F132
3UM46|4KK77
5A283|4KK77
3W230|4XM33
3W231|4XM35
4W174|4XM37
4W075|4XM39
4W076|4XM43
6W693|4XM57
6W694|4XM59
6W695|4XM61
5E145|5E077

Is there a way to create a binary file from this text format ?


Thanks
0
Comment
Question by:fordraiders
  • 8
  • 4
  • 2
  • +2
16 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 39273212
Hi, fordraiders.

Sure. What format do you want? A sample file would be great!

Thanks,
Brian.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39273226
.bin

The file example is in the question part.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39273239
sample file attached...

Want it to be in binary format   .bin...
ee-fileexample.txt
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 26

Expert Comment

by:redmondb
ID: 39273246
Thanks, fordraiders.

".bin" doesn't uniquely identify the format - there are lots of different types of ".bin" files. Can you tell you more about it? What will it be input to?

Sorry I didn't make it clear - I am looking for a sample of the output!

Thanks,
Brian.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39273299
@fordraiders, you didn't give us any details the last time you asked this question.  There are too many possibilities to give you an answer without more information.  What program will use the 'binary' file?
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39273467
ok...sure will let you know...sorry for the vagness.
0
 
LVL 21
ID: 39273499
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39273839
I will be using Excel 2010 to display the data on a userform.
Right now the text file are large. 2.3gb
Trying to reduce them in size to be more manageable.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39273869
You don't need to convert them to binary since that actually won't do you any good.  You will probably need to split them into much smaller pieces than 2.3GB.  Here are the specifications for Excel 2010: http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx?CTT=5&origin=HP005199291   Even if you have enough memory and the file fits within the limits,  It will take a very long time to create the display and with a 1,048,576 rows, nobody will take the time to go thru them.

What are you actually trying to do with these files?  There must be a better way than trying to display them in Excel.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39273878
i will be querying against the file...not displaying all the data at once.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39273885
typing a value into a textbox and displaying the data in a listbox.
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 39273922
I think Excel requires you to load that data into a spreadsheet to do anything with it and you may run into the limits built-in to Excel.  You may want to look at using a database like SQL Server Express which is made for searching and sorting large data files.  SQL Server Express is a free download from Microsoft.  You may still be able to use Excel as the front end but without having to wait for the entire file to be loaded.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39274704
currently using Access...doing ok...just looking to make a smaller footprint on the backend.
0
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 400 total points
ID: 39275645
Actually I suggest making the big footprint on the backend in SQL Express.  A real SQL server does not have to load the entire database to use it.  You should probably get a much better response.  The key is to take the time to make the appropriate indexes that allow SQL Server to go directly to the data instead of searching through the entire table to find an item.
0
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 100 total points
ID: 39276020
Hi,

I will be using Excel 2010 to display the data on a userform.
Right now the text file are large. 2.3gb
Trying to reduce them in size to be more manageable.

Why not store the data in multiple (&, hence, smaller) text files?

For example,

a) Nine (or Ten, depending on your data) files based on the first character within the "material_no" column (digits 1 to 9, or 0 to 9), or

b) Multiple files grouped by the first two characters within "material_no" (so all data that is prefixed "5L" is in one file, all data prefixed by "5F" is in another, a file for "1A", another file for "4T", & so on).

Then, the Userform you provide can use a textbox & a listbox, or a drop-down listbox, to capture the first character, or first two characters, of a "material_no", locate the correct file (based on filename) & pre-load the content from the appropriate file for selection.

If the user removes a character (or both characters), the available data to select is cleared.

This can also be achieved using a dedicated database, as mentioned above, of course.

BFN,

fp.
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 39282774
Thanks
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question