CSV to Staging table import using BCP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

CSV to Staging table import using BCP

Author

Topic

bbs_4_stock
Starting Member

2 Posts

Posted - 2008-03-31 : 03:24:35

Hi Guys,

I'm trying to import the contents of a CSV file into a staging table that I've created in SQL server 2005. To perform the import I have used the BCP utility with the use of a BCP format file.

The problem I'm having is that the data in some of the fields in the csv file are longer than the length of the corresponding field in my staging table. So when I try to import I get the following error:

[Microsoft][ODBC SQL Server Driver]String data, right truncation

And the record with the error does not import.

My question would be, is there a way of telling BCP to trim the
data before importing into the staging table? Could I somehow
specify in the BCP file to trim the data before importing or
is there a switch that i can specify in the BCP command which tells BCP to trim data to the length of the destination column.

If it helps I'm using the command below to run BCP.

bcp S_OLTPDEV.dbo.CX_LOTS_STG in %path1%\CTS_Siebel\Lots\To_Siebel_From_CTS\lotupdates.csv -f %path1%\CTS_Siebel\Lots\To_Siebel_From_CTS\Lots_Format.fmt -F 1 -S %dbsrvr% -U %duname% -P %dpasswd%

Thanks in advance, any help would be really appreciated.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-03-31 : 05:30:33

Why would you ewant to truncate it at this point? Just make the fields in the staging table large enough to hold all the data and then trim it afterwards if that is what you require.

bbs_4_stock
Starting Member

2 Posts

Posted - 2008-03-31 : 06:34:37

Firstly thanks for the reply.

From the staging table I would need to tranfer records to the base tables of a CRM system such as MSCRM. And the reason I would want to perform the trimming at this point is because the legacy system that the CSV is being generated from does not have definite lengths for its fields and making the column lengths big enough won't solve the problem as the data from the legacy system could still be longer.

Is this a limitation with BCP? are there other MS tools that I can use to perform this?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-03-31 : 07:26:54

There are a few ways to do it. Two of them are:

You can open the file in SSIS or use a query with OpenRowSet to get the format you require for your bcp:


select * from OpenRowset('MSDASQL','Driver={Microsoft Text Driver (*.txt; *.csv)}; 
						DefaultDir=C:\Test;Extended properties='';Format=CSVDelimited;''',
						'select * from Test.csv')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources