The commas will align the data in the approprioate fields for the import.
Printable View
The commas will align the data in the approprioate fields for the import.
Andy, how about
datastring = ",,,,,Data added " * Date()
that will tell anyone looking at the data when each append was made.
I am not sure what this means
"The commas will align the data in the approprioate fields for the import."
Do you mean importing in to Access or Excel?
Sorry for the confusion. Yeah after all the moving and copying files, it's time to import the master file to Access. This is where we both were saying Access should have been used to let Access handle the importing/appending for each file type.
By addidng the 3 commas, it separated the first field of the appended data to the correct field of the table and set all subsequent fields correctly.
So, can you explain the sequence of events?
Copy file or append to Master
Import master to Access
Copy fille or append to Master
Import master to Access again?
Evidently all of the copy file and append file will have to happen prior to importing to Access. After the master is imported to Access, no more appending will occur. So I guess if something is missed to bad so sad. You'll have to start another DB for any other data. Not my show.
So the header rows of the files are used as field names for the table when they are imported to access. When you append a file, it appends everything obviously including the header rows. Creating a file that header row of the intial master file a. The file is structured as header rows, all the data, then after an append, header rows, and all the data. When I import the appended master file, Access will import to the last data set before the header rows are repeated. I've got to omit the header rows when appending to the master file. Didn't think that would happen because when I was importing a file at a time that did not occur.
You could allow the Header rows to be inported and then use a Delete query to remove them.
I think you can specify whether or not a file has header rows or not.
But I also have a VBA import routine that could probably be modified to ignore them during import.
Are you using VBA, docmdimport or a Data Import to bring in the data to Access?
If that was confusing, i'm trying to say omit the header rows of an append file to the master. The master already has the header rows.
OK, of course that is possible, the code that I provided on your previous post skips the first line of code to avoid the headings.
Have you forgotten it already? :)
ps going out for a while so won't be able to answer until I get back.
We've had a few post going back and forth so cut me some slack. jk
I'll go back and look. Is it just to remove the first line? Because they are all different. Some 2 lines, some 3, and some not the complete line and the data set begins.
You're referring to the import and not omitting for an append, correct?
It was the more than 265 fields question, that was you wasn't it?
It was this line of code
If reccount > 1 Then ' process if not heading record
where we were using reccount, in this one we aren't currently using a counter be we could.
Or as I said you can do it in the import routine or even after the data has been imported by deleting any offending records.
Hey what do mean cut you some slack, I am the one who is 70 with fading brain cells. :devil2:
:sick:
My brain hurts
I was joking around anyways. 70, I would have never know.
Just so I'm putting the problem statement out there. Master file is in the directory and it has it's header rows for the file. A second file is appended to master file and it also brings the header rows with it. When trying to import using the routine you are referring to, it imports and stops where the appended file header row begins. So the newly appended
master file looks like this: Header rows - initial master file data - Header rows (from appending file) - data (from appending file).
I can see how using what was earlier used to overlook the headers but not sure how to go about overlooking both sets of headers rows as they will be downstream in the file and not the first rows of the file. And the second set of headers will be located differently for each of the five file types. Thats why I was hoping to omit the header rows from the append file in during that routine.
I know this is all crazy.
Sam, I am currently working on three separate databases, it keeps the old brain cells ticking over, I also post on Tech Guys,where the other 2 are, which used to be a sister forum to VBAX, I have 19000 posts over there.
The routine that I wrote for your previous post ommits the header rows during import and the same method can easily be used to ommit the header during the Append operation.
The only problem is if it is over more than one input "line", unless you know it in advance if it then you can ommit as many rows as necessary.
Andy, this should do it
Dim sDest As String, firstfile As String, filecopied As Integer, reccount As Integer
and
This line could beCode:Appendsub:
MsgBox "current file appending to - " & FileFound & " from - " & FileName
reccount = 0
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
Do Until EOF(1)
Line Input #1, datastring
If reccount > 1 Then
Print #2, datastring
End If
reccount = reccount + 1
Loop
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return
If reccount > 1 Then
If reccount >2 Then
or
If reccount > 3 Then
This is an interesting, if easy, problem and I would like to work on it, but I am not going to read the previous 97 posts to figure out the data structure of Andy's situation.
I don't even know if my speculation in that regard, in post #60, was accurate.
Don't let your chest swell too much, I am 67 and this is my hobby too, also to keep my b.b.b.brain active when it is too hot outside to build stuff. If you go back far enough, you can find my name on a few other forums, even had my own, but it was getting too much like work, so I killed mine and dropped out of the rest and only work on the extremely easy and the most interesting projects here.
So do the other lines need to be added? I added what OBP posted and the data append actually stopped working. It ran the procedure but didn't append any data.
SamT your insight and comments are appreciated as well.
Andy that is very odd, because I have tested it and it works for me, it just doesn't append the first line.
Sam, are you also self taught from the 80s?
yes and no, but from the 70's. Military.
I didn't pick up VBA until ca2004 when I rewrote the Excel Object Model and help files as a learning experience. I am still debating on upgrading to Win 7 and Excel 2007. It's getting boring. I am playing with CLISP at the moment, might set up an LFS Machine for that.
Or... I might grow Pot and go back to Natural Philosophy and finish my treatise on Quantum Gravity. But that would mean refreshing my math skills. Bleh.
This is odd then. I have checked everything I know to check. The routine runs start to finish and the Msgbox gives the indication data was transferred. But there is no data being transferred. If I remove the reccount variable (back to what it was previously), it will append the data. I'm not sure what gives if OBP has it running. And I did declare reccount as an integer.
Code:Appendsub: MsgBox "current file appending to - " & FileFound & " from - " & FileName
reccount = 0
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
datastring = ",,,"
Do Until EOF(1)
Line Input #1, datastring
If reccount > 1 Then
Print #2, datastring
End If
reccount = reccount + 1
Loop
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return
Any ideas? Also, before appending if I want to add a choice for the user to append or save in different location. Can I use the vb yes no msgbox and the msofiledialogsaveas? I've read where save as doesn't actually save the file.
Been busy today cleaning my son's house as he is on holiday and I am off out to Poker in a minute.
How many lines of code were being tranferred before you added reccount in to the subroutine?
Whe I get home I will post my version of the database.
Individual lines in the file? Vary from 50 to a couple hundred lines. If that's not what you're asking sorry, just let me know. I do need to add the option save the file to a different location if the user does not want to append the new file to the master. I've got to use file dialog and probably a yes no msgbox. Trying to add that in while we figure out the reason the append stopped after adding reccount.
Good luck playing cards.
IIRC, you said that some of the files have more than 1 header line. How can you tell by looking at the file which are header lines?
I guess my interpration of a header line is what is being used as a field name of database table when the file is imported to Access. Correct me if that's off base. I say the files consist of more than 1 header line because if you open the file there are 2 and 3 header lines and the actual data starts after the last header name. That can be mid line of the third line of the file. My definitions could be off and we are talking about different things. But essentially what I am trying to do is when a file is appending to the master file omit the header lines because those header lines are already in the master file. They shouldn't be duplicated.
I said all that and didn't answer your question. I know the file structure for the files that being worked with. The header lines will remain the same each time a file is exported.
Here it is.
Use the top button for the code.
Hmmmmm. What if you open the file in Notepad and turn off Word Wrap?Quote:
if you open the file there are 2 and 3 header lines and the actual data starts after the last header name
I'll buy you a cup of coffee the next time we meet if you see more than one header line then.
BTW, your definition is correct.
With word wrap off, the middle of the third line is where the last header name is and the data starts. This particular file has over 200 field names. But it is not one continuous line even with word wrap off. With word wrap on its many more lines. I'm going to put the files up here. Because even with the db OBP posted the append routine does nothing when I added reccount. It doesn't move any of the data to the master file. That makes no sense.
Andy we know from your other code where we split the file into 2 tables that the files use the vblf as it's record seperator for the header, the same as rest of the data, so as the VBA code also uses the vblf to separate the lines there should be no need to skip more than 1 line input.
I have retested my code this morning and it is still working great, doing exactly as expected, I just don't see how skipping the line input for 1 line affects all the rest.
Have you tried adding a msgbox to display what it says it is appending?
Code:Appendsub:
MsgBox "current file appending to - " & FileFound & " from - " & FileName
reccount = 0
Open firstfile For Input As #1
Open sDest & FileFound For Append As #2
Do Until EOF(1)
Line Input #1, datastring
If reccount > 1 Then
Print #2, datastring
msgbox datastring
End If
reccount = reccount + 1
Loop
Close #1
Close #2
MsgBox "data transferred to " & FileFound
Return
Update, using your original data file which was for importing 265+ fields I have reproduced your problem, ie it does NOT update when both files have Headers.
I will now try and establish how to overcome the problem.
Sometimes we can be as dumb as hell.
We established in your first post that because of the vblf that your files use the inputline imports the WHOLE file in one record, how could we have forgotten?
So it works for my files because they DO input 1 line at a time and not for your files because they don't, that was why we went to importing the Characters one a time and decide where the Record delimiters were.
So it look like we will have to do the same here.
Although I don't really understand what would be detrimental about having more header records as they can easily be deleted in other programs.
So we need to do Input (1, #1) or something like that.
Yes I will get on it now that I have had Lunch.
I won't bother with other files until I have this working.
The save to another folder could be achieved using the same copy code as now and then "kill" the original file.
This is the sort of code that you need for the decision process, obviously with different wording and actions
Code:Dim response
response = MsgBox("Are You Sure You Want to Quit Access", vbYesNo + vbExclamation + vbDefaultButton2)
If response = vbNo Then Exit Sub ' User chose No.
DoCmd.Quit
Ok good deal. Thanks for help
That means that even the Records are probably spread across multiple lines.Quote:
Originally Posted by andycl
I think that the files must be read by field count, rather than by lines.
Something likeCode:'first, Replace linefeeds with separator
TmpArray = Split(FileText, Separator)
For j = 0 to UBound(TmpArray) Step FieldCount
If TmpArray(j) = FirstHeaderName then GoTo J_Next 'Skips Headers
Write(TmpArray(j to j+FieldCount) to TmpFile
Write Linefeed Character toTmpFile
J_Next:
Next
Append TmpFile to Master