BULK INSERT with Format File..
in [Programming SQL]
|
From: DarioA on 26 Mar 2010 08:17 Friendly. I have the following table in server SQL 2005. CREATE TABLE dbo.acontlab( WCLOT numeric(8, 0) NOT NULL, WCFEC numeric(8, 0) NOT NULL, WCHOR numeric(8, 0) NOT NULL, WCUSU varchar(10) NOT NULL) I want to concern a flat text file without delimiters with name ACONTLAB.TXT. I put the first registries like example so that they can occur an idea: 000000012003012809350681JOSE 000000022003012907342126HUGOSER 000000032003013007264604HUGOSER In addition, I made the following format file with name ACONTLAB.FMT: 7.0 4 1 SQLNUMERIC 0 8 "" 1 WCLOT 2 SQLNUMERIC 0 8 "" 2 WCFEC 3 SQLNUMERIC 0 8 "" 3 WCHOR 4 SQLCHAR 0 10 "\r\n" 4 WCUSU Soon I execute following the sentences from Management Studio: Bulk insert dbo.acontlab from 'M:\borrar\acontlab.txt' With (formatfile = 'M:\borrar\acontlab.fmt') When executing, it throws the following message to me of error (Translation from Spanish): Mens. 9803, Level 16, Been 1, Line 1 Nonvalid Data for the type “numeric”. The instruction was finished. Also I tried changing SQLNUMERIC by SQLDECIMAL; obtaining identical result. I am thankful for any aid that can offer me.
From: Sylvain Lafontaine on 26 Mar 2010 12:00 You must replace SQLNUMERIC with SQLCHAR. SQLNUMERIC is used for files that have been written in binary, not in text. The difference is that you can read the file with your eyes when it is written in text but not really when it has been written in binary. Also, the version of BCP for SQL-2005 is 10.0 and not 7.0 for the first line. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "DarioA" <darioalbarenqueJUERASPAM(a)hotmail.com> wrote in message news:op.u96cfsu762ilbi(a)prg-003.col1.sfe... > Friendly. I have the following table in server SQL 2005. > CREATE TABLE dbo.acontlab( > WCLOT numeric(8, 0) NOT NULL, > WCFEC numeric(8, 0) NOT NULL, > WCHOR numeric(8, 0) NOT NULL, > WCUSU varchar(10) NOT NULL) > > I want to concern a flat text file without delimiters with name > ACONTLAB.TXT. I put the first registries like example so that they can > occur an idea: > 000000012003012809350681JOSE > 000000022003012907342126HUGOSER > 000000032003013007264604HUGOSER > > In addition, I made the following format file with name ACONTLAB.FMT: > 7.0 > 4 > 1 SQLNUMERIC 0 8 "" 1 > WCLOT > 2 SQLNUMERIC 0 8 "" 2 > WCFEC > 3 SQLNUMERIC 0 8 "" 3 > WCHOR > 4 SQLCHAR 0 10 "\r\n" 4 > WCUSU > > Soon I execute following the sentences from Management Studio: > Bulk insert dbo.acontlab > from 'M:\borrar\acontlab.txt' > With (formatfile = 'M:\borrar\acontlab.fmt') > > When executing, it throws the following message to me of error > (Translation from Spanish): > Mens. 9803, Level 16, Been 1, Line 1 > Nonvalid Data for the type "numeric". > The instruction was finished. > > Also I tried changing SQLNUMERIC by SQLDECIMAL; obtaining identical > result. > > I am thankful for any aid that can offer me. >
From: Erland Sommarskog on 26 Mar 2010 18:55 Sylvain Lafontaine (sylvainlafontaine2009(a)yahoo.ca) writes: > Also, the version of BCP for SQL-2005 is 10.0 and not 7.0 for the first > line. Yeah, but if you use 7.0 you don't have to bother with that noisy collation field. :-) -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Sylvain Lafontaine on 26 Mar 2010 19:54 Ah yes, this collation field. BTW, I made a small error: the version for SQL-2005 should be 9.0 and not 10.0; which is for SQL-Server 2008. -- Sylvain Lafontaine, ing. MVP - Windows Live Platform Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message news:Xns9D47F35B6C6ABYazorman(a)127.0.0.1... > Sylvain Lafontaine (sylvainlafontaine2009(a)yahoo.ca) writes: >> Also, the version of BCP for SQL-2005 is 10.0 and not 7.0 for the first >> line. > > Yeah, but if you use 7.0 you don't have to bother with that noisy > collation field. :-) > > > > -- > Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >
|
Pages: 1 Prev: urgent date format problem help needed.... Next: Query Optimization |