Link to home
Start Free TrialLog in
Avatar of juricta
jurictaFlag for United States of America

asked on

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

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
Avatar of Simon
Simon
Flag of United Kingdom of Great Britain and Northern Ireland image

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?
Try to export the table which is feeding the report and not the report itself.

use: DoCmd.OutputTo acOutputTable
Avatar of juricta

ASKER

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
You can also use the DoCmd.TransferText  in the table
Avatar of juricta

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of fabriciofonseca
fabriciofonseca
Flag of Brazil image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of juricta

ASKER

Great information!!  Taught me a few things I did not know.  Works perectly!