• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 778
  • Last Modified:

why there is problem with bulk insert

First, I create table:
CREATE TABLE [dbo].[ResultsOrdered](
      [DrawDate] [varchar](10) NOT NULL,
      [Sh1] [tinyint] NULL,
      [Sh2] [tinyint] NULL,
      [Sh3] [tinyint] NULL,
      [Sh4] [tinyint] NULL,
      [Sh5] [tinyint] NULL,
      [Sh6] [tinyint] NULL
)

then I am trying to populate it with data from text file where each row is in format:
dd.MM.yyyy 01 02 03 04 05 06(delimeters are horizontal tabs)

with  bulk insert:
bulk insert ResultsOrdered
from 'c:\ResultsOrdered.txt'
with
(
 
  rowterminator='\n',
  fieldterminator='\t'
 
)

and get the error:
Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (DrawDate).

What do  I do wrong?
0
AlexKostrub
Asked:
AlexKostrub
1 Solution
 
Aaron ShiloChief Database ArchitectCommented:
hi

it seems your tring to load date into varchar.
try changing the column type in the table to datetime.
or remove the " . " (period) from the date in the flat file.
0
 
AlexKostrubAuthor Commented:
Using Date type for the first column produces similar error(even with set dateformat dmy). Why I can not load date-semilar data into varchar when it is also a sequence of symbols?
0
 
Aaron ShiloChief Database ArchitectCommented:
i think the issue with datetime format 16/11/1909 (dd/mm/yyyy) while sql server may be expecting in mm/dd/yyyy format
0
 
arnoldCommented:
Could you post a snippet of the data?
0
 
ispalenyCommented:
Try

bulk insert ResultsOrdered
from 'c:\ResultsOrdered.txt'
with
(
  CODEPAGE = 'RAW',
  rowterminator='\n',
  fieldterminator='\t'
 
);
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now