Solved

Export table data to seperate Text Files

Posted on 2014-01-27
3
531 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
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

789 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