|
dcs
Starting Member
6 Posts |
 Posted - 2011-09-16 : 06:47:41
|
| I am having a csv file which I have to upload on my database. The table is as follows:-CREATE TABLE [dbo].[Out_orders_Table2]([CompanyID] [nchar](3) NULL,[ItemCode] [nchar](9) NULL,[Description] [nchar](38) NULL,[SupplierAccount] [nchar](9) NULL,[PONumber] [nchar](8) NULL,[POLineNumber] [numeric](3, 0) NULL,[OutstandingQty] [numeric](6, 0) NULL,[DueDate] [date] NULL,[StockRoom] [nchar](3) NULL,[PriorityFlag] [nchar](3) NULL) ON [PRIMARY]GOThe data file is like this:-MK,12345678,ABC 123lr RK Finish abcde abcdef ,ABCDEFGH,A123456,1,1234,18/09/2010,W1, CD,98765432,RR CTR APPLE JAM AND JELLY KITS ,ABCDEFGH,A654321,1,25000,08/06/2011,W1, AB,123456,Ab1 Class AB2 3pk R/Ctn M&K Eu Oct07 ,ABCDE007,P123456,8,1200,11/09/2011,CD, EF,123456,2 x Tennis balls, Rackets and bags,TNSRCKBL,T763781,3,2800,10/08/2011,DL, Please note,1. In row 4 there is a comma in the field. And I have to accomodate them and also they are not having double quotes.2. I have used a comma and space delimited file as well. to allow the fields having comma in them. 3. Also the last column 'Priority Flag' doesnt have any data in the CSV file, so the column before it ends like this.. ,CD, 4. I am using SQL SERVER 2008. I am using bcp format file with version 8.0 because it wasnt working for 10.0Format File :- 8.0101 SQLNCHAR 0 3 "" 1 CompanyID "" 2 SQLNCHAR 0 9 "" 2 ItemCode "" 3 SQLNCHAR 0 38 "" 3 Descritpion "" 4 SQLNCHAR 0 9 "" 4 SupplierAccount ""5 SQLNCHAR 0 8 "" 5 PONumber "" 6 SQLNUMERIC 0 3 "" 6 POLineNumber "" 7 SQLNUMERIC 0 6 "" 7 OutstandingQty "" 8 SQLDATE 0 10 "" 8 DueDate "" 9 SQLNCHAR 0 3 "" 9 StockRoom "" 10 SQLNCHAR 0 3 "\n" 10 PriorityFlag "" This format file I manually created. I have already tried:-1. Using an auto generated format file using bcp utility. It gave errors.2. In the above file using SQLCHAR instead of SQLNCHAR.3.using "/r/n" as field terminator.using bcp in command prompt:bcp TestDB.dbo.Orders_Table2 in E:\Testing\OSOTest1.csv -f E:\Testing\OrdersF1.fmt -T -F 2 -b 100Errors:-Starting copy...SQLState = 22008, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]Invalid date formatSQLState = 22003, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]Numeric value out of rangeSQLState = 22001, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncationSQLState = 22001, NativeError = 0Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncationBCP copy in failed======================================================================I used script to do the same thing. The stored procedure is as follows:-USE [TestDB]GOCREATE PROCEDURE [dbo].[example_load_OS_file] AS DECLARE @filename varchar(30) = 'OSOTest1.csv'DECLARE @TSQL varchar(2000)SET @TSQL = 'BULK INSERT [Orders_Table2]FROM '''+'E:\Testing\'+@filename+''' ' SET @TSQL = LTRIM(RTRIM(@TSQL)) EXEC(@TSQL+' WITH (formatfile = '''+'E:\Testing\OrdersF1.fmt'+''')')Errors I receive when I execute this stored procedure:-Msg 7399, Level 16, State 1, Line 2The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Msg 7330, Level 16, State 2, Line 2Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".Should I use openrowset or SSIS for this. I have never worked with them before, and all other CSV files I have uploaded are using a stored procedure.. but this one doesnt seem to work.Plz advice.. Thanks in advance. |
|
Errors with bcp and bulk insert