Import complex text file into Access
- Thread starter Sergo
- Start date
Maybe better than us, you never know
It's absolutely necessary to comment code especially when you're done with it.
I believe the file attached (Access 2000) should do it. I didn't have time for any fancy forms so just open the module and run the function called TransposeFile from the Immediate Window:
All the relevant comments are there.
One thing to note is that it transposes the text file to an Excel sheet and the reason behind that is because it allows users to scrutinise the file and make corrections (if necessary) before exporting to db.
So if you want to export directly to the db you can implement that. Or if you want to export the transformed spreadsheet, you can also implement that. All the necessary functions are there for you to use and make slight adjustments.
It's now in your hands and yours to manage.
I believe the file attached (Access 2000) should do it. I didn't have time for any fancy forms so just open the module and run the function called TransposeFile from the Immediate Window:
Code:
?TransposeFile("[COLOR="Blue"]Path to file[/COLOR]\Sample.txt")One thing to note is that it transposes the text file to an Excel sheet and the reason behind that is because it allows users to scrutinise the file and make corrections (if necessary) before exporting to db.
So if you want to export directly to the db you can implement that. Or if you want to export the transformed spreadsheet, you can also implement that. All the necessary functions are there for you to use and make slight adjustments.
It's now in your hands and yours to manage.
- Local time
- Today, 03:55
- Joined
- Jul 9, 2003
- Messages
- 17,622
I found the problem, the new sample text has an extra "Language" in it:-
So it looked wrong, however I worked through it and it makes sense.
Now it needs code to concatenate the lines as identified by "LineStart" and "LineEnd" and then add them to the correct field in the table.
I still need to figure out the end, as the "LineStart" and "LineEnd" values are wrong for the last Author:-
Code:
ID SectionName LineCurrCitation fCounter LineStart LineEnd
4677 Abstract 1 ---------------- 7 ---------------- 11 ---------------- 10
4678 Language 1 ---------------- 7 ---------------- 12 ---------------- 13
4679 Language 18 ---------------- 1 ---------------- 15 ---------------- 17
4680 Citation 18 ---------------- 2 ---------------- 19 ---------------- 18So it looked wrong, however I worked through it and it makes sense.
Now it needs code to concatenate the lines as identified by "LineStart" and "LineEnd" and then add them to the correct field in the table.
I still need to figure out the end, as the "LineStart" and "LineEnd" values are wrong for the last Author:-
Code:
ID SectionName LineCurrCitation fCounter LineStart LineEnd
3224 Abstract ------------- 906 --------------- 3 --------------- 916 --------------- 915
3225 Author --------------- 906 --------------- 7 --------------- 917 --------------- 918
3226 END ------------------- 0 ---------------- 0 --------------- 920 --------------- 920
Last edited:
Where's the extra Language field coming from Tony?
- Local time
- Today, 03:55
- Joined
- Jul 9, 2003
- Messages
- 17,622
Here:- (line 11) Original studies in any language were searched from MEDLINE database,
- Local time
- Today, 03:55
- Joined
- Jul 9, 2003
- Messages
- 17,622
It is for my code 'cause it looks for it in the whole line...
Might need a slight modification!
WOW, works like a champ! I thought we go to plan B, but this way is way better. One think. I know I'm asking to much. Can you change it, so it's saves in database. I tried to change it, I'm getting lost. This way I'll make this table as temperately, so the user can make corrections to data and then click submit button. I'm trying to reduce steps for the user. I'll do all this, all I need is Transpose data in the same database. But rest of it is amazing, it will take some time to understand it! Thank you very much!
Like I said, it's in your hands now so "take some time to understand it" and add the extra layer of functionality. You did say "I can read the code and follow it, and learn a lot from it" and "Once I get code working, rest of it I can do it myself." so get coding.
