-
I am having a bit of a problem with a program I am working on. The person I am writing it for has asked if I could take a file in text format and copy certain information from it into different tables in a database. I thought "No problem". I ran into something that I cannot figure out how to fix though. There are certain records in the text file that do not contain 2 fields that all the other records have. So when the program reads the file and starts assigning the fields to variables, it doesn't recognize that this has happened, and I get a runtime error. Here is what my code looks like:
Do Until EOF(3)
Input #3, pstrIO, pstrPCMSid, pstrForeignSid, pstrDelete1, pstrDelete2,pstrCalls, pcurAirDollars, pcurTax1, pcurTax2, pcurSurDollars, pcurTollDollars, curTotalDollars, pstrSurcharges, pstrNS, pstrDelete3, pstrAirMou
Some of the records are missing the pstrdelete3 field and pstrAirMou field. If the program reads a record that is missing this field, it takes the first two fields from the next record and assigns these variables with those fields.
Is there a way to make it so that the program will stop reading a record when there are no more fields? Any help is greatly appreciated!!!!!
-
Without seeing the text file you're trying to extract from, it's impossible to say. Usually when you're trying to do something like this, each field will be separated with a TAB character. If a field is blank, then that also has a TAB character to replace it. So, if you find three TAB characters next to each other, it will tell you that a field is blank - The TAB character before the field, the one contained within the field, and the one after it.
Unfortunately, a computer can't tell if there are fields missing mid-record, or if the missing fields should actually be at the end. All the computer knows is that two fields are missing, but there's little way of knowing where they should be.
Any help?
-
??
Well, that's kind of what I thought. The text file coming in is a comma delimeted file. It's for a cellular company. The records with fields missing are rejected calls and the fields missing have to do with money. Because they are rejected they will not contain the last two fields because they pertain to calls that were actually accepted by this cellular company. I was kind of hoping that there would be a way to read a record until it reaches the return character, vbCrLf, but I couldn't figure out a way to make that happen. I have worked around this problem with the user, by making them open up the file in Excel and filling those empty records with entering 0's into them, but it just doesn't seem like good programming to me. Oh well, thanks for the help!
-
Instead of using Input to read the data directly into variables use the Line Input statement to read the entire line into a string variable.
Then use the split function to break the line into parts. You can then determine is any fields are mssing.
-
CSV Files
If it is a CSV file, then open it as a text file. Then read one line at a time. Once you have a line read in, you will have to break it up yourself, depending on where the commas are, and place each field in the appropriate variable. It sounds a lot harder than it actually is.
-
hmm
Okay - that sounds like a good idea. So what is the syntax for reading a line and the syntax for the split function. I've heard of them, but I'm only in my second semester of Visual Basic programming, so I've never used that. Thanks for the help though!!!! :)
-
Sample Code
Code:
dim lsFields() as string
dim lsLine as string
Line Input #3 lsLine
lsFields = Split (lsLine, ",", -1)
After this runs the lsFields array will contain the values in the file. By using the ubound function on the array you can determine the number of fields that were read in.
-
Something like this
Something similar to the following will probably do the trick
Code:
Dim myStr As String
Dim myStrArray() as String
Do Until EOF(#1)
Line Input #1 , myStr
myStrArray = Split(myStr, ",")
putRecords
Loop
Private Sub PutRecords()
'Make sure all the records exist. If the last two are missing this will give you an empty string in each.
ReDim Preserve myStrArray(15) As String
pstrIO = myStrArray(0)
pstrPCMSid = myStrArray(1)
pstrForeignSid = myStrArray(2)
pstrDelete1 = myStrArray(3)
pstrDelete2 = myStrArray(4)
pstrCalls = myStrArray(5)
pcurAirDollars = myStrArray(6)
pcurTax1 = myStrArray(7)
pcurTax2 = myStrArray(8)
pcurSurDollars = myStrArray(9)
pcurTollDollars = myStrArray(10)
curTotalDollars = myStrArray(11)
pstrSurcharges = myStrArray(12)
pstrNS = myStrArray(13)
pstrDelete3 = myStrArray(14)
pstrAirMou = myStrArray(15)
End Sub
[Edited by Iain17 on 04-26-2000 at 04:44 PM]
-
Thank You
I had almost given up on this project. I thank you all for helping me out! It is greatly appreciated!!!
Have a great day!
