How do I Append a table with non-delimited txt file data

[Gotboost858]

Views: 3 Size: 10.9 KB">QPLAFiles.zipQQPLA_20150421..txt.zipHi everyone,

I am very new to access 2013 but I figured I would ask this question here.
I am trying to automate the population of an access 2013 database with non-delimited txt file data. The txt files have more fields that I require due to the special characters in the files and I need to correct the format of the import to match the existing tables I have created. When importing the file originally I use the delimited by spaces option and got the files to cooperate but I had some issues with format of the datetime. The Columns I created in my database table are qpla_DateTime, qpla_IP, qpla_UserName, qpla_ErrorNumber, qpla_ErrorMessage and qpla_FileException.


How do I automate the appending of this table with this txt file data???

[June7]

If the import wizard or TransferText method will not serve then need VBA to open and manipulate text file object and read 1 line at a time, parse the string to an array, save array elements to table fields, loop and repeat to end of text file.

Common topic. Search on: Access VBA read write text file

Here is one: http://forums.aspfree.com/microsoft-...vba-30159.html

[Gotboost858]

Outstanding. This is exactly what I was looking for. Thanks June7!

[Gotboost858]

PPIP042115..txt.zipIs there a way to rearrange this file in a way that will allow me to automate the appending into a table.

[June7]

Not without a lot of manual editing.

Did you post a file with real data? Anything confidential in there?

[Gotboost858]

ITs all test data. nothing in there is confidential

[Gotboost858]

This is all originally from a log file generated from a web application that generates reports. This data is pushed out in this format. I would not be able to do manual editing since I am working on the development side of the program. Production data isn't being used to test this access database but the databases are massive. I might need to have them create a better formatted report that lists these in a better way for importing and appending to my access database.

[rpeare]

that's a pretty easy file to parse, you just have to know what elements you want to extract from it.

These are the fields you said you wanted:
qpla_DateTime
qpla_IP
qpla_UserName
qpla_ErrorNumber
qpla_ErrorMessage
qpla_FileException

Which portions of the unit record below are you trying to dump into these fields?

this looks like your unit record

Request Date: 21/04/15 Time: 12:00:17
Get http://10.254.1.61/MNETtoQM/QMReturn.aspx
Content-Type: application/x-www-form-urlencoded
Result_ID=1446313269&Percentage_Score=100&Questions_Attempted=100&Member_Group=&Assessment_ID=8848965138035809&CONTENTITEMID=13acb7f7%2Dd08c%2De211%2D8eda%2D005056a70006&Questions_Answered=100&ID=1667849c%2D1680%2D4e49%2D88c9%2Df73b7eeb4027&ENTRYID=a68dd3e0%2D70e2%2D4fb1%2D9c28%2De089317b4446&command=results&COURSEID=cb6bfc08%2D5083%2D4978%2Db170%2D1b5da793f00d&Participant=JOSHUA%2EB%2EKNIGHT1

Response Date: 21/04/15 Time: 12:00:17
http://10.254.1.61/MNETtoQM/QMReturn.aspx

Results accepted by LMS.  Updating with the following data:
User Name = JOSHUA.B.KNIGHT1
Content Item ID = 13acb7f7-d08c-e211-8eda-005056a70006
Transcript ID = a68dd3e0-70e2-4fb1-9c28-e089317b4446
Grade = 100


Request Date: 21/04/15 Time: 12:03:41
Get http://10.254.1.61/MNETtoQM/QMReturn.aspx
Content-Type: application/x-www-form-urlencoded
Result_ID=328603420&Percentage_Score=50&Questions_Attempted=10&Member_Group=&Assessment_ID=551852768010481&CONTENTITEMID=439be61d%2D8536%2D47da%2Da6cb%2Df1755c11453c&Questions_Answered=5&ID=55f56f45%2Dbcd9%2D4136%2Daf03%2D58ff81463ac0&ENTRYID=e523265e%2D700e%2D4031%2Dbd00%2D10f84c281ba5&command=results&COURSEID=68b1c06a%2D6871%2D45cf%2Db341%2D28e0554e8051&Participant=GARRY%2EM%2EYARBOROGH

Response Date: 21/04/15 Time: 12:03:41
http://10.254.1.61/MNETtoQM/QMReturn.aspx

Results accepted by LMS.  Updating with the following data:
User Name = GARRY.M.YARBOROGH
Content Item ID = 439be61d-8536-47da-a6cb-f1755c11453c
Transcript ID = e523265e-700e-4031-bd00-10f84c281ba5
Grade = 50


Start Date: 21/04/15 Time: 12:04:29
10.254.1.61
D:\perception5\Repositories\Shared\NGQMRepository\pip\MarineNet.pip
/perception5/session.php?CALL=MarineNet.pip&ID=c4e05980-2502-47a0-ba4d-4bb6dcdb0791

Request Date: 21/04/15 Time: 12:04:30
Get http://10.254.1.61/MNETtoQM/QMReturn.aspx
Content-Type: application/x-www-form-urlencoded
ID=c4e05980%2D2502%2D47a0%2Dba4d%2D4bb6dcdb0791&CALL=MarineNet%2Epip&COMMAND=callback

Response Date: 21/04/15 Time: 12:04:30
http://10.254.1.61/MNETtoQM/QMReturn.aspx


SESSION=0551852768010481&
NAME=GARRY.M.YARBOROGH&
COURSEID=68b1c06a-6871-45cf-b341-28e0554e8051&
CONTENTITEMID=439be61d-8536-47da-a6cb-f1755c11453c&
ENTRYID=e523265e-700e-4031-bd00-10f84c281ba5&
ID=c4e05980-2502-47a0-ba4d-4bb6dcdb0791&
S1=&
S2=E-4&
S3=MARINE+CORPS&
S4=Active&
S5=M0CLSPX10A&
S6=Not+Proctored&
S7=130041R6&
EXPIRES=22:04:2015:00:04