Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Export table data to seperate Text Files

Posted on 2014-01-27
3
Medium Priority
?
562 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 39813635
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
ID: 39813803
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
ID: 39814197
Thanks worked great !
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

824 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