Using MS Access/MS Access - Parse data
I have this data. Each line contains FileNameLocation, Size, CreationDate, ModifyDate.
T:admCommon Filesdb1.mdb 92.0 KB (94208 bytes) 9/20/2008 9:54:28 PM 3/24/2011 10:41:20 AM
T:admCommon FilesHEICSIncident Commander HandbookIncid Cmdr contacts.mdb 1.50 MB (1572864 bytes) 11/6/2008 10:58:23 AM 2/4/2013 12:01:50 PM
T:admittingRESTOREDAdmittingPrompt Pay DiscountCopy of PROMPT PAY DISCOUNT ADJUSTMENT SHEET new.mdb 132.0 KB (135168 bytes) 1/20/2009 2:03:42 PM 6/3/2009 11:20:31 AM
T:admittingRESTOREDAdmittingQCQC Websites.mdb 96.0 KB (98304 bytes) 1/20/2009 2:03:51 PM 6/3/2009 11:20:54 AM
T:admittingRESTOREDAdmittingQCQC Websites1.mdb 96.0 KB (98304 bytes) 1/20/2009 2:03:51 PM 6/3/2009 11:20:54 AM
T:admittingRESTOREDAdmittingTeam LeadsAdmitting.mdb 17.88 MB (18747392 bytes) 5/20/2009 1:05:17 PM 6/3/2009 11:21:18 AM
T:admittingTeam LeadsAdmitting.mdb 21.20 MB (22224896 bytes) 11/10/2009 3:57:48 PM 3/11/2010 8:54:32 AM
I want to separate the fields out. I got the first field but am stuck on the others.
This data is in an Access table - there is no delimiter the data is all in one field. I cannot use a fixed delimiter because the field lengths are dissimilar
Please help me.
How was this file created? Without a delimiter and a variable space between elements it is going to be almost impossible to parse it out.
My suggestion would be to output this into a text file, then do a replace on the file. Replace 2 spaces together with a pipe (|) character. Then replace all instances of 2 pipe characters with one. This should give you a pipe delimited file, you can import back into Access.
This looks like someone tried to create a catalog of Access files.
I have an application that does just that. If you are interested I can explain how it works.
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA