?
Solved

Create a Binary file from a text file via vba

Posted on 2013-06-24
16
Medium Priority
?
485 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 1600 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 400 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

777 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