Create file with header and append bcp data

Last Post 20 Aug 2012 03:18 AM by rasika. 4 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
jmaikido
New Member
New Member

--
05 Mar 2008 08:16 PM
Hi Guys,

Is there an easy way, perhaps via SSIS to create a .txt file with the following header and then bcp data from SQL Server into this file? If i simply create the file with the header, how can i export data from SQL Server into this file without removing the header?

Header structure:

DataShed Export; Table Spec : Micromine
10 VARIABLES
DataSet C 30 0
Hole_ID C 20 0
Hole_Type C 10 0
Max_Depth N 7 2
NAT_Grid_IC 50 0
NAT_East N 10 2
NAT_North N 10 2
NAT_RL N 6 2
Parent_HolC 20 0
Precollar_N 4 0

Many thnx in adv.

Rgds,
John
SQLUSA
New Member
New Member

--
06 Mar 2008 12:36 AM
bcp needs a FORMAT file.

Checkout BOL.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
BI Workshop NYC - March 17-20: http://www.sqlusa.com/order2005bitr...008031720/
nosepicker
New Member
New Member

--
06 Mar 2008 09:10 AM
If all you want to do is append data to an existing file with your header, you can use SSIS or BCP to export the data to a second file, and then append that data to the first file with a command line redirect:

type YourDataFile.txt >> YourHeaderFile.txt

Now, if you want to import your data into a table from a file that has a header, that's a different story. It's possible, but a little more involved. I've been able to do it, but in a hacky sort of way. If you want to do that, let me know and I'll give you the gory details.
SQLUSA
New Member
New Member

--
07 Mar 2008 12:12 AM
>Now, if you want to import your data into a table from a file that has a header, that's a different story. It's possible, but a little more involved. I've been able to do it, but in a hacky sort of way. If you want to do that, let me know and I'll give you the gory details.
>

I don't get it.

Header first line is a simple checkmark in DTS/SSIS Import/Export wizards.

Kalman Toth - Database, Data Warehouse & Business Intelligence Architect
BI Workshop NYC SSAS, SSRS, SSIS - March 17-20: http://www.sqlusa.com
rasika
New Member
New Member

--
20 Aug 2012 03:18 AM
This should work to solve you problem

Create a new file then use xp_cmdshell to append

bcp to c:\tmp.txt
then
exec master..xp_cmdshell 'type c:\tmp.txt >> c:\myfile.txt'
exec master..xp_cmdshell 'del c:\tmp.txt'

Create a file with headers say "c:\myfile.txt'"
bcp query out data into a file say c:\tmp.txt
Then append data into myfile using
exec master..xp_cmdshell 'type c:\tmp.txt >> c:\myfile.txt'

It would definately help, i did the same.

Thanks,
Rasika.


Acceptable Use Policy
---