Solved

How to Export a Report to a TEXT file without extra line spacing

Posted on 2014-12-16
7
817 Views
Last Modified: 2014-12-22
I have a database program that takes records from a table, sends it to a report, and exports that report to a text file.  I need the text file to format with ONLY single spacing between the records.  Currently the text file shows/prints with extra line spaces and I canot determine why.  I am attaching a copy of the text file (AS EXPORTED) and also text files (CORRECTED-How I need them to look)  One is a straight line text and the other file has columns that MUST line up with each item.  
Basically I need to remove (or not enter) line spaces for INVENTORY and line up the columns for SALES.
The CODE to export is:
  SALES:-->   DoCmd.OutputTo acOutputReport, "rptSalesDaily", acFormatTXT, ("C:\Users\juricta\Microsoft\Access\Database Files\Working\LiquorReports\" & vOLCCFileName)

INVENTORY--> DoCmd.OutputTo acOutputReport, "rptSalesDailyInventoryConvertToString", acFormatTXT, ("C:\Users\juricta\Microsoft\Access\Database Files\Working\LiquorReports\" & vOLCCFileName)

SALES Table Contents (only a few records)
tblSalesDaily
Product Code      Product Name      List Price      QTY        Price Per   Total Purchase
001312      MONARCH BOURBON-A-BL      $9.00      2      $18.00      $18.00
001402      JIM BEAM                           $19.95      1      $19.95      $37.95
001382      ELIJAH CRAIG                        $24.95      2      $49.90      $49.90
001402      JIM BEAM                                          $19.95      2      $39.90      $89.80
001482      GENTLEMAN JACK                        $31.95      2      $63.90      $63.90
001462      JACK DANIEL #7 BL LA      $22.95      3      $68.85      $132.75
001466      JACK DANIEL #7 BL LA      $2.75      1      $2.75      $2.75
001392      WOODFORD RESERVE      $36.95      2      $73.90      $73.90

INVENTORY Table Contents (only a few records)
tblSalesDailyInventoryConvertToString
ID      StringExportVariable
1      14120919041009OLF00021.FIL 00
2      0014120919041009OLF00063 00
3      631412091904100900700015141209
4      631412091904100900800030141209
5      631412091904100901100005141209
6      631412091904100900109200006141209
7      631412091904100900129200000141209
8      631412091904100900130200003141209



If you need any other information please do not hesitate to ask.
DailyInv141209---As-Exported.txt
DailyInv141209---Corrected.txt
Sales141209---As-Exported.txt
Sales141209---Corrected.txt
0
Comment
Question by:juricta
[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
  • 3
7 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40503658
Hi, neither of your '--Corrected.txt' files are available. I can see the '--As-Exported' files but I get 404 errors for the others.

Can I ask why you want this particular type of output rather than tab or comma delimited text or PDF or Excel? Is it a requirement of some downstream system?
0
 
LVL 8

Expert Comment

by:fabriciofonseca
ID: 40503674
Try to export the table which is feeding the report and not the report itself.

use: DoCmd.OutputTo acOutputTable
0
 

Author Comment

by:juricta
ID: 40503780
SimonAdept
The requirement by the office is it MUST BE TEXT file or it gets rejected.  I will try to reattach the CORRECTED files.  They were only adjusted to remove the line spaces and line up the column entries.  There can be no commas or any other markings on the files for the same reasons listed above.

CORRECTED Inventory should look like this (NO empthy lines):
  14120919041009OLF00021.FIL  00
  0014120919041009OLF00063      00
  631412091904100900700015141209
  631412091904100900800030141209
  631412091904100901100005141209
  631412091904100900109200006141209
  631412091904100900129200000141209
  631412091904100900130200003141209
  631412091904100900131200000141209
  631412091904100900131800000141209

CORRECTED Sales should look like this (ALL columns lined up):

fabriciofonseca:  I will try the tableooutput but like I said...it must be in text format
DailyInv141209---Corrected.txt
Sales141209---Corrected.txt
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 8

Expert Comment

by:fabriciofonseca
ID: 40503784
You can also use the DoCmd.TransferText  in the table
0
 

Author Comment

by:juricta
ID: 40508329
I have tried to use the OutPutTo: acOutputTable (see code line below)
I have tried to use the TransferText (see code line below)

        DoCmd.OutputTo acOutputTable, "tblSalesDailySalesExport", acFormatTXT, ("C:\Users\juricta\Microsoft\Access\Database Files\Working\LiquorReports\Bubba3.txt")

        DoCmd.TransferText acExportDelim, , "tblSalesDailySalesExport", "c:\Users\juricta\Microsoft\Access\Database Files\Working\LiquorReports\bubba31.txt"

The results are attached but still wrong format.  I need just the data...NO quotation marks, NO dashes between records, and NO "PIPE | marks"

An EXAMPLE for SALES---> 3214121815461009001525215468PremisesNO
0
 
LVL 8

Accepted Solution

by:
fabriciofonseca earned 500 total points
ID: 40508472
You get the quotation marks, dashes between records, and "PIPE | marks" because you are not using a specification for the transfer text command.

Try the following:  
        DoCmd.TransferText acExportDelim, "ExportSpec" , "tblSalesDailySalesExport", "c:\Users\juricta\Microsoft\Access\Database Files\Working\LiquorReports\bubba31.txt"

  To create the "ExportSpec" follow the steps below:

  1-) Right Click on the tblSalesDailySalesExport with the Mouse
  2-) Export -> Text File
  3-) Select any export name and Click "OK"
  4-) Click "Advanced" button (left bottom of the page)
  5-) In the Advanced screen you can set or clean quotation marks, dashes between records, and "PIPE | marks", etc
  6-) Click "Save As" and save with the following name: "ExportSpec". This is the name you will use in the cmd.transfertext command.

  I hope it can help.

Regards,
0
 

Author Closing Comment

by:juricta
ID: 40514170
Great information!!  Taught me a few things I did not know.  Works perectly!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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.
Suggested Courses
Course of the Month3 days, 20 hours left to enroll

630 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