bcp and int>ASCII code conversion [modified]
-
hello I'm trying to bcp in matrix of integers into SQL using SQL Server bcp.exe
1,2,3 3,4,5 6,7,8And bcp command as follows:bcp.exe DEV.dbo.SomeIntData in C:\BcpTest\bcp\output\output_int_pc.txt -S SomeServerName -f int.fmt -TIn my formatting file:9.0 3 1 SQLBIGINT 0 25 "," 1 Col1 "" 2 SQLBIGINT 0 25 "," 2 Col2 "" 3 SQLBIGINT 0 25 "\n" 3 Col3 ""My simple test table:CREATE TABLE [SomeIntData]( [Col1] [int] NULL, [Col2] [int] NULL, [Col3] [int] NULL )Problem is, integer value of "1" when inserted (by bcp.exe) into database became "49" (ASCII value/code of "1") - Any suggestion?! Thanks! http://www.asciitable.com/[^] http://msdn.microsoft.com/en-us/library/ms191479.aspx[^] http://msdn.microsoft.com/en-us/library/ms189110.aspx[^]dev
modified on Monday, July 12, 2010 2:37 AM
-
hello I'm trying to bcp in matrix of integers into SQL using SQL Server bcp.exe
1,2,3 3,4,5 6,7,8And bcp command as follows:bcp.exe DEV.dbo.SomeIntData in C:\BcpTest\bcp\output\output_int_pc.txt -S SomeServerName -f int.fmt -TIn my formatting file:9.0 3 1 SQLBIGINT 0 25 "," 1 Col1 "" 2 SQLBIGINT 0 25 "," 2 Col2 "" 3 SQLBIGINT 0 25 "\n" 3 Col3 ""My simple test table:CREATE TABLE [SomeIntData]( [Col1] [int] NULL, [Col2] [int] NULL, [Col3] [int] NULL )Problem is, integer value of "1" when inserted (by bcp.exe) into database became "49" (ASCII value/code of "1") - Any suggestion?! Thanks! http://www.asciitable.com/[^] http://msdn.microsoft.com/en-us/library/ms191479.aspx[^] http://msdn.microsoft.com/en-us/library/ms189110.aspx[^]dev
modified on Monday, July 12, 2010 2:37 AM
Just an observation We found that the BCP.exe in SQL 2008 was a lot more unstable than earlier versions. Apparently MS tightened up some of the formatting rules and it basically broke BCP. If you can make it behave then it is still the fastest load method. We do not do any tansformation during the load, shove everything into varchar fields in a staging table. We then use stored procs to do the transforms. This gives us a number of advantages: No errors in the load Easier to manage the errors in the data when it is right there in front of you. Dramatically faster - I mean hugely faster than either SSIS or Biztalk.
Never underestimate the power of human stupidity RAH
-
Just an observation We found that the BCP.exe in SQL 2008 was a lot more unstable than earlier versions. Apparently MS tightened up some of the formatting rules and it basically broke BCP. If you can make it behave then it is still the fastest load method. We do not do any tansformation during the load, shove everything into varchar fields in a staging table. We then use stored procs to do the transforms. This gives us a number of advantages: No errors in the load Easier to manage the errors in the data when it is right there in front of you. Dramatically faster - I mean hugely faster than either SSIS or Biztalk.
Never underestimate the power of human stupidity RAH
-
nooeee.... I have no time to play around or experiment - and how do you implement "Stored procs to do transforms"? Thanks
dev
You know what the data looks like and you know what/where it needs to be. So the staging tables have the original data, simply write an insert statement with the transformations and lookups (joins) to move it to your final format.
Never underestimate the power of human stupidity RAH
-
You know what the data looks like and you know what/where it needs to be. So the staging tables have the original data, simply write an insert statement with the transformations and lookups (joins) to move it to your final format.
Never underestimate the power of human stupidity RAH
hey, I got it working - problem with bcp format file if you follow MSDN advice and mark a field SQLINT (corresponds to database table col type), you'd end up having this int to ASCII conversion problem. Just mark the damn field as SQLCHAR, and let db do conversion and all good.
dev
-
hey, I got it working - problem with bcp format file if you follow MSDN advice and mark a field SQLINT (corresponds to database table col type), you'd end up having this int to ASCII conversion problem. Just mark the damn field as SQLCHAR, and let db do conversion and all good.
dev
devvvy wrote:
Just mark the damn field as SQLCHAR
Basically what I suggested in the first place, get the data in as char and deal with the crap after you have the data.
Never underestimate the power of human stupidity RAH
-
devvvy wrote:
Just mark the damn field as SQLCHAR
Basically what I suggested in the first place, get the data in as char and deal with the crap after you have the data.
Never underestimate the power of human stupidity RAH
-
devvvy wrote:
Just mark the damn field as SQLCHAR
Basically what I suggested in the first place, get the data in as char and deal with the crap after you have the data.
Never underestimate the power of human stupidity RAH
-
devvvy wrote:
Just mark the damn field as SQLCHAR
Basically what I suggested in the first place, get the data in as char and deal with the crap after you have the data.
Never underestimate the power of human stupidity RAH
Is it possible to skip -f switch (i.e. not supply format file) and let bcp to do this job herself (i.e. infer field type auto like old days?) C:\somedir\bcp.exe DEV.dbo.SomeMixedData in C:\somedir\output\output_abc_mixed.txt -n -S SomeSErver -T Starting copy... 0 rows copied. << Just can't get it working without format file. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1 When open with Notepad my sample file resembles: 20100701,1.110000e+000,2.220000e+000 20100702,3.330000e+000,4.440000e+000 20100703,5.550000e+000,6.660000e+000 And my simple table: CREATE TABLE [dbo].[SomeData]( [Col1] [varchar](255) NULL, [Col2] [varchar](255) NULL, [Col3] [varchar](255) NULL )
dev
modified on Tuesday, July 13, 2010 7:02 AM