Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi, all! I'm having a little issue when executing an .csv export which remove the "#" on a field name ( file #) and replaces it with a "." (file .) How can I prevent this from happening? Currently after the export, i'm going manually and replace the "." with the "#". Any help is extremely appreciated!!!..:-)

    DoCmd.TransferText acExportDelim, , "MasterfileAPEX", "S:\APEX\Exports\FILEEXP001" & ".csv", True

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    What happens if you do the export manually.
    Does it still lose the # ?
    Andrew

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    This one has me stumped

    I tried using the Code page option:

    Code:
    DoCmd.TransferText acExportDelim, , "Contracts", "G:\BEKDocs\File0001.csv", True, , 65001
    And tried several different code pages found via the linked Wikipedia entry.

    No Love

    Good luck with this one. Your only option may be to change the field name to include "No" vs "#".
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I tested this and I could repeat the problem with a manual export.

    I think this supports some standard field name conventions:
    • No spaces
    • No special characters
    If I remove the space before the # it exports OK, but with a space before the # it exports as .
    Regards
    John



  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Andrew,

    This gets curiouser & curiouser

    In the export wizard {2003 XP-3} it retains the # sign {see below} but in the file it's gone!

    Code:
    "ContractNo","Agency","Location","MyOwn."
    "AB123","DOD","Arlington","AB123"
    "AB124","USIA","Fairfax","AB124"
    "AB127","DOD","DC","AB127"
    "AC057","Army","Fort B.","AC057"
    "AF004","Air Force","Nellis","AF004"
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    As John H. said, this is why you use standard naming conventions.

    Excel doesn't like to see a # sign. I've had situation like this: "SELECT FileNo AS [File#] FROM..." and it didn't like it either.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Ah! The 64K question is why Microsoft didn't include this information in it's naming conventions? It specifically excludes other special characters, so why not the #?

    I agree that standard naming conventions are the way to go in all things programming!
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Wow! guys I really appreciate all the help in your post. I have tried manually and all; the file still comes missing the # sign as some of you noted. All this trouble for me is because this export file is needed for some other (Third party) web base application who requires the field name in just naming convention; if not it will pop a ton of error messages in the import.

    Is there any VBA code that after a file is exported it will go and open the export file and replaces the "file ." with "file #"?

  9. #9
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    Quote Originally Posted by RetiredGeek View Post
    Ah! The 64K question is why Microsoft didn't include this information in it's naming conventions? It specifically excludes other special characters, so why not the #?

    I agree that standard naming conventions are the way to go in all things programming!
    Because, I don't think they are Microsoft's conventions. They are generally SQL naming conventions. That Microsoft "allows" it in Access is just Microsoft's way of making things easier for the casual Access user (vs. the professional Developer).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Lastcall View Post
    Is there any VBA code that after a file is exported it will go and open the export file and replaces the "file ." with "file #"?
    Most Likely, opening the file up as a text stream object and doing a replace for File with File# on row 1.
    Assuming that it is only row 1 that has the File name header in it.

    Could also probably be done with Excel but this raises issues of number and text auto conversion,
    so I'd probably stick to manipulation of the text file.

    Will post a bit of code later (if no one else does it first).
    Andrew

  11. #11
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Here's an example in principle

    You need to set a reference in

    Tools References

    to the Microsoft Scripting Runtime to use a FileSystemObject & Textstream object

    Code:
    Sub ConvertTextFile()
    
    Dim strPath As String, strFileIn As String, strFileOut As String
    Dim strLine As String, lngLine As Long
    Dim ofs As FileSystemObject, otsIn As TextStream, otsOut As TextStream
    
    strPath = "T:\Downloads\"
    strFileIn = "FileCSV.csv"
    strFileOut = "FileCSVOut.csv"
    
    Set ofs = New FileSystemObject
    Set otsIn = ofs.OpenTextFile(strPath & strFileIn, ForReading)
    Set otsOut = ofs.CreateTextFile(strPath & strFileOut, True)
    
    lngLine = 0
    Do Until otsIn.AtEndOfStream
        lngLine = lngLine + 1
        strLine = otsIn.ReadLine
        'Only first line needs a change
        If lngLine = 1 Then
            strLine = Replace(strLine, "File .", "File #")
        End If
        otsOut.WriteLine strLine
    Loop
    
    otsIn.Close
    otsOut.Close
    Set otsIn = Nothing
    Set otsOut = Nothing
    Set ofs = Nothing
    
    MsgBox "Text File updated to file " & strPath & strFileOut
    
    End Sub
    Andrew

  12. #12
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by AKW View Post
    Here's an example in principle
    Principle my foot! I tested it and it works great!

    Nice Job. This one goes into my bag of tricks...Thanks Andrew
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. #13
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Wow! Andrew your are awesome, that works perfectly..Thanks for your support..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •