Results 1 to 4 of 4
  1. #1
    chalupabatman is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2016
    Posts
    114

    Exporting Data From Table

    I have a table that is a direct spreadsheet import, and holds the data in a horrid format. Each user has many rows, and I need to export the data so that each user has ONE row.

    I had been creating a CSV List Table using Allen Brownes ConcatRelated() function - then using the QuerySPlit() function to parse the data out of the table and export to Excel - which this was working when the count of records being exported was low, but if the count is extremely high then this process no longer works.

    Ideally - what is the best way to take a table in access that has many rows per user, and export to Excel to ONE row per user?

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Usually when you import a spreadsheet you will have an append query straight afterwards to clean up the data. This may help.

    I presume "no longer works" means it is taking too long? Calling functions from queries slows them down a lot. You could try a totals query, group by the user.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sounds to me like the data isn't in need of 'cleaning up' so much as wanting to transpose rows to columns for export back to Excel. Curious why it doesn't just stay in Excel since it's column oriented rather than row oriented anyway. I've never done that if that is what you're after. Seems to me that a crosstab query that you then transfer to excel, perhaps with an intermediate table if the query doesn't transfer well, would be one way. What I know would work would be automation, whereby you outer loop through the table records and inner loop through the fields, writing to a spreadsheet as you go, one cell at a time. Reach the end of the field set and exit the inner loop, advance the record by one and the spreadsheet row by one and repeat for as many records as there are. You don't say what a lot of records is, so can't say how long this would take.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Would be nice to see the Excel data looks like before import (5 - 10 rows) and what the data looks like/should be when exported back to Excel.

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

Similar Threads

  1. Replies: 3
    Last Post: 10-18-2017, 06:57 PM
  2. Replies: 3
    Last Post: 06-15-2017, 12:14 PM
  3. Access Pivot Table: losing Detail Data when Exporting to Excel
    By MichaelSchulz in forum Import/Export Data
    Replies: 7
    Last Post: 05-12-2015, 05:10 PM
  4. Exporting Data from Table
    By russellporter in forum Import/Export Data
    Replies: 1
    Last Post: 02-26-2013, 09:50 AM
  5. Help with exporting data
    By wee in forum Import/Export Data
    Replies: 10
    Last Post: 09-24-2009, 01:15 AM

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