• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1997
  • Last Modified:

Periods in filenames are being replaced with blanks and sometimes # signs.

Multiple periods in filenames getting changed to pound signs or spaces.  I'm creating flat text files using the transfertext command in access VBA. The export works fine but the filename, which contains multiple periods is having all periods except the one just prior to the extension changed to a pound sign. I can live with that but I'd prefer to have more control over the filenames.
Does anyone know what is changing the filename? There are other cases where the periods are disappearing from the filename as well outside of my application and it's wreaking havoc on other processes. I'd like to make a very strong case for getting rid of the extra periods and would like your help.
0
steven-v-hanson
Asked:
steven-v-hanson
  • 7
  • 6
  • 3
  • +2
3 Solutions
 
jjafferrCommented:
can you please give an example of what is happening.
0
 
steven-v-hansonAuthor Commented:
A.B.C.EEEEEEE.F.txt is changing to A#B#C#EEEEEEE.#.txt

Understand that I don't like to see periods in filenames. Apparently, neither does VBA. I need to make an ironclad case for changing the filenames to A_B_C_EEEEEEE_F.txt

Apparently "it doesn't work" isn't good enough.

:) thanks
0
 
steven-v-hansonAuthor Commented:
oops. A.B.C.EEEEEEE.F.txt is changing to A#B#C#EEEEEEE#F.txt

0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
jjafferrCommented:
I don't really know what is happening, this is a strange behavior,
however, we can always write an update query,
we change the # to _ except for the last period before the file extension (if you want to change the spaces to _ too, that can be done too).

if you want to go that direction, then we'll make a Function in a module, call this Function from an Update query,
then user the Replace command to do the changes, for example:
YoueField=replace(YourField,"#","_")

jaffer
0
 
Bradley HaynesCommented:
Here is a link to (VB) naming conventions, which may be a good reference for you and others viewing this.
http://en.wikipedia.org/wiki/Leszynski_naming_convention

Having dots in file names is NEVER good. It is like reserved words in programming languages.
0
 
steven-v-hansonAuthor Commented:
"Having dots in file names is NEVER good. It is like reserved words in programming languages"

I agree. And I'm sure you are an expert too, but I followed the link you gave and it talked about using either the hyphen ("-") or underscore ("_") as word separators in filenames but did not tell me why I can't use a period. I have already suggested using hyphens to management instead of periods. They want to know why they can't use periods. (I'm sure they're going to change to underscores but in case they hesitate... I want to be there with the proof why periods are bad)

0
 
Bradley HaynesCommented:
When the a period is seen in a file name by the computer it is expecting to see the filetype extension right after. As you know the extension is associated with the application that is called to open said file.

The short answer for 'Management' is that periods in file name create incompatible files, which will not open and therefor are of no use to anyone.  :-|

If this truly is an issue, from the outside looking in; you are being SUBJECTED to "micro-management" . . . hats off and my sympathies to you.
0
 
jjafferrCommented:
Let's look at the issue again.

I tried the following lines, and they both worked fine:

    DoCmd.TransferText acExportDelim, , "tbl_Names", "c:\Table.txt", False
    DoCmd.OutputTo acOutputTable, "tbl_Names", acFormatTXT, "C:\Table2.txt"

can you please:
post your code for the output,
If this didn't work, then
upload your mdb, so that we can have a look at.

jaffer
0
 
steven-v-hansonAuthor Commented:
jjafferr: the filename has many periods in it. Please look at the previous posts. Also, the names are in a variable and not hard-coded in the statement. Notice in the previous post: A.B.C.EEEEEEE.F.txt is changing to A#B#C#EEEEEEE.#.txt
it shows that the dot in front of the txt is good. It doesn't get changed. It's just the dots in the rest of the filename.
0
 
jjafferrCommented:
did you try exporting using both transfertext and outputto, with the same results?
0
 
steven-v-hansonAuthor Commented:
No. Just the transfertext. But I'm not the only one having problems with the periods in the filename. We have excel spreadsheets with multiple periods in the names. When a user modifies a spreadsheet and performs the save operation, sometimes, NOT ALWAYS, the save takes place but the file was not updated. What we find is the changes are part of another file which does NOT have the periods in its name.
If excel and access are going to modify the filename, it would be really nice if we'd get some sort of warning about it. So, I suppose we add this to the long list of undocumented FEATURES in Microsoft Office.
0
 
jjafferrCommented:
This is what I would do to solve this issue, not from Access, but from the txt file:

 DoCmd.TransferText acExportDelim, , "tbl_Names", "c:\Table.txt", False
 
