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.  

FileLocationName: Left([Field1],InStr(1,[Field1],"mdb")+2)

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.  

Thank you,

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

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.

[an error occurred while processing this directive]