Solved

Export table data to seperate Text Files

Posted on 2014-01-27
3
502 Views
Last Modified: 2014-01-27
Access 2010 vba routine needed

I have a single table. 2 fields.

fldSegment-text
fldCategory-text


This table contains 206,000+ rows of data

The fld Segment has distinctly 34 names:
Example:
fldSegment
Abrasives
Adhesives, Sealants and Tape
Cleaning
Electrical
Electronics, Appliances, and Batteries
Fasteners
Fleet and Vehicle Maintenance
Furniture, Hospitality and Food Service
Hand Tools
Hardware
HVAC and Refrigeration
Hydraulics
Lab Supplies
Lighting
Lubrication
Machining
Material Handling
Motors
Office Supplies
Outdoor Equipment
Paint, Equipment and Supplies
Plumbing
Pneumatics
Power Tools
Power Transmission
Product Services
Pumps
Raw Materials
Reference and Learning Supplies
Safety
Security
Test Instruments
UNCATEGORIZED
Welding

Open in new window


What I need:  I need to export each one of the Segment Names along with the "fldCategory" into a different text file.. "|<pipe delimited>"  no text qualifier.

So in the end I will have 34 different text files:
Example: Cleaning.txt will have:
fldSegment	fldDescriptors
Cleaning	                Broom
Cleaning	                Broom Brace
Cleaning	                Broom Handle
Cleaning	                Broom Head
Cleaning	                Broom Set
Cleaning	                BROOM VACUUM
Cleaning	                Brooms 18
Cleaning	                Brush
Cleaning	                Brush and Pole Kit

Open in new window


etc,,,,,


Thanks
fordraiders
0
Comment
Question by:fordraiders
3 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
Comment Utility
What have you tried so far? Posting something like "VBA Routine Needed" kinda smacks of "do my job for me ..."

Create a query named qryExport based on "yourTable", and then do something like this:

Dim qdf As DAO.QueryDef
Set qdf = Currentdb.QueryDefs("qryExport")
Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("SELECT DISTINCT fldSegment FROM YourTable")

Do Until rst.EOF
  qdf.SQL = "SELECT * FROM YourTable WHERE fldSegment='" & rst("fldSegment") & "'"
  DoCmd.TransferText acExportDelim, , "qryExport", "YourFile"   
  rst.MoveNext
Loop

Open in new window

You may need to save a "specification" to use with the TransferText method in order to use the pipe delimiter. Here's a link about that:

http://msdn.microsoft.com/en-us/library/office/ff835958.aspx
0
 
LVL 1

Expert Comment

by:MarvinM80
Comment Utility
You might also try to work in the variable "fldSegment" into naming the 34 different output files, so that you get names like "Cleaning.txt" for example.
0
 
LVL 3

Author Closing Comment

by:fordraiders
Comment Utility
Thanks worked great !
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now