Solved

Create a Binary file from a text file via vba

Posted on 2013-06-24
16
480 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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

717 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