?
Solved

Hardware Clown / Aspiring Code Monkey

Posted on 2011-05-12
21
Medium Priority
?
461 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:fizzlefry
  • 13
  • 8
21 Comments
 

Author Comment

by:fizzlefry
ID: 35749755
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
0
 
LVL 7

Accepted Solution

by:
lewisg earned 2000 total points
ID: 35749759
Looking at the attached file I see a problem with your description. Starting at line 6 there are 10 lines with the same number following PUR. If you were to make a file for each line you would only have one 32706-sales.txt since the first nine would have been overwritten. Since there are repeating numbers I would suggest only having two files.

pseudo code:

open input.txt for input as #1
open items.txt for append as #2
open sales.txt for append as #3

while not EOF #1
  line input #1, strTemp
  if left(strTemp,3) = "HID" append strTemp #2
  if left(strTemp,3) = "BID" append strTemp #2
  if left(strTemp,3) = "SID" append strTemp #3
  if left(strTemp,3) = "PUR" append strTemp #3
wend

close #1, #2, #3

Open in new window

0
 

Author Comment

by:fizzlefry
ID: 35749829
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,
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 7

Expert Comment

by:lewisg
ID: 35750012
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.
0
 

Author Comment

by:fizzlefry
ID: 35750527
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...
0
 
LVL 7

Expert Comment

by:lewisg
ID: 35750820
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
0
 

Author Comment

by:fizzlefry
ID: 35751064
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.
0
 
LVL 7

Expert Comment

by:lewisg
ID: 35751272
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
0
 

Author Comment

by:fizzlefry
ID: 35753683
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...
0
 

Author Comment

by:fizzlefry
ID: 35753710
I just verified, I can get it to work in a windows XP environment, but NOT Win7 PRO x64
0
 

Author Comment

by:fizzlefry
ID: 35754026
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.
0
 
LVL 7

Expert Comment

by:lewisg
ID: 35754242
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.



0
 

Author Comment

by:fizzlefry
ID: 35754262
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!
0
 

Author Comment

by:fizzlefry
ID: 35754579
THANKS AGAIN!!!
0
 
LVL 7

Expert Comment

by:lewisg
ID: 35754714
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...
0
 

Author Comment

by:fizzlefry
ID: 35755250
I may have done something wrong in this, not properly awarding points.
0
 

Author Comment

by:fizzlefry
ID: 35756111
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...!
0
 
LVL 7

Expert Comment

by:lewisg
ID: 35756392
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
0
 

Author Comment

by:fizzlefry
ID: 35756750
FANTASTIC!!!  Thank you again!!!
0
 

Author Comment

by:fizzlefry
ID: 35756758
would you be able to copy and paste the source code for my records.  Like I said, trying to learn.
0
 
LVL 7

Expert Comment

by:lewisg
ID: 35756978
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

0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

864 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