Very urgent help with BCP required

[JCAD1]

I am completely new to SSIS and am using BCP to import some text data into SQL Server 2005. It works fine but when you look at the imported data in the table, you will see that some records are not correctly imported. The problem is that the data includes commas which are being interpreted in the file as a field terminator. This occurs mainly in the address column. Because it is a csv file (comma-separated), it splits the address where a comma appears and puts part of it into the next column. Consequently, the data in this column is pushed into the next and so on. In the sample data provided, two records are imported correctly using the script below but four are not.

EXEC MASTER..XP_CMDSHELL 'BCP DWStage..Lama IN E:\Extracts\Test.csv /c /U /P /T /t, /F2'

There are over 100 files loaded into the data warehouse daily and I would like to automate the process of importing them in. I don’t want to modify the files (remove commas from the data) as it would be an impossible task to do. Is there any way of importing this type of csv data into the tables using BCP? Or could it be possible to incorporate a script/stored procedure to do this? Could someone please help me to fix this problem. I am completely stuck.

Thank you very much in advance for your help.

Sample data is attached. Use the following script to create the table.

CREATE TABLE [dbo].[Lama](
[FILE_NAME] [varchar](50) NULL,
[DATA_START_DATE] [varchar](50) NULL,
[DATA_END_DATE] [varchar](50) NULL,
[TOTAL_ROW_COUNT] [varchar](50) NULL,
[ROW_IDENTIFIER] [varchar](50) NULL,
[ADDSS_REFNO] [varchar](50) NOT NULL,
[ADTYP_CODE] [varchar](50) NULL,
[LINE1] [varchar](50) NULL,
[LINE2] [varchar](50) NULL,
[LINE3] [varchar](50) NULL,
[LINE4] [varchar](50) NULL,
[PCODE] [varchar](50) NULL,
[LOCAT_CODE] [varchar](50) NULL,
[HDIST_CODE] [varchar](50) NULL,
[ELECT_CODE] [varchar](50) NULL,
[COUNTY] [varchar](50) NULL,
[CNTRY_REFNO] [varchar](50) NULL,
[START_DTTM] [varchar](50) NULL,
[END_DTTM] [varchar](50) NULL,
[CREATE_DTTM] [varchar](50) NULL,
[MODIF_DTTM] [varchar](50) NULL,
[USER_CREATE] [varchar](50) NULL,
[USER_MODIF] [varchar](50) NULL,
[ARCHV_FLAG] [varchar](50) NULL,
[STRAN_REFNO] [varchar](50) NULL,
[PRIOR_POINTER] [varchar](50) NULL,
[EXTERNAL_KEY] [varchar](50) NULL,
[COUNT_OTCOD_REFNO] [varchar](50) NULL,
[COMMU_OTCOD_REFNO] [varchar](50) NULL,
[PARSH_OTCOD_REFNO] [varchar](50) NULL,
[PUARE_OTCOD_REFNO] [varchar](50) NULL,
[PCARE_OTCOD_REFNO] [varchar](50) NULL,
[RES_REG_DTTM] [varchar](50) NULL,
[SUBURB] [varchar](50) NULL,
[STATE_CODE] [varchar](50) NULL,
[MPIAS_REFNO] [varchar](50) NULL,
[PCG_CODE] [varchar](50) NULL,
[TEAMA_REFNO] [varchar](50) NULL,
[CMMCA_REFNO] [varchar](50) NULL,
[CMMSC_REFNO] [varchar](50) NULL,
[TLAND_REFNO] [varchar](50) NULL,
[SYN_CODE] [varchar](50) NULL,
[DEL_POINT_ID] [varchar](50) NULL,
[QAS_BARCODE] [varchar](500) NULL,
[PAFKey] [varchar](50) NULL,
[QAS_VERIFIED] [varchar](50) NULL,
[PAF_ADD_KEY] [varchar](50) NULL,
[OWNER_HEORG_REFNO] [varchar](50) NULL,
CONSTRAINT [PK_TMP_GP_ADDRESSES] PRIMARY KEY CLUSTERED
(
[ADDSS_REFNO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

Jcad1

 

[hmckillop]

Have a look at creating a format file which means you can be more specific in importing information

Have a look at

http://doc.ddart.net/mssql/sql70/impt_bcp_16.htm

or

http://support.microsoft.com/kb/67409

These should help you specify fields and terminators - will deal with your issue with commas.

"I'm living so far beyond my income that we may almost be said to be living apart