Link to home
Start Free TrialLog in
Avatar of fizzlefry
fizzlefry

asked on

Hardware Clown / Aspiring Code Monkey

Please forgive the amateur-ish nature of this.  I am trying to broaden my horizons.  I've been charged with a task and I'm having some trouble accomplishing it.  I've been picking through code to come up with a viable solution.  Here it is:

I have an ASCII file produced by a manufacturing software.  This file contains 2 parts:  items and sales.  I need to split this file, based on the leading column (HID and BID to 1 file, everything else to the other file, (SID and PUR).  The file name for the HID / BID file should be based on the number that follows the HID, no leading zeros, with a "-items" (i.e. HID00458001 = 458001-items.txt).  The second file name is based on the same constraints, except it has "-sales" as a suffix (i.e. HID00458001 = 458001-sales.txt).  If file names are too complex, I'd be happy if 1 said "sales" and 1 said "items".

I know this may seem like an open request.  But I would be eternally grateful for anyone that could help me.  Like I said, I'm trying to expand my horizons, so any help would be more than appreciated!


test
Avatar of fizzlefry
fizzlefry

ASKER

So you don't think I'm a complete freeloader, I managed to get the following code to partly accomplish what I wanted (it would remove all BID records, and create a simple new file).  But I couldn't reverse the logic.  Basically, I'm grabbing at straws and would love some help. Here's what I got:

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\test\Test", ForReading)

Do Until objFile.AtEndOfStream
    strLine = objFile.ReadLine
    If InStr(strLine, "BID") = 0 Then
        strNewContents = strNewContents & strLine & vbCrLf
    End If
Loop

objFile.Close

Set objFile = objFSO.CreateTextFile("C:\test\output\sales.txt", ForWriting)
objFile.Write strNewContents

objFile.Close
ASKER CERTIFIED SOLUTION
Avatar of lewisg
lewisg
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
lewisg...  Thank for you responding!!!  I am so grateful to hear from you.  The repeating number is a customer number.  I will use that later to calculate the totals (that's another whole thread I'm sure).  But what I need to do is separate the items from the sales, then I will need to import them into something (perhaps Access since I can work in that) and evaluate the sales data.  But all I'm looking to d, at this level, is separate the files.  So, from my example:

file 1: 458001-items, contains the 4 BID items, with the HID on top.
file2:  458001-sales, contains the 34 SID / PUR records.

Like I said, my ultimate goal is to compare the files against each other and obtain a result.  But I'm crawling, before walking.  

I hope I didn't misunderstand your response.  Like I've said, I'm just very novice and trying to do what I can to work outside of my comfort zone.

I truly look forward to your response,
Since you are headed for Access you might want to think about using 4 output files. From the test data it looks like lines beginning with each of the 3 letter identifiers have different structures. Access has the ability to import table data like you already have in your test file. So if you have 4 different files then you could have 4 different saved import specifications.

file 1: HID.txt, contains the HID record
file 2: BID.txt, contains the 4 BID records
file 3: SID.txt, contains the 3 SID records
file 4: PUR.txt, contains all the PUR records

Your next step is figuring out where to land the data in Access table(s). From the looks of things you might want 4 tables.

I know it is bothersome but you need to figure out what information you need to extract in order to figure out the best way to store this data.
Ultimately, I will need to verify quantity sold, based on each report submitted.  Each wholesaler will submit a report of all their customers and what was sold.  But the problem is when it comes to the item actually sold, it can be listed by either the UPC or the vendor part#, hence the reason the BID exists.  The BID is what I use to find out how that specific wholesaler submits the data, and allows me to "decode" it to a standard format.  The final step is going to be to see totals sold x costs to figure out rewards due.  But once I get it into access, I think I can manipulate it through queries and reports.  

As I see it, I will need to catalog sales for each customer, based on their customer number (PUR32706, etc...)  Or I could just have it add the customer information as part of the sales record.  In access, when it writes the sales, there are columns that will list the customer information (and may repeat for each PUR sale).  But what I'll need to do is select a wholesaler (458001 for Company Q).  Then run a report to show how many sales, broken up by each customer.  There will be some math to establish the cost vs. sales.  Bu, like I said, I think I'm good once I get it in Access.  I just can't break up this file.

I'm really sorry to make this so difficult.  If it was already in a CSV or something I could work with, I'd be fine.  But these damn flat-files....  They KILL me!!!!  

I can't thank you enough for your time and look forward to your reply...
Your problem isn't flat file vs CSV it is that you have 4 different record types mixed in a single file. The need to be separated so they can be imported to Access.

Here is a working QuickBASIC program:
DIM strTemp AS STRING

OPEN "test" FOR INPUT AS #1
OPEN "HID.txt" FOR OUTPUT AS #2
OPEN "BID.txt" FOR OUTPUT AS #3
OPEN "SID.txt" FOR OUTPUT AS #4
OPEN "PUR.txt" FOR OUTPUT AS #5

DO WHILE NOT EOF(1)
  INPUT #1, strTemp
  IF LEFT$(strTemp, 3) = "HID" THEN PRINT #2, strTemp
  IF LEFT$(strTemp, 3) = "BID" THEN PRINT #3, strTemp
  IF LEFT$(strTemp, 3) = "SID" THEN PRINT #4, strTemp
  IF LEFT$(strTemp, 3) = "PUR" THEN PRINT #5, strTemp
LOOP

CLOSE #1, #2, #3, #4, #5

Open in new window


This code should run in just about any version of BASIC you can find. A decent free version is here: http://www.qb64.net/forum/index.php?topic=568.0 . The help on QB64 isn't as good as I remember from M$'s versions but it works for some Q&D like this.

I attached an compiled EXE made by QB64. If you put this EXE in a directory with a data file named test it will create the 4 output files. This will allow you to move on the the Access part of your quest.
test.exe
Again, I can't thank you enough for your response.  You are right though, separating the file into the four elements was critical for manipulation.  I am having a problem with your exe though:

The program can't start because SDL_image.dll is missing from your computer.  Try reinstalling the program to fix this program.

I was hoping you could take me just a little bit further.  I'm running WIndows 7 Pro.
Sorry about that!

I guess QB64 EXEs need DLLs to run. You could download the zip file in the link above and put all the files in a directory with the EXE and it would likely run.

I hunted around and found my good old Microsoft QuickBASIC v4.5 and made a EXE with it. Tested on Win7 Pro. Runs like a champ! Not bad for a development environment released in 1988. Still works on the current OS. Try that on anything with a fruit on it!

Look at the file sizes! The "old" QB45 makes a 31K EXE and the "new" QB64 makes a 1.1M EXE that requires DLLs to run. We have come a long way...
TEST.EXE
I regretfully am still having problems.  My system is 64bit and I keep getting errors to check my version for 32 or 64bit.  I have a copy of Visual Studio 2010 and tried creating a new VB project and dumping the code in there myself, however I keep getting all these errors about "Declaration expected." for the first word in every line.  I feel very lowlevel but I have no idea what I'm doing!!!  I just need you to come a little further with me...
I just verified, I can get it to work in a windows XP environment, but NOT Win7 PRO x64
Just so you know, I want to understand all this.  I am more than appreciative (and willing to award points) for your solution.  But I want to be able to understand it myself.  Build on the knowledge you're giving me.  My hopes are to bring the importing into access directly through the program.  Since I essentially know the fixed column width and everything.  I hope to automate that process as well.  That's why I got the VS2010 and am trying to compile this myself.  Althought I would like the program you supplied to be able to work on my laptop, I am more than content running it through my virtual XP.  Again, any help is VERY much appreciated.
My W7 is 32 bit.

Never liked VS since it isn't backwards compatible, as you have just experienced. I still use VB6 for most of my projects but I'm migrating to Real Studio. No matter what you use the steps are the same:

* Open files.
* If you are not at the end of the input file, read a line from the input file .
* Depending on the first three letters of the line write the entire line to the appropriate output file.
* Close the files.

Since this appears to be your first project of this sort I would discourage you from getting too hung up in the details of any one part of the project. Once you have a way to get the files into Access move on to getting your tables figured out and then to what you want to do with them. The reason I say this is because you may find that you need additional information in the tables that is not in the files (date, file source, operator, shipping info...) that would be easy to incorporate in the import process. Just like writing a story it is usually best to get an working outline before polishing the prose.



you make perfect sense.  Thank you again for everything!  I really appreciate you making my first experience on this site a good one.  You are truly deserving of your rank!
THANKS AGAIN!!!
Glad to help!

I'm not sure if closing the question is required for accepting an answer. I usually get a "Good Answer!" email on questions but on this I got a "A question you participated in will be closed in four days" email. I usually only get those on questions that a resolution was never found. I'm puzzled...
I may have done something wrong in this, not properly awarding points.
lewis...  one more issue.  I noticed, when I run this amazing program, it is doing one odd thing.  When I use it one 1 test file, the HID text is fine.  But when I use it on another, the HID is missing all the address information.  I have no idea why.  Please let me know.  I am including the problem file and the problem result. test HID.TXT

But like I said, if I use a different source file, it captures the entire HID.

Help...!
The comma in  "AWESOME, INC." is being interpreted as the end of the string by the INPUT function.

The attached EXE uses LINE INPUT instead. It's been a few years...
TEST.EXE
FANTASTIC!!!  Thank you again!!!
would you be able to copy and paste the source code for my records.  Like I said, trying to learn.
DIM strTemp AS STRING

OPEN "test" FOR INPUT AS #1
OPEN "HID.txt" FOR OUTPUT AS #2
OPEN "BID.txt" FOR OUTPUT AS #3
OPEN "SID.txt" FOR OUTPUT AS #4
OPEN "PUR.txt" FOR OUTPUT AS #5

DO WHILE NOT EOF(1)
  LINE INPUT #1, strTemp
  IF LEFT$(strTemp, 3) = "HID" THEN PRINT #2, strTemp
  IF LEFT$(strTemp, 3) = "BID" THEN PRINT #3, strTemp
  IF LEFT$(strTemp, 3) = "SID" THEN PRINT #4, strTemp
  IF LEFT$(strTemp, 3) = "PUR" THEN PRINT #5, strTemp
LOOP

CLOSE #1, #2, #3, #4, #5

Open in new window