Solved

Create a Binary file from a text file via vba

Posted on 2013-06-24
16
434 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
Comment Utility
Hi, fordraiders.

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

Thanks,
Brian.
0
 
LVL 3

Author Comment

by:fordraiders
Comment Utility
.bin

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

Author Comment

by:fordraiders
Comment Utility
sample file attached...

Want it to be in binary format   .bin...
ee-fileexample.txt
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
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 82

Expert Comment

by:Dave Baldwin
Comment Utility
@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
Comment Utility
ok...sure will let you know...sorry for the vagness.
0
 
LVL 21
Comment Utility
0
 
LVL 3

Author Comment

by:fordraiders
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
Comment Utility
i will be querying against the file...not displaying all the data at once.
0
 
LVL 3

Author Comment

by:fordraiders
Comment Utility
typing a value into a textbox and displaying the data in a listbox.
0
 
LVL 82

Expert Comment

by:Dave Baldwin
Comment Utility
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
Comment Utility
currently using Access...doing ok...just looking to make a smaller footprint on the backend.
0
 
LVL 82

Accepted Solution

by:
Dave Baldwin earned 400 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now