Solved

bulk insert - not loading alphanumeric data

Posted on 2004-08-06
21
2,496 Views
Last Modified: 2008-01-09
I'm loading a | vertical bar delimited text file (400,000 records)into a SQL table with Bulk insert.  I have the properties codepage and datafile setup to OEM and char.
I tried both the format file and setting the properties on bulk insert task to column delimiter = vertical bar and row delimiter = cr + lf.  It loads just the numeric data.  Worst thing is it loads the same record 400,000 times.  I used tab delimiter, ACP, native - I mean tried different options but didn't make any difference.
Pleaseeeeeeeeeeeee Helpppppppppppp!

I'm going crazy for last 2 weeks.


Any help is really appreciated.
0
Comment
Question by:divyashruthi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
21 Comments
 
LVL 12

Expert Comment

by:patrikt
ID: 11734466
Show us your bulk insert statement. And possibly data sample.
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 11734473
can u paste your bulk insert statement ..........

itsvtk
0
 

Author Comment

by:divyashruthi
ID: 11734488
2      3      0      6      Columbus Branch      !      0      WT      OH      43219      301                             2003-09-12 00:00:00      0      ram2      2003-09-16 00:00:00      2003-09-12 00:00:00      2003-09-12 00:00:00      2003-09-12 00:00:00      .00      2003-09-16 00:00:00      .00      1335      .00      McNaughton-McKay Electric Co      2255 City Gate Drive      .00      Columbus            1                        nmj -wt      27.83            .00000      2      .00000      NULL      25.00      NULL      NULL      NULL      0      0                        .00000      .00000            NULL      NULL      827309      .00      1      2      0      0      0      2      25.00      25.00      501       27.83      27.83      27.83      2003-09-12 00:00:00      .00      0                829113      0      mmo             0      .00      6.58      $       $       ram2      0      2003-09-12 00:00:00      0      0      0      3      0      1      .00      6.58                          0      .00      .00      .00                        301            0      wtei      sxreceiptdt_2003      0


this is the sample data
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:divyashruthi
ID: 11734495
I'm doing this thru DTS - Bulk insert task.  How do I get the statement.  
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 11734522
don't use dts... use below statement.....

Examples
This example imports order detail information from the specified data file using a pipe (|) as the field terminator and |\n as the row terminator.

BULK INSERT Northwind.dbo.[Order Details]
   FROM 'f:\orders\lineitem.tbl'
   WITH
      (
         FIELDTERMINATOR = '|',
         ROWTERMINATOR = '|\n'
      )

This example specifies the FIRE_TRIGGERS argument.

BULK INSERT Northwind.dbo.[Order Details]
   FROM 'f:\orders\lineitem.tbl'
   WITH
     (
        FIELDTERMINATOR = '|',
        ROWTERMINATOR = ':\n',
        FIRE_TRIGGERS
      )



http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_4fec.asp

itsvtk
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 11734545
in your input i didn't find  column delemeter i.e  '|'  in your sample data file....

you can use either bulk insert statement or bcp utility  

bcp utility url

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_bcp_61et.asp

V. Thandava Krishna.
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 11734588
Hi,,

Here is the tutorial on bulk insert & bcp utility....

http://www.sqlteam.com/item.asp?ItemID=3207
http://www.sqlteam.com/item.asp?ItemID=4722

itsvtk
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 11734600
if u get any error while running bulk insert or bcp utility,  paste the error message with error code....

itsvtk
0
 
LVL 2

Expert Comment

