Solved

Export table data to seperate Text Files

Posted on 2014-01-27
3
543 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 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

To Patch or not to Patch? That is the question!

Don't get caught out like thousands of others around the world in the recent Ransomware Fiasco!
Discuss..
- Why it's not a good idea to wait before Patching
- Sensible approaches to Patching discussed
- Add your feedback, comments and suggestions

Question has a verified solution.

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

Suggested Solutions

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

738 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