Exporting to CSV

[pmc1]

Hi,

I have a table which is based on a query that I want to export to a csv
file on a regular basis.The table has 15 columns and each column is
populated fully.

So the table looks something like

Heading1 Heading2 Heading3 ...
aa bb cc
aa bb cc
aa bb cc

My problem is that, in the resulting CSV I need to preceed the columns
from the table with 2 extra columns the first row of which contains
static text i.e. this text will always be the same regardless of the
contents of the table.

So the resulting CSV would look something like:

NewCol1 NewCol2 Heading1 Heading2 Heading3 ...
SA 3357 aa bb cc
aa bb cc
aa bb cc

Could anyone suggest how I can add these 2 columns to the CSV file and
only have the static text on the first row (Note the text has to be on
in the same row as the first row of the table).

Thanks for any help

PC

[salad]

pmc1 wrote:

Hi,

I have a table which is based on a query that I want to export to a csv
file on a regular basis.The table has 15 columns and each column is
populated fully.

So the table looks something like

Heading1 Heading2 Heading3 ...
aa bb cc
aa bb cc
aa bb cc

My problem is that, in the resulting CSV I need to preceed the columns
from the table with 2 extra columns the first row of which contains
static text i.e. this text will always be the same regardless of the
contents of the table.

So the resulting CSV would look something like:

NewCol1 NewCol2 Heading1 Heading2 Heading3 ...
SA 3357 aa bb cc
aa bb cc
aa bb cc

Could anyone suggest how I can add these 2 columns to the CSV file and
only have the static text on the first row (Note the text has to be on
in the same row as the first row of the table).

Thanks for any help

PC

Make your query a union
Select "" As Col1, "" As Col2, Name, Addr from Test
Union
Select "SA" As Col1, "3357" As Col2, "" As Head1....From Test

Then futz with how you want it ordered so that it floats to the top.

[pmc1]

Thanks for you reply.

This works to a point. The issue I have is that the first row only
contains the static text and each column in the first row there after
is blank. I need the first row of the resulting recordset to contain
both the static text AND the first row of the original table.

Any suggestions

Thanks again.

...pc

salad wrote:

pmc1 wrote:

Hi,

I have a table which is based on a query that I want to export to a csv
file on a regular basis.The table has 15 columns and each column is
populated fully.

So the table looks something like

Heading1 Heading2 Heading3 ...
aa bb cc
aa bb cc
aa bb cc

My problem is that, in the resulting CSV I need to preceed the columns
from the table with 2 extra columns the first row of which contains
static text i.e. this text will always be the same regardless of the
contents of the table.

So the resulting CSV would look something like:

NewCol1 NewCol2 Heading1 Heading2 Heading3 ...
SA 3357 aa bb cc
aa bb cc
aa bb cc

Could anyone suggest how I can add these 2 columns to the CSV file and
only have the static text on the first row (Note the text has to be on
in the same row as the first row of the table).

Thanks for any help

PC

Make your query a union
Select "" As Col1, "" As Col2, Name, Addr from Test
Union
Select "SA" As Col1, "3357" As Col2, "" As Head1....From Test

Then futz with how you want it ordered so that it floats to the top.

[salad]

pmc1 wrote:

Thanks for you reply.

This works to a point. The issue I have is that the first row only
contains the static text and each column in the first row there after
is blank. I need the first row of the resulting recordset to contain
both the static text AND the first row of the original table.

Any suggestions

Thanks again.

Sure. When in doubt, create a kludge.
http://dictionary.reference.com/browse/kludge

You'll have to use your imagination on this and adjust it to your needs.

I will assume you are calling this from a form (we'll call it Form1).
On the form, add a new textbox and call it TopID. Make it's visible
property false. Default Value 0 or "0" depending on if the unique id is
char or numeric.

Now create a query that presents the data for export (use the one you
currently have). Call it Query1. You'll need a field that makes this a
UNIQUE row. I use a field called ID which you might not have in it.

Now, in the code do something like this
Dim rst As DAO.Recordset
set rst = Currentdb.OpenRecordset("Query1",dbopensnapshot)
If rst.RecordCount 0 then
rst.MoveFirst
Forms!Form1!TopID = rst!ID
...process for more actions like runninq query2

OK, Now you've assigned the top row's ID from Query1 to the form's variable.

Now create Query2. It will be something like
Select "" As Col1, "" As Col2, Query1.* From QUery1 _
Where ID <Forms!Form1!TopID
UNION
Select "123" As Col1, "456" As Col2, Query1.* From QUery1 _
Where ID = Forms!Form1!TopID

And futz with the sort.

Basically, any records with an ID not equal to the TopID will have blank
columns and they will get unioned to the one record equal to the top one.

There may be better ways to do this but heck, if it works who cares?