I have a strange text file that contains values separated by a 2 character abbreviation. ie. "PN" and "QT" I can import just fine, but I can't seem to get the data to separate into separate columns.
This is what the text file looks like.
PN=DSAI6612
QT=3
PN=CT22L
QT=3
after I import and filter through a query it looks like
Field1 Field2
PN DSAI6612
QT 3
PN CT22L
QT 3
Problem is, I need it to look like this
Field1 Field2
DSAI6612 3
CT22L 3
I don't know what I'm missing.
Please help!!
Thank you in advance
Full Version: Importing Delimited With Text?
Hi,
Without a "key" to relate the records to each other, it sounds like you'll need a custom VBA routine to transpose your file into the format you want.
Just my 2 cents...
Without a "key" to relate the records to each other, it sounds like you'll need a custom VBA routine to transpose your file into the format you want.
Just my 2 cents...
having a common key would be ideal, but the problem is that the txt file is auto generated by a custom piece of software that is no longer supported because the company has gone belly up, so I have to work with what i have. If someone can help me with what syntax i need to write a script, that would be great!
Should I be posting in a different place???
QUOTE (pensived @ Feb 21 2012, 07:41 PM) 
Should I be posting in a different place???
What do you mean?
QUOTE (pensived @ Feb 21 2012, 07:38 PM)
If someone can help me with what syntax i need to write a script, that would be great!
As I was saying, you will probably need a custom function for this. It's not going to be a simple syntax. You'll need to use a recordset and loop through it with the right logic to get the right piece of data and put it in the right place. Are you familiar with VBA at all? How about recordsets?
Just my 2 cents...
Sorry, I meant in a different forum? I posted the same question in Access Automation, and in Access Queries, but one was deleted. I assume there is a rule against double posting. I was asking because perhaps I would have a better chance at getting some help if I posted in the right place.
Thank you
Thank you
I'm fairly familiar with VB, I've used recordset loops to check for past due invoices upon opening an order entry screen, but how would I use that logic to take the information from a query and filter it so that it separates it the way I want? Honestly I think I'm have a 3 day brain fart, because I should have been able to figure this out. I have functions built into this current database that do some pretty crazy things, but this is stumping me and it's driving me mad!
QUOTE (pensived @ Feb 21 2012, 08:09 PM)
Sorry, I meant in a different forum? I posted the same question in Access Automation, and in Access Queries, but one was deleted. I assume there is a rule against double posting. I was asking because perhaps I would have a better chance at getting some help if I posted in the right place.
Thank you
Thank you
Oh, I see what you mean now. Yes, our forum guidelines specifically prohibits double and cross posting. If you feel that you have posted in the wrong forum, you can just ask any moderator to move your question to a different forum. You can use the !Report button to the left for that.
But still, the forum where you post your question doesn't really mean much if you're trying to get some attention. Most contributors here use the Latest Posts or New Posts or Unanswered Topics menu to see what's going on or where help is needed. I for one don't go to any specific forum to check out the questions because I can see all of them from any of the menus I mentioned.
Just my 2 cents...
Oh well that's good to know 
Thank you for the Forum help. I'm glad someone is even looking to be honest. I can usually find an answer or a slicker way to do something just by browsing forums, but this has got me beat.
Thank you for the Forum help. I'm glad someone is even looking to be honest. I can usually find an answer or a slicker way to do something just by browsing forums, but this has got me beat.
Hi,
That's good to hear. Here's some pseudocode that maybe you can convert into code:
1. Create recordset based on the imported data
2. Start a loop to read the first record
3. Store the value into another table's field1
4. Read the next record from the recordset
5. Store the value into field2 of the other table
6. Repeat the loop until you reach the end of the recordset
Just my 2 cents...
QUOTE (pensived @ Feb 21 2012, 08:14 PM)
I'm fairly familiar with VB, I've used recordset loops to check for past due invoices upon opening an order entry screen, but how would I use that logic to take the information from a query and filter it so that it separates it the way I want? Honestly I think I'm have a 3 day brain fart, because I should have been able to figure this out. I have functions built into this current database that do some pretty crazy things, but this is stumping me and it's driving me mad!
That's good to hear. Here's some pseudocode that maybe you can convert into code:
1. Create recordset based on the imported data
2. Start a loop to read the first record
3. Store the value into another table's field1
4. Read the next record from the recordset
5. Store the value into field2 of the other table
6. Repeat the loop until you reach the end of the recordset
Just my 2 cents...
If it were me, ... (there's always more than one way to accomplish something), ... I would not import the data as you are there...
I would just use VBA to import it and parse it all at once.
1) Read the entire file into memory
2) Use the REPLACE() function to replace all of the vbCrLfs with "="
3) Use the SPLIT() function and split the data into an array using "=" as the split character
4) Loop through the array and every 2nd and 4th element is what you write to the record.
I would just use VBA to import it and parse it all at once.
1) Read the entire file into memory
2) Use the REPLACE() function to replace all of the vbCrLfs with "="
3) Use the SPLIT() function and split the data into an array using "=" as the split character
4) Loop through the array and every 2nd and 4th element is what you write to the record.
I would love to do it in one step, but it's a little beyond me.
I just tried this using the recordsets, but again I'm misssing something
Function import()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("ImportQry1")
Set rs2 = db.OpenRecordset("ImportTbl")
rs1.MoveFirst
Do Until rs1.EOF
If rs1![code] = "qt" Then
rs2.AddNew
rs2![prod] = rs1![prod]
rs2.Update
End If
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
End Function
I just tried this using the recordsets, but again I'm misssing something
Function import()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("ImportQry1")
Set rs2 = db.OpenRecordset("ImportTbl")
rs1.MoveFirst
Do Until rs1.EOF
If rs1![code] = "qt" Then
rs2.AddNew
rs2![prod] = rs1![prod]
rs2.Update
End If
Loop
rs.Close
Set rs = Nothing
Set db = Nothing
End Function
>>I would love to do it in one step, but it's a little beyond me.<<
We can work on that, if you're up for learning something new ...
We can work on that, if you're up for learning something new ...
Always open to new learning, can't progress without it!
Hi,
For one thing, I notice that you are not checking for the PN part and grabbing it so you can store the value in rs2.
And for another, you are not moving your record pointer in your loop, so you seem to be just adding the same record in rs2 ad infinitum. To move the record pointer, you can use the command rs1.MoveNext.
Just my 2 cents...
QUOTE (pensived @ Feb 21 2012, 08:50 PM)
I just tried this using the recordsets, but again I'm misssing something
For one thing, I notice that you are not checking for the PN part and grabbing it so you can store the value in rs2.
And for another, you are not moving your record pointer in your loop, so you seem to be just adding the same record in rs2 ad infinitum. To move the record pointer, you can use the command rs1.MoveNext.
Just my 2 cents...
If you want to pursue my solution, here's the first step:
Get the whole file into memory:
Buffer = Space(FileLen(LogFile))
Open LogFile For Binary Access Read As #1
Get #1, , Buffer 'Place entire contents of file in Buffer
Close #1
Get the whole file into memory:
Buffer = Space(FileLen(LogFile))
Open LogFile For Binary Access Read As #1
Get #1, , Buffer 'Place entire contents of file in Buffer
Close #1
I didn't include PN because I was just trying the code to see if it would run without errors. It did not. I added that bit, but obviously in the wrong place. It still errors out.
Hi Niesz,
I know this sounds stupid but I'm pulling 18 hour days..... I'm assuming that first step is a new module? Yes I know I know, no sleep please be kind.
I know this sounds stupid but I'm pulling 18 hour days..... I'm assuming that first step is a new module? Yes I know I know, no sleep please be kind
.
Changed it to MoveNext, now it crashes. I think I might be losing my mind. I'm attaching a sample, if you have 2 minutes please let me know where i'm going wrong.
messed up the attach
>>I'm assuming that first step is a new module?<<
I would just create a new form, add a button, and use the button's OnClick event. Paste that code in the event.
You also need to declare the variables and assign the file location to the Filename.
Let me know when you have this working and we'll move on to the next step.
I would just create a new form, add a button, and use the button's OnClick event. Paste that code in the event.
You also need to declare the variables and assign the file location to the Filename.
Let me know when you have this working and we'll move on to the next step.
Private Sub Command4_Click()
Dim lngUnderscore As Long
Dim strDate As String
Dim strFolder As String
Dim strFile As String
strFolder = "C:\"
strFile = Dir(strFolder & "import.txt")
Buffer = Space(FileLen(LogFile))
Open LogFile For Binary Access Read As #1
Get #1, , Buffer 'Place entire contents of file in Buffer
Close #1
End Sub
Dim lngUnderscore As Long
Dim strDate As String
Dim strFolder As String
Dim strFile As String
strFolder = "C:\"
strFile = Dir(strFolder & "import.txt")
Buffer = Space(FileLen(LogFile))
Open LogFile For Binary Access Read As #1
Get #1, , Buffer 'Place entire contents of file in Buffer
Close #1
End Sub
Hi,
Not sure what you meant by that... I didn't suggest that you "change" anything to MoveNext. I said you need to "add" MoveNext so you can traverse through the recordset. For instance:
rs1.MoveFirst
Do While Not rs1.EOF
'do stuff here
rs1.MoveNext
Loop
Just my 2 cents...
QUOTE (pensived @ Feb 22 2012, 07:16 PM)
Changed it to MoveNext, now it crashes.
Not sure what you meant by that... I didn't suggest that you "change" anything to MoveNext. I said you need to "add" MoveNext so you can traverse through the recordset. For instance:
rs1.MoveFirst
Do While Not rs1.EOF
'do stuff here
rs1.MoveNext
Loop
Just my 2 cents...
Let's modify a few lines so that the right variables are declared:
Private Sub Command4_Click()
Dim strFolder As String
Dim strFile As String
Dim Buffer as String
Dim MyArray()
strFolder = "C:\"
strFile = "import.txt"
Buffer = Space(FileLen(strfolder & strFile))
Open strFile For Binary Access Read As #1
Get #1, , Buffer 'Place entire contents of file in Buffer
Close #1
'now that entire file is in the Buffer variable, we can manipulate it at will...
'replace all Carriage Return / Line Feeds with an equals sign
Buffer = Replace(Buffer, vbCrLf, "=")
'at this point you should have a long string that looks like "PN=DSAI6612=QT=3=PN=CT22L=QT=3...."
'now we can place all the bits into an array
MyArray = Split(Buffer, "=")
'so now MyArray(0) is equal to "PN", MyArray(1) is equal to "DSAI6612", etc
'now we can loop through the array and write all the records to the table
End Sub
Follow so far? ...
Private Sub Command4_Click()
Dim strFolder As String
Dim strFile As String
Dim Buffer as String
Dim MyArray()
strFolder = "C:\"
strFile = "import.txt"
Buffer = Space(FileLen(strfolder & strFile))
Open strFile For Binary Access Read As #1
Get #1, , Buffer 'Place entire contents of file in Buffer
Close #1
'now that entire file is in the Buffer variable, we can manipulate it at will...
'replace all Carriage Return / Line Feeds with an equals sign
Buffer = Replace(Buffer, vbCrLf, "=")
'at this point you should have a long string that looks like "PN=DSAI6612=QT=3=PN=CT22L=QT=3...."
'now we can place all the bits into an array
MyArray = Split(Buffer, "=")
'so now MyArray(0) is equal to "PN", MyArray(1) is equal to "DSAI6612", etc
'now we can loop through the array and write all the records to the table
End Sub
Follow so far? ...
Yes and it's a very slick approach. I tried it just now, but I get a type missmatch error and the debugger highlights MyArray = Split(Buffer, "="). Very cool, what do I do next?
Sorry for the misunderstanding. Indeed it's not crashing now, but it's not moving the filtered data either.
Function Import()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("ImportQry1")
Set rs2 = db.OpenRecordset("ImportTbl")
rs1.MoveNext
Do Until rs1.EOF
If rs1![Code] = "qt" Then
rs2.AddNew
rs2![Prod] = rs1![Prod]
rs2.Update
End If
rs1.MoveNext
Loop
'rs.Close
Set rs = Nothing
Set db = Nothing
End Function
Let me say again how much I appreciate the help i'm receiving on this problem
Function Import()
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("ImportQry1")
Set rs2 = db.OpenRecordset("ImportTbl")
rs1.MoveNext
Do Until rs1.EOF
If rs1![Code] = "qt" Then
rs2.AddNew
rs2![Prod] = rs1![Prod]
rs2.Update
End If
rs1.MoveNext
Loop
'rs.Close
Set rs = Nothing
Set db = Nothing
End Function
Let me say again how much I appreciate the help i'm receiving on this problem
Sorry, ...
Typing from memory ...
Should be:
Dim MyArray() as String
Typing from memory ...
Should be:
Dim MyArray() as String
After you make that fix, the next step is to cycle through the array.
Add this declaration at the top of the function:
Dim i As Long
Then add this code at the end:
'now we can loop through the array and write all the records to the table
For i = 0 To UBound(MyArray) Step 4
'at this point myarray(i+1) = the PN value and myarray(i+3) = the QT value
'to prove it let's print them to the Immediate Window
Debug.Print MyArray(i + 1), MyArray(i + 3)
Next i
Add this declaration at the top of the function:
Dim i As Long
Then add this code at the end:
'now we can loop through the array and write all the records to the table
For i = 0 To UBound(MyArray) Step 4
'at this point myarray(i+1) = the PN value and myarray(i+3) = the QT value
'to prove it let's print them to the Immediate Window
Debug.Print MyArray(i + 1), MyArray(i + 3)
Next i
I'm really liking the method, and understanding the logic. I've done everything I hope as requested, but I get a "Subscript out of range" error and "Debug.Print MyArray(i + 1), MyArray(i + 3)" is highlighted.
Private Sub Command4_Click()
Dim i As Long
Dim strFolder As String
Dim strFile As String
Dim Buffer As String
Dim MyArray() As String
strFolder = "C:\"
strFile = "import.txt"
Buffer = Space(FileLen(strFolder & strFile))
Open strFile For Binary Access Read As #1
Get #1, , Buffer 'Place entire contents of file in Buffer
Close #1
'now that entire file is in the Buffer variable, we can manipulate it at will...
'replace all Carriage Return / Line Feeds with an equals sign
Buffer = Replace(Buffer, vbCrLf, "=")
'at this point you should have a long string that looks like "PN=DSAI6612=QT=3=PN=CT22L=QT=3...."
'now we can place all the bits into an array
MyArray = Split(Buffer, "=")
'so now MyArray(0) is equal to "PN", MyArray(1) is equal to "DSAI6612", etc
'now we can loop through the array and write all the records to the table
'now we can loop through the array and write all the records to the table
For i = 0 To UBound(MyArray) Step 4
'at this point myarray(i+1) = the PN value and myarray(i+3) = the QT value
'to prove it let's print them to the Immediate Window
Debug.Print MyArray(i + 1), MyArray(i + 3)
Next i
End Sub
Private Sub Command4_Click()
Dim i As Long
Dim strFolder As String
Dim strFile As String
Dim Buffer As String
Dim MyArray() As String
strFolder = "C:\"
strFile = "import.txt"
Buffer = Space(FileLen(strFolder & strFile))
Open strFile For Binary Access Read As #1
Get #1, , Buffer 'Place entire contents of file in Buffer
Close #1
'now that entire file is in the Buffer variable, we can manipulate it at will...
'replace all Carriage Return / Line Feeds with an equals sign
Buffer = Replace(Buffer, vbCrLf, "=")
'at this point you should have a long string that looks like "PN=DSAI6612=QT=3=PN=CT22L=QT=3...."
'now we can place all the bits into an array
MyArray = Split(Buffer, "=")
'so now MyArray(0) is equal to "PN", MyArray(1) is equal to "DSAI6612", etc
'now we can loop through the array and write all the records to the table
'now we can loop through the array and write all the records to the table
For i = 0 To UBound(MyArray) Step 4
'at this point myarray(i+1) = the PN value and myarray(i+3) = the QT value
'to prove it let's print them to the Immediate Window
Debug.Print MyArray(i + 1), MyArray(i + 3)
Next i
End Sub
The method assumes a few things:
1) That the file's end-of-line character is a CR/LF pair.
2) That there will always be matched pairs all the way through the file.
Did you see anything print in the Immediate Window?
Are you familiar with debugging code? Can step through the code and determine when it fails? On the first loop? On the last?
It's basically telling you that the number of elements in the array are not equally divisible by 4 (but it should be if the 2 items mentioned above hold true).
1) That the file's end-of-line character is a CR/LF pair.
2) That there will always be matched pairs all the way through the file.
Did you see anything print in the Immediate Window?
Are you familiar with debugging code? Can step through the code and determine when it fails? On the first loop? On the last?
It's basically telling you that the number of elements in the array are not equally divisible by 4 (but it should be if the 2 items mentioned above hold true).
I've cleaned up the source file manually just to be sure that the data is clean and there are matched pairs. In a real setting there are other lines in the file, but they do not have a prefix of PN or QT. This clean data still resulted in the same error. Nothing appeared in the Immediate window.
Is it possible to attach the file? (It must be zipped)
Right after this line of code:
MyArray = Split(Buffer, "=")
... type this:
Debug.print myarray(0)
Then see what shows in the Immediate Window.
Right after this line of code:
MyArray = Split(Buffer, "=")
... type this:
Debug.print myarray(0)
Then see what shows in the Immediate Window.
Still nothing shows, I'm googling the error and the snippet of code to see if there is any common cause. DB and *.txt file attached
Couple of things...
1) I changed the OPEN line so that the filepath is included. You only were looking for the filename, but not the filepath
2) I added an IF statement so that when we reach the end of the file, we stop processing. This was necessary because the file includes a CR/LF after the last line of data.
So try this code:
1) I changed the OPEN line so that the filepath is included. You only were looking for the filename, but not the filepath
2) I added an IF statement so that when we reach the end of the file, we stop processing. This was necessary because the file includes a CR/LF after the last line of data.
So try this code:
CODE
Option Compare Database
Option Explicit
Private Sub Command4_Click()
Dim strFolder As String
Dim strFile As String
Dim Buffer As String
Dim MyArray() As String
Dim i As Long
strFolder = "C:\"
strFile = "import.txt"
Buffer = Space(FileLen(strFolder & strFile))
Open strFolder & strFile For Binary Access Read As #1
Get #1, , Buffer 'Place entire contents of file in Buffer
Close #1
'now that entire file is in the Buffer variable, we can manipulate it at will...
'replace all Carriage Return / Line Feeds with an equals sign
Buffer = Replace(Buffer, vbCrLf, "=")
'at this point you should have a long string that looks like "PN=DSAI6612=QT=3=PN=CT22L=QT=3...."
'now we can place all the bits into an array
MyArray = Split(Buffer, "=")
'so now MyArray(0) is equal to "PN", MyArray(1) is equal to "DSAI6612", etc
'now we can loop through the array and write all the records to the table
For i = 0 To UBound(MyArray) Step 4
'at this point myarray(i+1) = the PN value and myarray(i+3) = the QT value
'to prove it let's print them to the Immediate Window
'check to see if we're on the last line of the file
If i + 3 <= UBound(MyArray) Then
Debug.Print MyArray(i + 1), MyArray(i + 3)
End If
Next i
End Sub
Option Explicit
Private Sub Command4_Click()
Dim strFolder As String
Dim strFile As String
Dim Buffer As String
Dim MyArray() As String
Dim i As Long
strFolder = "C:\"
strFile = "import.txt"
Buffer = Space(FileLen(strFolder & strFile))
Open strFolder & strFile For Binary Access Read As #1
Get #1, , Buffer 'Place entire contents of file in Buffer
Close #1
'now that entire file is in the Buffer variable, we can manipulate it at will...
'replace all Carriage Return / Line Feeds with an equals sign
Buffer = Replace(Buffer, vbCrLf, "=")
'at this point you should have a long string that looks like "PN=DSAI6612=QT=3=PN=CT22L=QT=3...."
'now we can place all the bits into an array
MyArray = Split(Buffer, "=")
'so now MyArray(0) is equal to "PN", MyArray(1) is equal to "DSAI6612", etc
'now we can loop through the array and write all the records to the table
For i = 0 To UBound(MyArray) Step 4
'at this point myarray(i+1) = the PN value and myarray(i+3) = the QT value
'to prove it let's print them to the Immediate Window
'check to see if we're on the last line of the file
If i + 3 <= UBound(MyArray) Then
Debug.Print MyArray(i + 1), MyArray(i + 3)
End If
Next i
End Sub
HEY!!! Wow that works like a charm! Now I just have to pass the values from the array to a table right?
Correct. Are you OK from here on out?
I'm certainly going to try my best. Thank you so much for the help. I hope I can get the rest on my own.
No problem. If you run into any issues, just post back. 
Ok I know I'm very close. I've added the following below For i = 0 To UBound(MyArray) Step 4 and it populates the table, but only with the values that are first in the array, and it populates them as many times as there are lines??
If i + 3 <= UBound(MyArray) Then
'Debug.Print MyArray(i + 1), MyArray(i + 3)
End If
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("importtbl", dbOpenDynaset)
rs.AddNew
rs!Code = MyArray(1)
rs!Prod = MyArray(3)
rs.Update
Next i
End Sub
You are a very good teacher!! I got the export to work in two tries!!
If i + 3 <= UBound(MyArray) Then
'Debug.Print MyArray(i + 1), MyArray(i + 3)
End If
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("importtbl", dbOpenDynaset)
rs.AddNew
rs!Code = MyArray(1)
rs!Prod = MyArray(3)
rs.Update
Next i
End Sub
You are a very good teacher!! I got the export to work in two tries!!
First, I would move the opening & closing of the recordset outside of the FOR...NEXT loop ... otherwise you are opening and closing the RS each time you insert a record.
Then, you need to not hardcode the array element number in the code:
rs!Code = MyArray(i+1)
rs!Prod = MyArray(i+3)
Then, you need to not hardcode the array element number in the code:
rs!Code = MyArray(i+1)
rs!Prod = MyArray(i+3)
That did it!!! You are a genius!!
Thank you so much
Thank you so much
Don't forget to Close and Set to Nothing the RS when you're done with it. It's just good programming practice!
Nice job, Walter!
It's nice when posters are willing to take the time to learn something new. These days everyone always seems to "just want the answer", and I'm just not willing to give it to them that easily. I'd rather they digest it a little at a time ... even if it takes a few days to accomplish.
Give a man a fish and you feed him for a day. Teach a man to fish and you feed him for a lifetime.
Thank you very much. I would rather have a lesson than an answer any day.
Thank you very much. I would rather have a lesson than an answer any day.
Me again,
Presented the database, everything was fine, then a monkey wrench. We have a customer who exports his files and they look like this. Is there a way to modify the code I already have, to filter out only the entries that start with "QT" and 'PN"? Thanking you in advance.
SF=18 stations.SPC
ST=ProjectSymbols AutoCAD 2007 Takeoff 17:55:09 11-15-2010
DT=11-15-2010
TM=17:55:09
PN=BFK8T72
MC=TOS
QT=3
TG=
GC=
T3=
T4=
T5=
PD=
TK=
Presented the database, everything was fine, then a monkey wrench. We have a customer who exports his files and they look like this. Is there a way to modify the code I already have, to filter out only the entries that start with "QT" and 'PN"? Thanking you in advance.
SF=18 stations.SPC
ST=ProjectSymbols AutoCAD 2007 Takeoff 17:55:09 11-15-2010
DT=11-15-2010
TM=17:55:09
PN=BFK8T72
MC=TOS
QT=3
TG=
GC=
T3=
T4=
T5=
PD=
TK=
That changes things a little ...
I would still import the file, split it out into an array, but then step through the array by 2's instead of 4's. When a PN is found in MyArray(0), write MyArray(1) to a variable. When a QT is found in MyArray(0), write the the stored variable (which should be the PN value) and the MyArray(1) value to the recordset. Ignore any other "set" of values that are not PN or QT.
I would still import the file, split it out into an array, but then step through the array by 2's instead of 4's. When a PN is found in MyArray(0), write MyArray(1) to a variable. When a QT is found in MyArray(0), write the the stored variable (which should be the PN value) and the MyArray(1) value to the recordset. Ignore any other "set" of values that are not PN or QT.
Ok I've given it the old college try, meaning I failed miserably then went to a pub . I'm still not getting it. It's currently not looking for PN or QT. When I tried playing with replacing the "=" nothing works. Just a push in the right direction??? Please....
Option Compare Database
Option Explicit
Private Sub Command4_Click()
Dim strFolder As String
Dim strFile As String
Dim Buffer As String
Dim MyArray() As String
Dim i As Long
Const strPath As String = "c:\"
strFolder = "C:\"
'strFile = "import.txt"
strFile = Dir(strPath & "*.txt")
Buffer = Space(FileLen(strFolder & strFile))
Open strFolder & strFile For Binary Access Read As #1
Get #1, , Buffer
Close #1
Buffer = Replace(Buffer, vbCrLf, "=")
MyArray = Split(Buffer, "=")
For i = 0 To UBound(MyArray) Step 4
'If i + 3 <= UBound(MyArray) Then
'Debug.Print MyArray(i + 1), MyArray(i + 3)
'End If
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("importtbl", dbOpenDynaset)
If i + 3 <= UBound(MyArray) Then
rs.AddNew
rs!Code = MyArray(i + 1)
rs!Prod = MyArray(i + 3)
rs.Update
End If
Next i
rs.Close: Set rs = Nothing
End Sub
. I'm still not getting it. It's currently not looking for PN or QT. When I tried playing with replacing the "=" nothing works. Just a push in the right direction??? Please....Option Compare Database
Option Explicit
Private Sub Command4_Click()
Dim strFolder As String
Dim strFile As String
Dim Buffer As String
Dim MyArray() As String
Dim i As Long
Const strPath As String = "c:\"
strFolder = "C:\"
'strFile = "import.txt"
strFile = Dir(strPath & "*.txt")
Buffer = Space(FileLen(strFolder & strFile))
Open strFolder & strFile For Binary Access Read As #1
Get #1, , Buffer
Close #1
Buffer = Replace(Buffer, vbCrLf, "=")
MyArray = Split(Buffer, "=")
For i = 0 To UBound(MyArray) Step 4
'If i + 3 <= UBound(MyArray) Then
'Debug.Print MyArray(i + 1), MyArray(i + 3)
'End If
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("importtbl", dbOpenDynaset)
If i + 3 <= UBound(MyArray) Then
rs.AddNew
rs!Code = MyArray(i + 1)
rs!Prod = MyArray(i + 3)
rs.Update
End If
Next i
rs.Close: Set rs = Nothing
End Sub
I'm at Microsoft MVP Summit in Seattle right now, ... that's why my reply's are so sparse, but try something more like:
CODE
For i = 0 To UBound(MyArray) Step 2
Dim rs As DAO.Recordset
Dim PnValue as String
Set rs = CurrentDb.OpenRecordset("importtbl", dbOpenDynaset)
If i + 1 <= UBound(MyArray) Then 'check to see if array has at least one more element
Select Case MyArray(i)
Case "PN"
'first set of pairs found, write to variable
PnValue = MyArray(i+1)
Case "QT"
'second set of pairs found write both to table
rs.AddNew
rs!Code = PnValue
rs!Prod = MyArray(i + 1)
rs.Update
PnValue = "" 'reset variable
Case Else
'do nothing
End Select
End If
Next i
rs.Close: Set rs = Nothing
Dim rs As DAO.Recordset
Dim PnValue as String
Set rs = CurrentDb.OpenRecordset("importtbl", dbOpenDynaset)
If i + 1 <= UBound(MyArray) Then 'check to see if array has at least one more element
Select Case MyArray(i)
Case "PN"
'first set of pairs found, write to variable
PnValue = MyArray(i+1)
Case "QT"
'second set of pairs found write both to table
rs.AddNew
rs!Code = PnValue
rs!Prod = MyArray(i + 1)
rs.Update
PnValue = "" 'reset variable
Case Else
'do nothing
End Select
End If
Next i
rs.Close: Set rs = Nothing
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.