FileName="c:\Table.txt"
 'read the saved file
 open FileName for input as #1
 Temp = Input(LOF(1), #1)       'read the whole file in the variable Temp
 Temp=Replace(Temp,"#","_") 'replace all the # to _
 close #1
 
 'delete the old file
 kill FileName

 'now save this file with the corrections, with the save file name
 open FileName for output as #1
 print #1, Temp  'write the data to the file
 close #1


jaffer
0
 
steven-v-hansonAuthor Commented:
jjafferr, you seem to be missing the point. I appreciate your efforts but this problem does not have a programatic solution. You seem to be in your own little world which, incidentally, doesn't admit the thoughts of others. Take the time to go back and actually read what is being posted.
Practice listening. I don't want code here. I want to know why periods disappear in file names. The code is not failing. Something internal to Microsoft, something behind the scenes, something that takes place during a 'SAVE' operation in excel and something that takes place with the filename in a transfertext in Access is changing the periods to spaces or pound signs or underscores. I want to know why, I don't want to use a different set of instructions (because they might start screwing up a week from Halloween). I want to know why it's happening in the first place. If you start your next response with the word, 'Blue' I will split the points with you (just for reading this).
0
 
jjafferrCommented:
Steven

I don't want the points, I have plenty, thank you, my profile says that.

a question for you:
How on earth does Access know that the "String" you have saved in a field is a file name???
Thus the issue is somewhere else.

when we have problems with Access (or any other programs), and we cannot seem to be able to solve them, we look for work arounds, and that's what I have done.

jaffer
0
 
Jeffrey CoachmanMIS LiasonCommented:
:steven-v-hanson,

Lets go back to jjafferr's post:
<did you try exporting using both transfertext and outputto, with the same results?>
Your reply:
<No. Just the transfertext. >

I just tried this:
DoCmd.OutputTo acOutputReport, "Report1", acFormatTXT, "C:\A.BC.E.F..H...I.txt"
(Tons-O- Periods!)
:)
... and it worked fine!

HTH

Jeff Coachman
0
 
steven-v-hansonAuthor Commented:
Thanks, but i wasn't trying to find a workaround. I'm trying to identify a specific technological reason why using multiple periods in the filename is bad. The fact that the periods in the filename disappear when saving the file from excel or word or other programs.... intermittently is the real problem here.
I need to justify telling the project leader that we need to get rid of filenames with extra periods. We've explained that the periods disappear intermittently but it doesn't seem to get through. I need a writeup by microsoft that says, 'Don't use periods in filenames because ... blah blah'. My word isn't good enough.
Thanks though for taking the time to try.
0
 
Jeffrey CoachmanMIS LiasonCommented:
steven-v-hanson,

Fair enough,
One thing you can say is that with respect to the "TransferText acExportDelim" you can't use periods, regardless of the reason, because they simply won't work.
Why won't they work?

(Bear with me here!)
:)
Here is what I've heard from some veteran programmers:
Some of the old programs that read-in text files, only recognized a file by parsing the filename and taking the last 3 characters of the name, (after the "period"), to determine what type of file it was. These old programs would not accept anything else, because that was the only way it was done back then.
It has been said that MS limits the file name to this spec to maintain compatibility with these older programs.

Could you find an old MS white paper, or internal memo explaining why?
Probably, &if you searched long enough.
But suppose you couldnt?!
What would your supervisor do then?

Your best bet is to call up Microsoft Tech support, and ask them.
It will cost you big money, but at least you will have an answer.
($59.00USD, according to this link,):
http://www.lockergnome.com/nexus/marcerickson/2007/02/05/microsoft-raises-support-fees-for-windows-office/

Good luck!
:O

Again, good luck

Jeff Coachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
This is a link that might help.

Just be aware that MS might not even give you a good explanation.
But at least it will be an official "Thats the way it is"!
:)

http://support.microsoft.com/oas/default.aspx?ln=en-us&x=7&y=18&prid=6680&gprid=36071

JeffCoachman
0
 
andreabyerCommented:
I have the exact same problem!  This is how I got around it....
Dim RptName1 As String
Dim RptName2 As String
.
.
RptName1 = "kardex" & Dte & Tme & ".csv"
RptName2 = "kardex." & "." & Dte & "." & Tme & ".csv"
.
.
DoCmd.TransferText acExportDelim, "FTPExport", "qry_FTPFile", FTPPath & RptName1, False
Name FTPPath & RptName1 As FTPPath & RptName2

Basically renaming the file on the server.  The file with multiple periods opens fine.  This is the wrong way to go about things, but the specifications for the machine reading the file required a FileName.Date.Time.csv format.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 7
  • 6
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now