Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

Bulk Insert Error: Converting int


Experts.  This problem has my head spinning.

I have a text file and there are 4 columns, tab delimited.
ex:
37501      Quaneyah      Cleveland      NULL
50903      Nelson      Urena-Perez      NULL
127207      Darryl      Muldrew      08

I'm trying to bulk insert this into a table with 4 columns.  
CREATE TABLE [LoadStudentTable] (
      [StudentID] [int] NOT NULL ,
      [StudentFirstName] [varchar] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [StudentLastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [StudentGrade] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

When I run the bulk insert it inserts the table, but gives the following results.
808793907   Quaneyah                            Cleveland                                          NU
809054261   Nelson                              Urena-Perez                                        NU
842478129   Darryl                              Muldrew                                            08

If you notice StudentIds DO NOT match.  Its performing some type of crazy conversion.  I've checked the file using DTS and it works fine, but I HATE DTS and refuse to use it. :)

Thanks in advance!
Pete
0
petemill
Asked:
petemill
  • 11
  • 4
  • 4
  • +1
1 Solution
 
petemillAuthor Commented:
For the record here is the execute statement:

BULK INSERT Operations.dbo.LoadStudentTable FROM 'D:\mssql\export\ses_student_file.csv'
WITH (
        DATAFILETYPE     = 'char'
      , FIELDTERMINATOR  = '\t'
      , KEEPNULLS
      , ROWTERMINATOR    = '\r'
      , FORMATFILE = 'D:\program files\Microsoft SQL Server\MSSQL\import\ses_student_file.fmt'
)
0
 
apirniaCommented:
Are you using a DTS?

It is kind of tricky when exporting from a text file to DB....if you have spaces between your fields it might convert that space to ASCII code number and then insert that into the DB. You can use an Excel to do this or maybe reformat the text file like this:

37501,Quaneyah,Cleveland,NULL
50903,Nelson,Urena-Perez,NULL
127207, Darryl,Muldrew, 08

and then use "," as the delimiter instead of Tab or Space.
0
 
Brendt HessSenior DBACommented:
Can you show the .fmt file?  I suspect a flaw in the definition of that first column - perhaps it is defined as SQLINT, when it should be SQLCHAR...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
petemillAuthor Commented:
Apirnia,

Cant use , .   Those things are bad.. :)    We have a DB of names, so when they put JR  it would be in the lastname as   jordan, jr.     And I try not to use DTS.  Its too black-boxish.  I like to know exactly whats going on with processes.

bhess1,

Here is the .fmt.  Why would I want to define it as SQLCHAR when the table column is INT.  And *I* have always thought 1234 int   =  1234 char..   Call me crazy. :)

8.0
4
1       SQLINT        0       4       "\t"                  1     StudentID            ""
2       SQLCHAR       0       35      "\t"                      2     StudentFirstName     SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       50      "\t"                      3     StudentLastName      SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       2       "\r\n"                    4     StudentGrade         SQL_Latin1_General_CP1_CI_AS
0
 
apirniaCommented:
You can use another delimiter, or combination.......  |~| .
DTS is not really a Black-Box you can actually see all the code it creates.

0
 
petemillAuthor Commented:

I'll have to check that part out.  I've tried | and ~  and will probably go back to one of those and then switch to native code, to protect the data, atleast a little, once I get atleast the one part working.
0
 
rafranciscoCommented:
Try this one if it makes any difference:

BULK INSERT Operations.dbo.LoadStudentTable FROM 'D:\mssql\export\ses_student_file.csv'
WITH (  FORMATFILE = 'D:\program files\Microsoft SQL Server\MSSQL\import\ses_student_file.fmt' )
0
 
petemillAuthor Commented:

rafrancisco:  I believe this was one that I had already tried.  No Luck.

Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
0
 
rafranciscoCommented:
The error you encountered is because of the StudentGrade, because your value is "NULL" while the table is expecting only 2 characters.  Instead of putting the string "NULL", don't put anything in that field.
0
 
petemillAuthor Commented:

rafrancisco:

That may be A problem.  But, its not THE problem.  I'm really looking for why the int field is being converted to a different int value.  I'll go ahead and take out the NULL, but I dont think that will solve the main issue.
0
 
petemillAuthor Commented:

ranfrancisco:  Great!  That solves that problem.  But, the other conversion problem still exists.  Thats the most important one.
0
 
rafranciscoCommented:
Will it be okay with you if you just use SQLCHAR instead of SQLINT in your format file:

8.0
4
1       SQLCHAR       0       10       "\t"               1     StudentID            SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       35      "\t"                      2     StudentFirstName     SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       50      "\t"                      3     StudentLastName      SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       2       "\r\n"                    4     StudentGrade         SQL_Latin1_General_CP1_CI_AS
0
 
petemillAuthor Commented:

Sure.   I can always try.. But, WHY would I need to do that?  I mean, if my table has an INT column and my data is INT, then wouldnt it make sense to use a SQLINT?

I tried it and i worked.  but.... WHY???
0
 
Brendt HessSenior DBACommented:
As noted in the Books-on-line topic "Using Format Files":

Host file data type:  Data type stored in the particular field of the data file. With ASCII data files, use SQLCHAR; for native format data files, use default data types.

Since this is a tab-delimited data file, I can safely assume that it is an ASCII file.  Thus, all fields are considered to be SQLCHAR.  The reason that your numbers are so funny is that it is looking at the characters 12345 (for example), and converting their binary values into the value they would have if that sequence of bytes was an int data type.
0
 
petemillAuthor Commented:

Ok.  When I convert this over to native, at that point, should I use sqlint?
0
 
Brendt HessSenior DBACommented:
Yep.  If you are transferring native format files, then you would indeed use SQLINT.  However, on text files, everything coming in is an SQLCHAR - the system will then attempt to convert the data to the correct datatype, and will generate an error if it is invalid for the destination.
0
 
petemillAuthor Commented:

Great.... thanks for your help.  
0
 
petemillAuthor Commented:
I would have granted both bhess1 and rafranciso with the accepted answer..  but it would only allow 1..  
0
 
rafranciscoCommented:
>> I would have granted both bhess1 and rafranciso with the accepted answer..  but it would only allow 1..  <<

More than one Expert helped solve my problem. What do I do?

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi69

Good luck on your next question.
0
 
petemillAuthor Commented:

I'll keep that in mind next time.. sorry.. :(
0
 
Brendt HessSenior DBACommented:
If you want:

Post a question in the Community Support

http://www.experts-exchange.com/Community_Support/

and ask for help from staff in either re-opening this (so you can award multiple people) or splitting the reward (taking some from me :( and giving it to rafrancisco :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 11
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now