Bulk Import Errors - Dbl Quotes maybe problem

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)

Bulk Import Errors - Dbl Quotes maybe problem

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-18 : 10:46:05

Hi Guys,I’m trying to do a Bulk Insert but I am receiving the following error:
conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 8 (Phone).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 8 (Phone).".
Task failed: dbo.tblNoName

The data is comma delimited which I stipulated in my Import Connection section. But the data also has quotes around it (ie. The field Lname nvarchar (17) = “Brown” and the field phone nvarchar (10) = “12345678921”. Is there a way to ignore the quotes or do I have to remove them before I import?

Or is my problem something else all together?

The connection is solid;
Format = “Specify”
RowDelimiter = {CR}{LF}
columnDelimiter = Comma {,}

No other options are set.

The data looks like:
"tstLName","tstFname","000 N Tst DR","IDAHO sp","ID","00000000",

Thank you,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-18 : 11:26:48

Are you using BULKINSERT T-SQL command? Have you tried using a format file option?

Trudye
Posting Yak Master

169 Posts

Posted - 2008-03-18 : 13:10:07

Thank you so much visakh for responding so quickly. No I'm using SSIS tool for Bulk Insert. If I use Format file option don't I have to have a *.fmt file? If not what should my Connection screen look like

Something like this?
Format = use file
FormatFile = name of *.fmt file

I manage to pull an old one I remember seeing a while back, but I’m not sure what the format is:

7.0
6
1 SQLCHAR 0 2 "" 1 RCode
2 SQLCHAR 0 4 "" 2 CCode
3 SQLCHAR 0 4 "" 3 Filler1

I know that col 1 is field number, col 2 is data type, col 4 is field length, col 6 look like field number again and of course col 7 is fieldname. Not too sure about the others.

Also any idea what row 1 (7.0) and row 2 (6) mean?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources