?
Solved

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

Posted on 2007-10-05
19
Medium Priority
?
1,871 Views
Last Modified: 2013-11-27
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
Comment
Question by:steven-v-hanson
  • 7
  • 6
  • 3
  • +2
19 Comments
 
LVL 27

Expert Comment

by:jjafferr
ID: 20023736
can you please give an example of what is happening.
0
 
LVL 1

Author Comment

by:steven-v-hanson
ID: 20023791
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
 
LVL 1

Author Comment

by:steven-v-hanson
ID: 20023824
oops. A.B.C.EEEEEEE.F.txt is changing to A#B#C#EEEEEEE#F.txt

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 27

Expert Comment

by:jjafferr
ID: 20023839
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
 
LVL 8

Expert Comment

by:Bradley Haynes
ID: 20024092
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
 
LVL 1

Author Comment

by:steven-v-hanson
ID: 20024178
"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
 
LVL 8

Assisted Solution

by:Bradley Haynes
Bradley Haynes earned 150 total points
ID: 20024643
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 20024898
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
 
LVL 1

Author Comment

by:steven-v-hanson
ID: 20025629
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 20026420
did you try exporting using both transfertext and outputto, with the same results?
0
 
LVL 1

Author Comment

by:steven-v-hanson
ID: 20026447
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
 
LVL 27

Expert Comment

by:jjafferr
ID: 20027083
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
 
LVL 1

Author Comment

by:steven-v-hanson
ID: 20027246
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
 
LVL 27

Assisted Solution

by:jjafferr
jjafferr earned 150 total points
ID: 20027257
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20239188
: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
 
LVL 1

Author Comment

by:steven-v-hanson
ID: 20240454
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 450 total points
ID: 20244694
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 20261237
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
 

Expert Comment

by:andreabyer
ID: 37899882
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question