November 29, 2011 at 3:20 am
I am using the following to export a query in the a tab delimited file. I am having a promblem with the format it porduces.
If you open it up in notpad editor it comes up with null characters. If you open the file up in Excel and save back down as tab delimited it is O.K. These is something called a format file I might need to use but being new to SQL I am not sure where to start.
I have been going round and round on this one so would appreciate any help.
exec master..xp_cmdshell 'bcp "SELECT[RecordType],[CustomerGroup],[User],[CustomerNumber]FROM [HBSLIVE].[dbo].[UDEF_WEB_UPDATE_RASS_ASR]" queryout "D:\WebFiles\Web Update File Out\*** Update Files\RASS_ASR_ALL.xls" -w -UUserName -PPassword -SAppserver'
November 29, 2011 at 4:43 am
kyle.doouss (11/29/2011)
I am using the following to export a query in the a tab delimited file. I am having a promblem with the format it porduces.If you open it up in notpad editor it comes up with null characters. If you open the file up in Excel and save back down as tab delimited it is O.K. These is something called a format file I might need to use but being new to SQL I am not sure where to start.
I have been going round and round on this one so would appreciate any help.
exec master..xp_cmdshell 'bcp "SELECT[RecordType],[CustomerGroup],[User],[CustomerNumber]FROM [HBSLIVE].[dbo].[UDEF_WEB_UPDATE_RASS_ASR]" queryout "D:\WebFiles\Web Update File Out\*** Update Files\RASS_ASR_ALL.xls" -w -UUserName -PPassword -SAppserver'
You can start from here.
Creating a Format File
http://msdn.microsoft.com/en-us/library/ms191516.aspx
Using a Format File to Bulk Import Data
November 29, 2011 at 5:10 am
Where do you put the format file. What type of file is it?
November 29, 2011 at 5:15 am
kyle.doouss (11/29/2011)
Where do you put the format file. What type of file is it?
Did you click on the URLs I provided?
November 29, 2011 at 5:19 am
Yes I did but I could not work out how it all went together.
Do you create a note file .fmt. From the bcp do you then put -f path\format file name?
November 29, 2011 at 5:28 am
kyle.doouss (11/29/2011)
Yes I did but I could not work out how it all went together.Do you create a note file .fmt. From the bcp do you then put -f path\format file name?
Yes.
Example:
bcp AdventureWorks2008R2..MyTestFormatFiles format nul -c -t, -f myTestFormatFiles.Fmt -T
Sample Format File:
9.0
4
1 SQLCHAR 0 7 "," 1 Col1 ""
2 SQLCHAR 0 100 "," 2 Col2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 3 Col3 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "\r" 4 Col4 SQL_Latin1_General_CP1_CI_AS
November 29, 2011 at 5:36 am
exec master..xp_cmdshell 'bcp "SELECT[RecordType],[CustomerGroupFROM [HBSLIVE].[dbo].[UDEF_WEB_UPDATE_RASS_ASR]" queryout "D:\WebFiles\Web Update File Out\*** Update Files\RASS_ASR_ALL.xls" -f "D:\WebFiles\Web Update File Out\Format Files\mytestformatfile.fmt -UKYLE -User Name -SAppserver'
If I have a text file in the right format is there a way to create a format file from it?
November 29, 2011 at 5:43 am
bcp "SELECT[RecordType],[CustomerGroupFROM [HBSLIVE].[dbo].[UDEF_WEB_UPDATE_RASS_ASR]" queryout "D:\WebFiles\Web Update File Out\*** Update Files\RASS_ASR_ALL.xls" -f "D:\WebFiles\Web Update File Out\Format Files\mytestformatfile.fmt -UKYLE -User Name -SAppserver
If I have a text file in the right format is there a way to create a format file from it?
Yes.
November 29, 2011 at 8:09 am
Sorry to be so slow. Who would you create a format file from a current tab delimited file?
November 29, 2011 at 8:13 am
kyle.doouss (11/29/2011)
Sorry to be so slow. Who would you create a format file from a current tab delimited file?
No problem at all. I am happy that you are trying... 🙂
Please use "\t" for tab.
November 29, 2011 at 8:14 am
For More: Specifying Field and Row Terminators
November 29, 2011 at 8:29 am
I don't know whether you call it trying?? I appreciate you help.
Below is where I have started. Where am I going wrong. Do you think if I can suss this It will sort out the format issues with bcp putting in null characters?
exec master..xp_cmdshell 'bcp "SELECT[RecordType],[CustomerGroup],[CustomerNumber],[AlfaDebtors],[CodeAssortment],[Authorisation],[ManualMaintenance],[AdressGroup],[Supplier],[CustomerCodeAlfa],[DeleteFlag],[AdressGroup_Long],[CodeAssortment_Long],[ERPCustomerGroup],[FreeForUse]FROM [HBSLIVE].[dbo].[UDEF_WEB_UPDATE_RASS_ASR]" queryout "D:\WebFiles\Web Update File Out\*** Update Files\RASS_ASR_ALL.txt" -f "D:\WebFiles\WebFiles\Web Update File Out\Format Files\RASS_ASR.txt" -UKYLE -P****** -SAppserver'
In the txt file I have
10.0
3
1 SQLCHAR 0 7 "\t" 1 RecordType ""
2 SQLCHAR 0 100 "\t" 2 CustomerGroup ""
3 SQLCHAR 0 100 "\t" 3 CustomerNumber ""
November 29, 2011 at 8:34 am
Do you think if I can suss this It will sort out the format issues with bcp putting in null characters?
You mean SSIS? Yes, you can.
November 29, 2011 at 8:38 am
Sorry when I said sort I was using slang for sort. DO you think it would be better uing SSIS. I have never used this before. What would you use?
November 29, 2011 at 8:46 am
kyle.doouss (11/29/2011)
Sorry when I said sort I was using slang for sort. DO you think it would be better uing SSIS. I have never used this before. What would you use?
If you are comfortable with SSIS, go ahead. I just noticed there are no filters in you query (it's a simple query). So you can use Export Import Wizard also (much easier). You can optionally save Wizard Options as SSIS package & schedule as well.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply