Solved

Export table data to seperate Text Files

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

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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

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.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

636 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