473,325 Members | 2,860 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Export Access DB to several text files

I have to first admit that I am in no way an Access wizard.

I am trying to automate the export of an access table to several text files.

My table has a field named Ballot_Style. I want to export all records with like values in the ballot style field to a text file that is named <ballot_style>.txt.

I can do this one style at a time using the following query:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT [Sample_Ballot].* INTO [Text;DATABASE=C:\sample].1.txt
  3. FROM Sample_Ballot
  4. WHERE trim([Ballot_Style])=1;
It is kind of a pain to have to edit the query for each of 90 - 300 different values that the Ballot_Style field will contain.

Suggestions would be appreciated.
Thanks,
May 16 '07 #1
4 1750
ADezii
8,834 Expert 8TB
I have to first admit that I am in no way an Access wizard.

I am trying to automate the export of an access table to several text files.

My table has a field named Ballot_Style. I want to export all records with like values in the ballot style field to a text file that is named <ballot_style>.txt.

I can do this one style at a time using the following query:

SELECT [Sample_Ballot].* INTO [Text;DATABASE=C:\sample].1.txt
FROM Sample_Ballot
WHERE trim([Ballot_Style])=1;

It is kind of a pain to have to edit the query for each of 90 - 300 different values that the Ballot_Style field will contain.

Suggestions would be appreciated.
Thanks,
Just for clarification, are you requesting that each Ballot Style be exported to a different Text File as in?
  1. ballot_style_1.txt
  2. ballot_style_2.txt
  3. ballot_style_3.txt
  4. ballot_style_4.txt
  5. ballot_style_5.txt
  6. ballot_style_6.txt
  7. ballot_style_7.txt
  8. ballot_style_8.txt
  9. ballot_style_9.txt
  10. ...
  11. ballot_style_300.txt
NOTE: This can easily be accomplished, but I must know this little detail in advance.
May 17 '07 #2
JConsulting
603 Expert 512MB
I have to first admit that I am in no way an Access wizard.

I am trying to automate the export of an access table to several text files.

My table has a field named Ballot_Style. I want to export all records with like values in the ballot style field to a text file that is named <ballot_style>.txt.

I can do this one style at a time using the following query:

SELECT [Sample_Ballot].* INTO [Text;DATABASE=C:\sample].1.txt
FROM Sample_Ballot
WHERE trim([Ballot_Style])=1;

It is kind of a pain to have to edit the query for each of 90 - 300 different values that the Ballot_Style field will contain.

Suggestions would be appreciated.
Thanks,
How about this

Expand|Select|Wrap|Line Numbers
  1. Function ExportTxt()
  2. Dim rs As dao.Recordset
  3. Dim myStyle As Long
  4. Set rs = CurrentDb.OpenRecordset("select distinct Ballot_Style from Sample_Ballot;")
  5. If rs.EOF Then Exit Function
  6. rs.MoveFirst
  7. myStyle = rs!Ballot_Style
  8. strSQL = "SELECT * INTO [Text;DATABASE=C:\sample]." & myStyle & ".txt FROM Sample_Ballot" & _
  9. " WHERE trim([Ballot_Style])=" & myStyle & ";"
  10. rs.MoveNext
  11. Loop
  12. rs.Close
  13. Set rs = Nothing
  14. End Function
  15.  
May 17 '07 #3
Just for clarification, are you requesting that each Ballot Style be exported to a different Text File as in?
  1. ballot_style_1.txt
  2. ballot_style_2.txt
  3. ballot_style_3.txt
  4. ballot_style_4.txt
  5. ballot_style_5.txt
  6. ballot_style_6.txt
  7. ballot_style_7.txt
  8. ballot_style_8.txt
  9. ballot_style_9.txt
  10. ...
  11. ballot_style_300.txt
NOTE: This can easily be accomplished, but I must know this little detail in advance.
You are correct. I may not have mentioned it, but each exported file needs to be in comma delimited format. In addition, all fields in the Access table are text fields.
Thanks
May 17 '07 #4
JConsulting
603 Expert 512MB
You are correct. I may not have mentioned it, but each exported file needs to be in comma delimited format. In addition, all fields in the Access table are text fields.
Thanks
This should get it.
J

Expand|Select|Wrap|Line Numbers
  1. .
  2. Function ExportTxt()
  3. Dim rs As dao.Recordset
  4. Dim qDf As QueryDef
  5. Dim strSQL As String
  6. Dim myStyle As String
  7. Dim myFile As Long
  8. Set rs = CurrentDb.OpenRecordset("select distinct Ballot_Style from Sample_Ballot;")
  9. If rs.EOF Then Exit Function
  10. rs.MoveFirst
  11. On Error Resume Next
  12. DoCmd.DeleteObject acQuery,   '<--- This deletes the old query getting ready to make a new one
  13. myStyle = rs!Ballot_Style
  14. myFile = "C:\sample\ballot_style_" & myStyle & ".txt"  'ballot_style_1.txt
  15. strSQL = "SELECT * FROM Sample_Ballot WHERE trim([Ballot_Style])='" & myStyle & "';"
  16. Set qDf = CurrentDb.CreateQueryDef("MyQuery", strSQL)
  17. DoCmd.TransferText acExportDelim, , "MyQuery", myFile, False
  18. rs.MoveNext
  19. Loop
  20. rs.Close
  21. Set rs = Nothing
  22. End Function
  23.  
May 17 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Janne Ruuttunen | last post by:
Hello DB2 people, I'm having problems exporting >= 250000 lobs to IXF files with the LOBSINFILE option, using a legacy DB2 2.1 system on Win NT. If I don't specify a path for the lobs,...
1
by: Bridget Willey | last post by:
I am using ACT 6 and am trying to "split" the database between records for customers and junk records. The accounts designated as "customers" have that word in the ID field, and I am using that...
6
by: Peter Frost | last post by:
Please help I don't know if this is possible but what I would really like to do is to use On Error Goto to capture the code that is being executed when an error occurs. Any help would be much...
3
by: Jorge Cecílio | last post by:
Hi! I would like to export some MS-Access reports output to pdf. However, the only possibility offered by Access (afaik) for me to export formatted output is snp (snapshot) (I use MS-Office...
3
by: excyauseme | last post by:
Hi guys! Do you know what is the best way to export a text file, this one is a log file that is already comma delimited thru a module run by my access database, to an excel spreadsheet? I need to...
1
by: Wandering | last post by:
I know you guys are heavy duty coders, while I do ad-hoc analysis, and rarely write code. And, I may be in the wrong groups because this is about an install issue, and I don't think it's a code...
0
by: johnvonc | last post by:
I am using Access 2003, and trying to Export query results in code to a csv file. I am using DoCmd.TransferText. When I run the query, there are two memo fields: Description and...
3
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, I have a question for you. I have a .csv file which has many lines of data. Each line has many data fields which are delimited by ",". Now I need to extract part of data from this...
3
by: Edward Reid | last post by:
OK, I've been watching my DB grow and have known for a long time I would have to move the back end, but of course I waited until I started getting odd behavior (at about 1.92GB). So today I...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.