urso47 (12/29/2012)
Hi guys,I have been struggling myself for the last two days but I couldn't make it work. Could anyone help me on this statement?
-- TABLE EQUIT
USE TEMPDB
GO
CREATE TABLE Equit
(
cGroupIDINT,
cGroupIDBillableINT,
cGroupNameVARCHAR(MAX),
cGroupDescriptionVARCHAR(MAX),
cRowIDINT,
cRowNameVARCHAR(MAX),
cRowDescriptionVARCHAR(MAX),
cTransactDateSMALLDATETIME,
cTransactTypeVARCHAR(MAX),
cChargeAccountIDVARCHAR(MAX),
cChargeAccountTypeVARCHAR(MAX),
cUserWhoPrintedVARCHAR(MAX),
cDocumentNameVARCHAR(MAX),
cUnitsINT,
cDocumentIDVARCHAR(MAX),
cDeviceIPVARCHAR(MAX),
cDevModelVARCHAR(MAX),
cDevManufVARCHAR(MAX),
cDurationVARCHAR(MAX),
cDestinationVARCHAR(MAX),
cFullNameVARCHAR(MAX),
cPrimaryPINVARCHAR(MAX),
cWorkstationVARCHAR(MAX),
cAmountINT,
JobPropertiesVARCHAR(MAX),
cAltCostINT,
cDepartmentVARCHAR(MAX),
cBillableVARCHAR(MAX),
cAlternatePinVARCHAR(MAX)
)
GO
-- FORMAT FILE FORMAT.EQUIT.FMT
10.0
30
1SQLCHAR00"\""0LineSQL_Latin1_General_CP1_CI_AS
2SQLCHAR00"\","1cGroupIDSQL_Latin1_General_CP1_CI_AS
3SQLCHAR00"\","2cGroupIDBillableSQL_Latin1_General_CP1_CI_AS
4SQLCHAR20",\""3cGroupNameSQL_Latin1_General_CP1_CI_AS
5SQLCHAR20",\""4cGroupDescriptionSQL_Latin1_General_CP1_CI_AS
6SQLCHAR20",\""5cRowIDSQL_Latin1_General_CP1_CI_AS
7SQLCHAR20",\""6cRowNameSQL_Latin1_General_CP1_CI_AS
8SQLCHAR20",\""7cRowDescriptionSQL_Latin1_General_CP1_CI_AS
9SQLCHAR20",\""8cTransactDateSQL_Latin1_General_CP1_CI_AS
10SQLCHAR20",\""9cTransactTypeSQL_Latin1_General_CP1_CI_AS
11SQLCHAR20",\""10cChargeAccountIDSQL_Latin1_General_CP1_CI_AS
12SQLCHAR20",\""11cChargeAccountTypeSQL_Latin1_General_CP1_CI_AS
13SQLCHAR20",\""12cUserWhoPrintedSQL_Latin1_General_CP1_CI_AS
14SQLCHAR20",\""13cDocumentNameSQL_Latin1_General_CP1_CI_AS
15SQLCHAR20"\","14cUnitsSQL_Latin1_General_CP1_CI_AS
16SQLCHAR20",\""15cDocumentIDSQL_Latin1_General_CP1_CI_AS
17SQLCHAR20",\""16cDeviceIPSQL_Latin1_General_CP1_CI_AS
18SQLCHAR20",\""17cDevModelSQL_Latin1_General_CP1_CI_AS
19SQLCHAR20",\""18cDevManufSQL_Latin1_General_CP1_CI_AS
20SQLCHAR20",\""19cDurationSQL_Latin1_General_CP1_CI_AS
21SQLCHAR20",\""20cDestinationSQL_Latin1_General_CP1_CI_AS
22SQLCHAR20",\""21cFullNameSQL_Latin1_General_CP1_CI_AS
23SQLCHAR20",\""22cPrimaryPINSQL_Latin1_General_CP1_CI_AS
24SQLCHAR20",\""23cWorkstationSQL_Latin1_General_CP1_CI_AS
25SQLCHAR20"\","24cAmountSQL_Latin1_General_CP1_CI_AS
26SQLCHAR20",\""25JobPropertiesSQL_Latin1_General_CP1_CI_AS
27SQLCHAR20"\","26cAltCostSQL_Latin1_General_CP1_CI_AS
28SQLCHAR20",\""27cDepartmentSQL_Latin1_General_CP1_CI_AS
29SQLCHAR20",\""28cBillableSQL_Latin1_General_CP1_CI_AS
30SQLCHAR20"\"\r"29cAlternatePinSQL_Latin1_General_CP1_CI_AS
--=== BLANK LINE
MASTER..XP_CMDSHELL 'bcp EQUIT in c:\temp\equit.csv -f c:\temp\equitfmt.fmt'
BULK INSERT EQUIT FROM 'c:\temp\equit.csv' WITH (FORMATFILE = 'c:\temp\equitfmt.fmt')
Error:
(13 row(s) affected)
Msg 4864, Level 16, State 1, Line 2
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (cGroupID).
Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 2
The 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 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
I also tried this one but I got the same error.
DECLARE @CAMINHO VARCHAR(256), @SQL VARCHAR (1000)
SET @CAMINHO = 'C:\TEMP\Equit.csv'
SET @SQL = 'BULK INSERT EQUIT
FROM ''' + @CAMINHO + '''
WITH (FIELDTERMINATOR = '','', CODEPAGE =''ACP'', ROWTERMINATOR='''')'
EXEC (@SQL)
The files used are attached.
Thanks in advance!
You have the right idea with the format file and the \" delimiter thing. Unfortunately, the data dosn't have a consistant format. There are places where a single double-quote is used, places where a double double-quote is used, and empty fields in each row that have no quotes. This is normally what happens when someone exports from a spreadsheet.
That also part of the reason for you import failure messages.
This, however, is what staging tables are all about. The only consistant delimiter is the comma so use that to do the initial import to a staging table and then cleanup/validate the data. And, if you can avoid it, stop using VARCHAR(MAX) for everything. It'll just slow your code down. You'll also need to import everything as a simple VARCHAR instead of SMALLDATETIME and NUMERIC(18,2) as you have in many places because the double quotes won't let them convert.
I'll be back shortly with an example.
--Jeff Moden
Change is inevitable... Change for the better is not.