how to extract field names when using 'copy to' ? 1

[Simonbegg]

Hello Everyone.
Need to be able to export a cursor to a text file.
That bit is no problem.
The issue is that the reciever wants the field names as the first record. How can i do that?
At the moment I am just doing...

>Select curResultTable
>Locate
>Copy To &lcfullfilepath Delimited With "" With Character "|"

where &lcfullfilepath is a txt file.
Can it be done with 'copy to'?
Cheers

 

[r0gerc]

What about LIST TO filename.txt OFF? Can you do that from within a program?

Best of luck,
Roger

 

[Ed Andres]

If you could go with CSV, the field headers are there and then maybe do some post processing to change the delimiter. If your data contains commas, then that would be a problem.

Ed

 

[Stella740pl]

The issue is that the reciever wants the field names as the first record. How can i do that? ... Can it be done with 'copy to'?

Yes, if you use COPY TO ... TYPE CSV. Field names do get exported to the .CSV, but the output will be different - your values will be (you guessed it) comma separated, not delimited with character "|".

Alternatively, you can loop through field names, create a character string out of it the way you want it, open your text file and insert it before everything else. I think you might be able to do it with low-level functions (like FOPEN and FWRITE or FPUTS, etc.), but didn't touch those for a while, so wouldn't bet on it.

What about LIST TO filename.txt OFF? Can you do that from within a program?

Of course you can. The resulting file would be a fixed-width file, not delimited. But maybe your receiver would be happy with it - you can ask.

How do they plan to use the file?

 

[Simonbegg]

How do they plan to use the file?"
Import into an SQl server. But they are insisting on | and field headings. I have suggested that I could put the file directly onto their SQL server as an SQL table, but no, they want a flat text file 'in case anything goes wrong'

 

[Simonbegg]

And thanks to all for the advice.

 

[TamarGranor]

I think Stella's idea of post-processing is best. But rather than using low-level file functions, just use FileToStr() and StrToFile(), roughly:

cTextFile = FileToStr("yourfile.txt")
cTextFile = cHeaderRow = m.cTextFile
StrToFile(m.cTextFile, "yourfile.txt")

Tamar

 

[Dan Freeman]

Typo alert:

cTextFile = cHeaderRow + m.cTextFile

I'd also skip the interim variable. I've had problems with larger files creating variables that are too large, but strtofile()/filetostr() don't seem to have a limit:

StrToFile(cHeaderRow+FileToStr("yourfile.txt")
, "anotherfile.txt")

 

[Simonbegg]

Tamar & Dan.
Many thanks.

 

[Olaf Doschke]

It's quite an overhead to output the text file, then write a new one with a header line and then append the first output to that. If your export tables are large, there are much better options, as your destination is SQL Server.

There are many ways for sql server to load such a flat file and none of them really needs such a header, it's just a little bit more convenient.

The best way to work with bulk load in sql server 2005 is SSIS (sql server integration services). You can use flat text files as a source, and it might be more convenient to have a column name row, but ssis flat file connection manager also allows you to interpret the whole file as data rows with no such column row. It's just a bit more work to then map the flat file columns to fields of the sql server destination table. But there are simple cases, eg field structure matches 1:1.

Then there is the BULK INSERT command and the bcp command line utility, both of which can also work with a flat text file without column name headers, they just need an additional format file, which can be generated by bcp for example from the structure of the destination file. That's even more helpful than just a column name row, as it defines not only column names but also field types etc.

Bye, Olaf.