Using MS Access/MS Access - Parse data

Advertisement


Question
I have this data.  Each line contains FileNameLocation, Size, CreationDate, ModifyDate.  
Field1
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,
Scott

Answer
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,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

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

Experience

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

Organizations
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

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.