Eliminate Blank Records from Exported Text file

[maryanncanor]

hi,

Good day. I use Ms Access 2003 to export data from table to TEXTFILE.

this is my Actual output:

EMP003 Matt Dwayne
EMP001 Josh Harnett

EMP005 Peter Parker
EMP006 Flakes Graham
EMP007 Kate Winslett

EMP009 Brittany Murphy
EMP010 Sophia Bush
EMP002 Jake Connor

EMP003 Bruce Wayne
EMP004 Mac Donalds



And this is my Desired output:
EMP003 Matt Dwayne
EMP001 Josh Harnett
EMP005 Peter Parker
EMP006 Flakes Graham
EMP007 Kate Winslett
EMP009 Brittany Murphy
EMP010 Sophia Bush
EMP002 Jake Connor
EMP003 Bruce Wayne
EMP004 Mac Donalds

How do I eliminate the blank spaces between in textfile.

Thank you!!!

[FishVal]

Hi, there.

You should provide more information:

• How do you perform export?
• Relevant dataset(s) metadata. Here is an example of how to post table MetaData :
  Table Name=tblStudent
  

  Field; Type; IndexInfo
  StudentID; AutoNumber; PK
  Family; String; FK
  Name; String
  University; String; FK
  Mark; Numeric
  LastAttendance; Date/Time

Regards,
Fish

[NeoPa]

Instead of exporting the table directly, create a Query which filters out empty lines. Export this query instead of the table.

[maryanncanor]

Instead of exporting the table directly, create a Query which filters out empty lines. Export this query instead of the table.

Hi,

I generate output thru using the Send report to a file using the Command button wizard. I am using a button to export a report.


How do I create a query that empty lines. Can elaborate on this.

Thanks

[NeoPa]

I can try, but I need a feeling for what you're already familiar with.

A. Do you have a query that the report runs from?
B. What do you know about queries already?

You should understand that queries only do base data - no totals or anything or even running totals.

[maryanncanor]

I can try, but I need a feeling for what you're already familiar with.

A. Do you have a query that the report runs from?
B. What do you know about queries already?

You should understand that queries only do base data - no totals or anything or even running totals.

Hi thanks for your time.

I have a query that displays what I need. Its just that there are spaces between.
My query is very basic. It selects the fields that I want to generate. I use the Output to function in Command Button. I can also use macro to export textfile from an existing Report.

and the textfiles appears like these:


00000001 1 DIRF
00000002 1 DIRF

00000003 1 DIRF
00000004 1 DIRF
00000005 1 DIRF

00000006 1 DIRF
00000007 1 DIRF
00000008 1 DIRF


Thank you for helping me.

[NeoPa]

Let's take the focus away from the report for the moment.

I need to know a bit about the query (what is its name and what is the SQL code for it)?

When run natively (again not the report - just running the query) does it include any blank lines then?

[maryanncanor]

Let's take the focus away from the report for the moment.

I need to know a bit about the query (what is its name and what is the SQL code for it)?

When run natively (again not the report - just running the query) does it include any blank lines then?

Hi there,

I created a table named RT1_Declarant_ Information_1_TBL
I created fields in this table. Then I made a query.
The query's name is Query1. I created an Expr1 field in the query to concatenate all the fields and put it in 1 field which is Expr1

sql view of query:
SELECT [SEQ_NUM] & [TYPE] & [DECLARANTS] & [FILENAME] & [CALENDAR_YR] & [O/R] & [DECLARATION_STATUS] & [NAME_BUSNAME_DEC] AS Expr1
FROM [RT1_Declarant_ Information_1];


If I run the concatenated query Expr1: [SEQ_NUM] & [TYPE] & [DECLARANTS] & [FILENAME] & [CALENDAR_YR] & [O/R] & [DECLARATION_STATUS] & [NAME_BUSNAME_DEC]


and export it in Export Text wizard as text, with delimited, Other: | (pipeline)
and Text qualifier as none>next>finish

I can get the desired output without quotes. But this is a very long process for a user.....

If I try to use Query1 to make a Report. The Output appears like this:


00000004100023569995DIRF2008R1131200811SMITH,JULES
00000005145467623417979DIRF2008O2100200811WAYNE,BR UCE
00000006100087844554DIRF2008O2111200811ANDREWS,REN EE

00000007178797998989945DIRF2008O1120200801ALLEN,MA RK
00000008100098989255DIRF2008O1131200811OWEN,HALLE
00000009147232645666665DIRF2008O1100200811MOONIE,I AN

00000010100059855685DIRF2008O1111200811CASTILLO,MA NUEL
00000011158956563365886DIRF2006R1220200810AMANTE,P OPE
00000012115487875215457DIRF2002R1231200810SALTA,NE IL



Is there a macro that can output textfile from a query without quotes so I can just use a macro in a Command button.

I would like to export data from an existing concatenated query Expr1 to an output textfle without spaces..

Thanks again. :)

[NeoPa]

Ah, now we have something a little more solid to work with. Well done.

Try :

SELECT [SEQ_NUM] &
       [TYPE] &
       [DECLARANTS] &
       [FILENAME] &
       [CALENDAR_YR] &
       [O/R] &
       [DECLARATION_STATUS] &
       [NAME_BUSNAME_DEC] AS Expr1
FROM [RT1_Declarant_ Information_1]
WHERE [SEQ_NUM] &
      [TYPE] &
      [DECLARANTS] &
      [FILENAME] &
      [CALENDAR_YR] &
      [O/R] &
      [DECLARATION_STATUS] &
      [NAME_BUSNAME_DEC]=''

This may well work better for your report too (depending on exactly what you need in it).

A query (QueryDef) with this SQL can be exported from within Access.

[maryanncanor]

Hi thanks for your time, however, the code that you gave me outputs a blank query. Am I missing something?

[NeoPa]

Ah, now we have something a little more solid to work with. Well done.

Try :

SELECT [SEQ_NUM] &
       [TYPE] &
       [DECLARANTS] &
       [FILENAME] &
       [CALENDAR_YR] &
       [O/R] &
       [DECLARATION_STATUS] &
       [NAME_BUSNAME_DEC] AS Expr1
FROM [RT1_Declarant_ Information_1]
WHERE [SEQ_NUM] &
      [TYPE] &
      [DECLARANTS] &
      [FILENAME] &
      [CALENDAR_YR] &
      [O/R] &
      [DECLARATION_STATUS] &
      [NAME_BUSNAME_DEC]=''

This may well work better for your report too (depending on exactly what you need in it).

A query (QueryDef) with this SQL can be exported from within Access.

Probably not.

I don't have direct access to your database so can't check how various fields are set up. Try changing the [=''] in line 17 to [ Is Null] instead :

[NAME_BUSNAME_DEC] Is Null