Results 1 to 3 of 3
  1. #1
    martingaleh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    22

    Exporting to excel with trailing spaces


    I have a table with text fields that contain values with trailing spaces. As I usually work with oracle data or other enterprise level data, trailing spaces are inevitable. How can I export an access table to excel and retain the trailing spaces.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How do you get Access to retain trailing spaces? Why would you want to retain?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    martingaleh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2015
    Posts
    22

    I see this happen often...

    Quote Originally Posted by June7 View Post
    How do you get Access to retain trailing spaces? Why would you want to retain?
    When access can't do something people will say I've developed on Access for 20 years and I've never had to do x

    If you want to know, I used acces these past two weeks because my new job has it. I'm a financial analyst in a corp that uses oracle and sap because of mergers. These systems keep their trailing spaces so if I want someone non technical to vlookup against it I need to have it.

    Here are some more solutions Ive tried which have all have varying degrees of success and a rant!

    Bringing the excel into access is a big fail because access doesn't import well. Linked tables with the first 25 rows fake and with the right type for type sniffing, batch files that auto import. Forget it. Access isn't made for any of this

    Power query. Our systems put out 100k row excel tables with over 150 columns and power query has to bring the whole thing in before the first manipulation.

    power pivot. Don't even start this stupidity. Case insensitive joins. At least access has alter table alter column binary (510) which necessitates hand joins over binary sometimes.

    If it wasn't for the GUI, the right answer is python and postgre, but you can't package that into one solution and the qt designer doesn't have automatic connects like swing and access.

    i don't know how people program for a living, but I do not know what's wrong with Microsoft. The teams don't talk so the syntax is everywhere, but even worse, the data types are everywhere. M power pivot syntax is case sensitive without a real IDE and intellisense and access isn't case sensitive in their database. Access binary is auto converted to padded 255 string for text export, but dao.recordset2 keeps it at 510 byte array. And, even though they've got the whole 16 bit in 2 byte pairs, it can't handle something as basic as a non width space. If it wasn't for packaging and the forms with nearly auto connects I'd be right to python qt. maybe java swing but I hate hand crafting hash table objects so much boiler plate

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Detecting Trailing Spaces in a nvarchar Field
    By EddieN1 in forum Queries
    Replies: 2
    Last Post: 10-29-2014, 11:22 AM
  2. Replies: 10
    Last Post: 12-28-2012, 02:06 PM
  3. Excel Import - File with row spaces
    By Jackfam58 in forum Import/Export Data
    Replies: 1
    Last Post: 04-13-2011, 07:35 PM
  4. Trailing Spaces workaround
    By shexe in forum Queries
    Replies: 23
    Last Post: 09-21-2010, 04:28 AM
  5. Replies: 9
    Last Post: 07-16-2009, 09:13 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums