Results 1 to 6 of 6
  1. #1
    smoothlarryhughes is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Sep 2012
    Posts
    36

    Column Headings - Change before uploading to Access Table

    Most of the files that I use to upload into my database are excel and the column headings have spaces prior to the text, which causes problems uploading them into access tables. Is there a way to amend the column headings of my excel files prior to using transferspreadsheet to populate my tables. The only way I know to do this now is to manually amend the column headings to match access prior to uploading.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Could probably do a Search/Replace on the headings row to remove spaces.

    Why would column headings have leading space?
    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
    smoothlarryhughes is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Sep 2012
    Posts
    36
    The files are reports run from an accounting system...i'm not sure why it puts random spaces all over the place. Can vba rename all the column headings prior to putting the data in the table? Or can the headings be ignored?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Yes.
    The import wizard as well as TransferSpreadsheet have parameter to ignore first row.
    http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
    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.

  5. #5
    smoothlarryhughes is offline Advanced Beginner
    Windows 7 64bit Access 2003
    Join Date
    Sep 2012
    Posts
    36
    Quote Originally Posted by June7 View Post
    Yes.
    The import wizard as well as TransferSpreadsheet have parameter to ignore first row.
    http://msdn.microsoft.com/en-us/libr...ffice.12).aspx
    So if I ignore first row how does it know which fields to import the data to? Can I specify which columns I want? Say I only want a:g will it look down the whole column and import where there is data, or do i have to specify rows as well (say a1:g1000)?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Not sure, don't do imports often. Suggest you experiment.

    Probably just imports each column in the order read - column 1 to field 1, etc. No idea what happens if autonumber ID field is first in order.

    You can specify range to import from. Look at the referenced link for guidelines. If there are blank rows within the range, might result in a blank record in Access.
    Last edited by June7; 11-08-2012 at 07:08 PM.
    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.

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

Similar Threads

  1. Replies: 12
    Last Post: 09-13-2012, 12:07 PM
  2. Variable Column Headings
    By asparks16 in forum Queries
    Replies: 1
    Last Post: 06-28-2012, 07:58 PM
  3. csv import second row contains column headings
    By dr_patso in forum Import/Export Data
    Replies: 1
    Last Post: 07-16-2011, 03:56 PM
  4. Resizing column headings
    By allykid in forum Access
    Replies: 0
    Last Post: 03-10-2011, 12:58 PM
  5. Cross Tab Column Headings...
    By mhoctober in forum Queries
    Replies: 3
    Last Post: 09-10-2010, 01:21 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