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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,
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.
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.
ASKER
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...
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:
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
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
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
ASKER
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.
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 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
ASKER
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...
ASKER
I just verified, I can get it to work in a windows XP environment, but NOT Win7 PRO x64
ASKER
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.
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.
ASKER
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!
ASKER
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'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...
ASKER
I may have done something wrong in this, not properly awarding points.
ASKER
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...!
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
The attached EXE uses LINE INPUT instead. It's been a few years...
TEST.EXE
ASKER
FANTASTIC!!! Thank you again!!!
ASKER
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
ASKER
Const ForReading = 1
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.Fi
Set objFile = objFSO.OpenTextFile("C:\te
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:\
objFile.Write strNewContents
objFile.Close