Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-12-16
7
Medium Priority
?
878 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: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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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 2000 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
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

971 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