UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Text File Conversion, Access 2016    
 
   
LilAnnCC1
post Mar 7 2018, 09:00 PM
Post#1



Posts: 736
Joined: 31-May 04
From: Wisconsin, USA


Hello all!

I am working with a text file created from a folder directory. I imported the text file into Excel--but I don't know enough about Excel to do what I want to do. I want to remove everything from the left of the first Letter of the string:

CODE
11/15/2017  08:45 PM       387,282,808 Baggage Claim (2013).mkv


I've manage to get it to this (not exactly what I want):

BaggageClaim

Now how do I insert a space between the lower and upper case? There are 1,302 files and they have anywhere form 1 to 5+ words in the title.

Though, in a perfect world my new field would be: Baggage Claim (2013) ohyeah.gif

Edit:

I've now managed to get it to BaggageClaim (2013), but still stuck on the space between words.

This post has been edited by LilAnnCC1: Mar 7 2018, 09:07 PM

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
orange999
post Mar 7 2018, 09:18 PM
Post#2



Posts: 1,777
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Do you have some sample data you can share?
Seems the location of the text you need to capture varies from record to record.
We will need some representative data to see if there is a pattern or algorithm that may work.

--------------------
Good luck with your project!
Go to the top of the page
 
DanielPineault
post Mar 7 2018, 09:28 PM
Post#3


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



What about something like

CODE
Function ExtractFileName(ByVal sInput As String) As String
     On Error GoTo Error_Handler

      'Remove the Date (static 20 chrs long)
     ExtractFileName = Trim(Right(sInput, Len(sInput) - 20))
     'Remove the numeric value
     ExtractFileName = Mid(ExtractFileName, InStr(ExtractFileName, " ") + 1)

Error_Handler_Exit:
     On Error Resume Next
     Exit Function

Error_Handler:
     MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
            "Error Number: " & Err.Number & vbCrLf & _
            "Error Source: ExtractFileName" & vbCrLf & _
            "Error Description: " & Err.Description & _
            Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
            , vbOKOnly + vbCritical, "An Error has Occured!"
     Resume Error_Handler_Exit
End Function


To Test

CODE
?ExtractFileName("11/15/2017  08:45 PM       387,282,808 Baggage Claim (2013).mkv")


Which returns

CODE
Baggage Claim (2013).mkv


but we'd need more sample of the data to confirm it's reliable for all cases.






If the date segment isn't static at 20 Chrs long then you could simply test for AM or PM and work from there quite easily.



--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
LilAnnCC1
post Mar 7 2018, 10:33 PM
Post#4



Posts: 736
Joined: 31-May 04
From: Wisconsin, USA


Oh Joy! Happy Dance! Happy Dance!

Thank you so much, Daniel Pineault! It works perfectly! I've just spent the last hour fixing my list manually and you solved it in minutes! I ran your function against my original list and it was perfect!

Thank you again!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
DanielPineault
post Mar 8 2018, 11:53 AM
Post#5


UtterAccess VIP
Posts: 5,951
Joined: 30-June 11



My pleasure. thumbup.gif

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st June 2018 - 02:44 PM