by:nexusSam
ID: 11734702
I would defnitely use BCP. The -k option will allow you to specify the column delimiter (remember to escape it if it's being interpreted as a special char).
Also, check that there are no dangling carriage returns. Open your file in a good editor and show spcial chars to check end-of-lines

I very much suspect this is your problem
0
 

Author Comment

by:divyashruthi
ID: 11734766
this is actual sample data

2|403884|0|9|"MICHELIN TIRE CORPORATION"|""|-2119597|"do"|"SC"|"29605"|"101"|""|"1254"|"UPNA"||0|"jeh"|10/19/02|10/30/95||10/30/95|0||0|"1658"|0|"1401 ANTIOCH CHURCH ROAD"|""|0|"GREENVILLE"|""|0|""|""|""|""|0|""|0|3|0||0||||0|0|""|""|""|0|3884|""||12/01/02|0|0|2|2|0|1|1|2|0|0|"201"|0|0|0|10/31/95|0|0|""|0|0|""|""|0|0|0|"%"|"$"|""|0|10/31/95|0|0|0|0|0|0|0|0|""|""|""|0|0|0|0|""|""|101|""|0|"sazaw"|"sxduedt_19951031 sxenterdt_19951030 sxorderdt_19951030 sxreceiptdt_ sxreqshipdt_19951031 sxshipdt_ sxstagecd_9 sxtranstype_do"|0

Please suggest -
I'm doing in DTS b'cos I have to schedule this to run everyday.  Is it better to write a stored procedure with bulk insert statement and execute it thru a DTS instead of doing with Bulk insert task.
0
 
LVL 14

Accepted Solution

by:
Thandava Vallepalli earned 500 total points
ID: 11734841
yes do it like that....

write a stored procedure with bulk statement and make it scheduler.....

itsvtk
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 11734889
first load the data with bulk insert statement.

if it works fine.. then make it scheduler through maintanice plan in enterprize manager...

V.Thandava Krishna.
0
 

Author Comment

by:divyashruthi
ID: 11734948
I created a stored procedure

CREATE PROCEDURE [dbo].[sp_ftp_load_wteh] AS

Bulk insert temp_wteh FROM
'd:\trendextracts\wteh_0803_1.txt'
WITH (
   DATAFILETYPE = 'char',
   FIELDTERMINATOR = '|',
   ROWTERMINATOR = '\n'
)
GO

And when I execute I get the below error

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 6 (notesfl).

The 6th column is defined as varchar in the SQL table.  The source file has a blank.  Don't why it gives this error.  How do I fix it.  Please suggest.
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 11735020
try this....

Bulk insert temp_wteh FROM
'd:\trendextracts\wteh_0803_1.txt'
WITH (
   DATAFILETYPE = 'char',
   FIELDTERMINATOR = '|',
   ROWTERMINATOR = '\n',
   KEEPNULLS
)
GO

itsvtk
0
 

Author Comment

by:divyashruthi
ID: 11735058
I changed to

WITH (
   DATAFILETYPE = 'char',
   FIELDTERMINATOR = '|',
   ROWTERMINATOR = '\n',
   KEEPNULLS
)
GO

still getting below error

Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 6 (notesfl).
(0 row(s) affected)

it is so close..help oh saviors!
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 11735075
what is the version of sql server?

==============
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 6 (notesfl).
===============

the error you typed is bud in sql server ... and microsoft fixed this issue in service pack 3 if you are using sql server 7.0

http://support.microsoft.com/default.aspx?scid=kb;EN-US;275614

itsvtk


0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 11735096
this error is coming for only one row.....  so delete this row from the file and execute the statement.  and let me know whether works fine or not....

itsvtk
0
 

Author Comment

by:divyashruthi
ID: 11735342
Interesting thing -

instead of deleting the first row I put the value |"N"| in the 6th column in the Text file.  When I ran it I got the same error.  Then what I did was, I removed "" (doublequotes) and left N between the pipes.  
The size of the field is 1 in the table.  So it accepted N.  

But my next error is
Server: Msg 4863, Level 16, State 1, Line 1
Bulk insert data conversion error (truncation) for row 1, column 8 (transtype).
(0 row(s) affected)

The column 8 is defined as size 2 in the SQL table.  And the source file has |"do"|.

Therefore, looks like it counting the doublequotes as characters/data to be inserted.

Double quotes is the text qualifier.  "How do I remove the text qualifier" so it doesn't count it.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 11735467
You don't need double quotes to be a text qualifier since the | delimit the columns.  SQL "knows" that the text begins at the
first vertical bar and ends at the second one, so it treats *every* non-delimiter (non-|) in-between as data, including ' or ".
0
 
LVL 14

Expert Comment

by:Thandava Vallepalli
ID: 11735604
no you can't remove text qualifier while loading data using bulk insert.....

so you need to write an external program in java or c++ to remove the quotes.  and then execute the bulk insert statemet.

itsvtk
0
 

Author Comment

by:divyashruthi
ID: 11737395
You people ROCK!  It worked.

I used a unix script to remove the quotes and with the bulk insert stmt it worked perfectly fine.

Thanks a lot.

I have one last question though.

Usually, on some of the fields, the source file has more characters than the destination field.  When I did transform data task, it would automatically truncate the extra characters and store the exact no of characters.  But with Bulk insert, it gives an error.  Now I need to change the field size.  I have 40 tables each containing 100's of fields.  
And I donot know what field in the source file would have more characters than designated.

Any comments/suggestions.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question