Solved

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

Posted on 2014-12-16
7
709 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
  • 3
  • 3
7 Comments
 
LVL 18

Expert Comment

by:SimonAdept
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now