?
Solved

Access 2007 export to text file

Posted on 2012-04-11
14
Medium Priority
?
448 Views
Last Modified: 2012-06-27
I have a project where I need to pull in data from an sql table, then have the user be able to change some of the data. Then I need to have the user be able to export the data in a format that is required by another application. That format is very specific. It needs to be pipe delimited without the quote marks. I am new to access, but I thought this would be fairly simple. I created my database and the table using the sql data. That went fine. I created a query to build a single field with the fields in the correct format with the pipe delimiter. My problem has been the export. I tried exporting the query. I left Export data with formatting and layout unchecked. There are 2 options, delimited and fixed width. Delimited is almost good, except it has " marks at the beginning and the end. So I tried fixed width. The problem there is that for some reason it only shows the 1st 5 characters of each record. I don't know why the truncation. If this could be figured out, I think this might work. Any help would be appreciated. I am not a VB programmer.
0
Comment
Question by:qbjgqbjg
[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
  • 7
  • 6
14 Comments
 
LVL 9

Expert Comment

by:wasiftoor
ID: 37832897
Just send us the exported file if possible and we will be able to give you a better opinion on that.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37832913
<I created a query to build a single field with the fields in the correct format with the pipe delimiter.> DON"T do this

if you want to export certain fields from the table, create a simple select query.
save the query and export.
in the export process, choose the options you want
0
 

Author Comment

by:qbjgqbjg
ID: 37832967
I want all of the fields in the table, but I want them exported as pipe delimited with no quote marks in a .txt file. How do I get this without building the data as I want it?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37832988
export the table,
in the export wizard, choose the options you want
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37833032
is this a one time process or repetitive ?
if repetitive, you need to use VBA codes to this

docmd.transfertext acexportdelim, "exportSpecification","tableName","c:\mytext.txt", true
0
 

Author Comment

by:qbjgqbjg
ID: 37833033
It looks like it is working. I chose delimited, then next and it let me remove the quotes. This will work, but it would be nice if there was a better way so it would be easier for the user.Query1.txt
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37833043
qbjgqbjg,

see my post at http:#a37833032
0
 

Author Comment

by:qbjgqbjg
ID: 37833052
It will be done on a regular basis. I have never done any VBA code. How do I setup that Code:
docmd.transfertext acexportdelim, "exportSpecification","tableName","c:\mytext.txt", true
0
 

Author Comment

by:qbjgqbjg
ID: 37833096
That link did not work for me.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 37833159
first you need to create an export specification

1.right click on the table
2.select export > Text file
   click on Browse and locate the destination folder
3. (you can accept the proposed name or change it)
click Save, then click OK
4. In the export text wizard select the type (Delim Fixed) width
5. Follow the wizard, before clicking on Finish
     5a .Click Advanced
6. In the Export Specification dialog box Field Information List, correct any descrepancies

7. click save as, give the specification a name <-- this is the specification name that you will use in the command line below


DoCmd.TransferText acExportDelim, "ExportSpecName", "TableName", "C:\myText.txt", True
0
 

Author Comment

by:qbjgqbjg
ID: 37833188
OK, I did all of that except the VB command. Where do I enter it? (OK, I know I am a Dummy)
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 2000 total points
ID: 37833214
create a form, add a command button (name it cmdExport) and use the click event of the button

private sub cmdExport_click()

DoCmd.TransferText acExportDelim, "ExportSpecName", "TableName", "C:\myText.txt", True

end sub
0
 

Author Comment

by:qbjgqbjg
ID: 37833239
OK, thanks. Creating a form was going to be my next task.
0
 

Author Closing Comment

by:qbjgqbjg
ID: 37833268
It will take awhile to get the form done. I have a book I am following. You have been a tremendous help. Thanks.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

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 …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 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