July 26, 2012 at 8:23 am
Hello,
I am trying to use the BCP bulk copy utility to import data from a text file
into a table. Both the text file and table have the same number of columns,
in the same order and a format file has been generated.
The database I'm using is called "FullDB" and the text file to import into
the table (table is empty BTW) is called D:\Samples.txt. Here's the basic
form of the BCP command I'm using:
bcp FullDB.dbo.TempSales2 in D:\Sample.txt -T -t,I've tried different combinations of the -f with the appropriate format file
(shwon below) -c, -n and -w.
Here's the table layout:
CREATE TABLE dbo.TempSales2
(
RecID INT NOT NULL,
SalesID INT NOT NULL,
SalesDate DATETIME NOT NULL,
SalesFlag BIT NOT NULL,
Qty TINYINT NOT NULL,
Price DECIMAL(6,2) NOT NULL,
Total DECIMAL(8,2) NOT NULL,
PriceUSD DECIMAL(6,2) NOT NULL,
TotalUSD DECIMAL(8,2) not null
);Here's the format file:
10.0
9
1 SQLINT 0 4 "," 1 RecID
""
2 SQLINT 0 4 "," 2 SalesID
""
3 SQLDATETIME 0 8 "," 3 SalesDate
""
4 SQLBIT 0 1 "," 4 SalesFlag
""
5 SQLTINYINT 0 1 "," 5 Qty
""
6 SQLDECIMAL 1 19 "," 6 Price
""
7 SQLDECIMAL 1 19 "," 7 Total
""
8 SQLDECIMAL 1 19 "," 8 PriceUSD
""
9 SQLDECIMAL 1 19 "" 9 TotalUSD
""And finally, here's a sample of the text file I'm trying to import into the
above table:
1,1,2011-06-13 17:47:42.653,1,1,3.4100,3.4100,5.4560,5.4560
2,1,2011-06-13 17:47:42.653,1,1,3.4100,3.4100,5.4560,5.4560
3,1,2011-06-13 17:47:42.653,1,1,3.4100,3.4100,5.4560,5.4560
4,1,2011-06-13 17:47:42.653,1,1,3.4100,3.4100,5.4560,5.4560
5,1,2011-06-13 17:47:42.653,1,1,3.4100,3.4100,5.4560,5.4560
I have tried various permutations of the bcp command without success. Some
combinations of switches show no errors, but show 0 rows as being processed.
Other permutations cause errors of which the most common appear to indicate
that there is a problem with the date/time field being imported.
Any advice appreciated.
Regards
Steve
Regards
Steve
July 26, 2012 at 9:44 am
You have no end of line. You have to add something (depending on your file) like a \'n' (remove the single quotes around the n):
10.0
9
1 SQLINT 0 4 "," 1 RecID
""
2 SQLINT 0 4 "," 2 SalesID
""
3 SQLDATETIME 0 8 "," 3 SalesDate
""
4 SQLBIT 0 1 "," 4 SalesFlag
""
5 SQLTINYINT 0 1 "," 5 Qty
""
6 SQLDECIMAL 1 19 "," 6 Price
""
7 SQLDECIMAL 1 19 "," 7 Total
""
8 SQLDECIMAL 1 19 "," 8 PriceUSD
""
9 SQLDECIMAL 1 19 "\'n'" 9 TotalUSD
""
Jared
CE - Microsoft
July 26, 2012 at 10:34 am
Your format file doesn't really match your data. The data you are importing is all character with no data lengths. You also have 4 decimal place numbers you are trying to put into 2 decimal place columns. I'd do the following.
Change the table definition:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TempSales2](
[RecID] [int] NOT NULL,
[SalesID] [int] NOT NULL,
[SalesDate] [datetime] NOT NULL,
[SalesFlag] [bit] NOT NULL,
[Qty] [tinyint] NOT NULL,
[Price] [decimal](6, 4) NOT NULL,
[Total] [decimal](8, 4) NOT NULL,
[PriceUSD] [decimal](6, 4) NOT NULL,
[TotalUSD] [decimal](8, 4) NOT NULL
) ON [PRIMARY]
GO
Then change the format file:
10.0
9
1 SQLCHAR014","1RecID""
2SQLCHAR014","2SalesID""
3SQLCHAR030","3SalesDate""
4SQLCHAR01","4SalesFlag""
5 SQLCHAR011","5Qty""
6 SQLCHAR019","6Price""
7 SQLCHAR019","7Total""
8 SQLCHAR019","8PriceUSD""
9 SQLCHAR019"\r\'n'"9TotalUSD""
Sorry, don't know what tag to use on the 'backslash r backslash n' combination to make it show properly in the file.
Invoke BCP with the -f formatfile argument and you should be good to go.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply