Solved

Bulk Insert Problem

Posted on 2013-06-25
14
1,234 Views
Last Modified: 2016-02-11
I have a need to import a file on a regular basis into sql server perhaps as often as every 10 minutes. The problem is that I have been unable to find any combination of bulk insert using
row or column termators or format file to accomplish that. The file actually originates on a unix system and is ftp'd to a windows site. There are 85 columns using ',' terminators. This is a
sample.16895625,0,0,0,0,642,2013-06-24 10:17:31,2013-06-24 16:17:31,2013-06-24 10:28:13,2013-06-24 16:28:13,0,0,0,0,0,0,-1,0,-1,-1,-1,61,1,0,0,0,0,0,0,0,0,0,0,0,0,0,1,7,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,00004069091372087042,,000002464,,,,,,000002464,,,,,,,,,,,,,,,,,,0,0,0,0,0,0,
16895626,0,0,0,0,0,2013-06-24 10:28:14,2013-06-24 16:28:14,2013-06-24 10:28:14,2013-06-24 16:28:14,0,0,0,0,0,655,-1,655,-1,-1,-1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,6,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,00004100521372087694,53455,,53455,,,,53455,,,,,,,,,,,,,,,,,,,0,0,0,0,0,0,
sample.txt
BULK INSERT xx.dbo.sample FROM 'c:\sample.txt' WITH (FIELDTERMINATOR = ',',rowterminator=',',formatfile = 'c:\xy.fmt')
Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (callid).
BULK INSERT  xx.dbo.sample FROM 'c:\sample.txt' WITH (FIELDTERMINATOR = ',')
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 85 (icrpullreason).

because it originally came from unix, using the BOL example
DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT xx.dbo.sample
FROM ''c:\sample.txt''
WITH (ROWTERMINATOR = '''+ CHAR(10) +''')';
EXEC (@bulk_cmd)
Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.

Tried on both sql2005 and sql2008r2 same results. Any ideas?
0
Comment
Question by:qube09
  • 5
  • 5
  • 2
  • +1
14 Comments
 
LVL 13

Expert Comment

by:Jesus Rodriguez
Comment Utility
Use the ROWTERMINATOR = '\n'

Look this example from MSN (http://msdn.microsoft.com/en-us/library/ms191485.aspx)

USE AdventureWorks;
GO
BULK INSERT myDepartment FROM 'C:\myDepartment-c-t.txt'
   WITH (
      DATAFILETYPE = 'char',
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n'
);
GO
0
 
LVL 15

Expert Comment

by:dbbishop
Comment Utility
I'm going to hazard a guess: try
BULK INSERT xx.dbo.sample FROM 'c:\sample.txt' WITH (FIELDTERMINATOR=',', ROWTERMINATOR = '''+ ',' + CHAR(10) +''')
0
 
LVL 15

Expert Comment

by:dbbishop
Comment Utility
Some other possibilities:
ROWTERMINATOR = '0x0a'
ROWTERMINATOR = '0x2c0a'
0
 

Author Comment

by:qube09
Comment Utility
none of those work. I am beginning to believe that either a uniquely designed format file or an entirely different approach is the only possibility

I have also tried using openrowset with similar results

k-designers:
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 85 (icrpullreason).
for all rows

dbbishop:that is the BOL example that also failed but I believe that it needs to be dynamic
ROWTERMINATOR = '0x0a'
ROWTERMINATOR = '0x2c0a'
both receive
Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 85. Verify that the field terminator and row terminator are specified correctly.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Few things:

1. In your example there is a format file specified, formatfile = 'c:\xy.fmt, but you didn't provide any info about it. Where is it coming from?

2. There is a discrepancy between the data sample you posted in the question and the  one from the sample file.

In regards to the second point I think that the sample file attached shows that there are 2 different lines per row, one longer that ends with , and a second one shorter which seems to have dates. However in your sample you start with the second line.

Am I correct?
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Sould be
16:28:13,0,0,0,0,0,0,-1,0,-1,-1,-1,61,1,0,0,0,0,0,0,0,0,0,0,0,0,0,1,7,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,00004069091372087042,,000002464,,,,,,000002464,,,,,,,,,,,,,,,,,,0,0,0,0,0,0,
16895626,0,0,0,0,0,2013-06-24 10:28:14,2013-06-24 16:28:14,2013-06-24 10:28:14,2013-06-24

Open in new window



and not

16895625,0,0,0,0,642,2013-06-24 10:17:31,2013-06-24 16:17:31,2013-06-24 10:28:13,2013-06-24 16:28:13,0,0,0,0,0,0,-1,0,-1,-1,-1,61,1,0,0,0,0,0,0,0,0,0,0,0,0,0,1,7,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,00004069091372087042,,000002464,,,,,,000002464,,,,,,,,,,,,,,,,,,0,0,0,0,0,0,

Open in new window


because in the second case the line ends with ,
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
Comment Utility
And if you try one of this

 (ROWTERMINATOR = ',\n')
 (ROWTERMINATOR = '''+CHAR(10)+''')
 (ROWTERMINATOR = ','''+CHAR(10)+''')
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:qube09
Comment Utility
the first two lines as in the attached
16895625,0,0,0,0,642,2013-06-24 10:17:31,2013-06-24 16:17:31,2013-06-24 10:28:13,2013-06-24 16:28:13,0,0,0,0,0,0,-1,0,-1,-1,-1,61,1,0,0,0,0,0,0,0,0,0,0,0,0,0,1,7,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,00004069091372087042,,000002464,,,,,,000002464,,,,,,,,,,,,,,,,,,0,0,0,0,0,0,

16895626,0,0,0,0,0,2013-06-24 10:28:14,2013-06-24 16:28:14,2013-06-24 10:28:14,2013-06-24 16:28:14,0,0,0,0,0,655,-1,655,-1,-1,-1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,6,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,00004100521372087694,53455,,53455,,,,53455,,,,,,,,,,,,,,,,,,,0,0,0,0,0,0,

an attempted format file
9.0
85
1       SQLINT        1       4       ""   1     callid                             ""
2       SQLINT        1       4       ""   2     acwtime                            ""
3       SQLINT        1       4       ""   3     ansholdtime                        ""
4       SQLINT        1       4       ""   4     consulttime                        ""
5       SQLINT        1       4       ""   5     disptime                           ""
6       SQLINT        1       4       ""   6     duration                           ""
7       SQLDATETIME   0       8       ""   7     segstart                           ""
8       SQLDATETIME   0       8       ""   8     segstart_utc                       ""
9       SQLDATETIME   1       8       ""   9     segstop                            ""
10      SQLDATETIME   1       8       ""   10    segstop_utc                        ""
11      SQLINT        1       4       ""   11    talktime                           ""
12      SQLINT        1       4       ""   12    netintime                          ""
13      SQLINT        1       4       ""   13    origholdtime                       ""
14      SQLINT        1       4       ""   14    queuetime                          ""
15      SQLINT        1       4       ""   15    ringtime                           ""
16      SQLSMALLINT   1       2       ""   16    dispivector                        ""
17      SQLSMALLINT   1       2       ""   17    dispsplit                          ""
18      SQLSMALLINT   1       2       ""   18    firstivector                       ""
19      SQLSMALLINT   1       2       ""   19    split1                             ""
20      SQLSMALLINT   1       2       ""   20    split2                             ""
21      SQLSMALLINT   1       2       ""   21    split3                             ""
22      SQLSMALLINT   1       2       ""   22    tkgrp                              ""
23      SQLSMALLINT   1       2       ""   23    eq_locid                           ""
24      SQLSMALLINT   1       2       ""   24    orig_locid                         ""
25      SQLSMALLINT   1       2       ""   25    ans_locid                          ""
26      SQLSMALLINT   1       2       ""   26    obs_locid                          ""
27      SQLSMALLINT   1       2       ""   27    uui_len                            ""
28      SQLSMALLINT   1       2       ""   28    assist                             ""
29      SQLSMALLINT   1       2       ""   29    audio                              ""
30      SQLSMALLINT   1       2       ""   30    conference                         ""
31      SQLSMALLINT   1       2       ""   31    da_queued                          ""
32      SQLSMALLINT   1       2       ""   32    holdabn                            ""
33      SQLSMALLINT   1       2       ""   33    malicious                          ""
34      SQLSMALLINT   1       2       ""   34    observingcall                      ""
35      SQLSMALLINT   1       2       ""   35    transferred                        ""
36      SQLSMALLINT   1       2       ""   36    agt_released                       ""
37      SQLSMALLINT   0       2       ""   37    acd                                ""
38      SQLSMALLINT   1       2       ""   38    disposition                        ""
39      SQLSMALLINT   1       2       ""   39    disppriority                       ""
40      SQLSMALLINT   1       2       ""   40    held                               ""
41      SQLSMALLINT   1       2       ""   41    segment                            ""
42      SQLSMALLINT   1       2       ""   42    ansreason                          ""
43      SQLSMALLINT   1       2       ""   43    origreason                         ""
44      SQLSMALLINT   1       2       ""   44    dispsklevel                        ""
45      SQLSMALLINT   1       2       ""   45    event1                             ""
46      SQLSMALLINT   1       2       ""   46    event2                             ""
47      SQLSMALLINT   1       2       ""   47    event3                             ""
48      SQLSMALLINT   1       2       ""   48    event4                             ""
49      SQLSMALLINT   1       2       ""   49    event5                             ""
50      SQLSMALLINT   1       2       ""   50    event6                             ""
51      SQLSMALLINT   1       2       ""   51    event7                             ""
52      SQLSMALLINT   1       2       ""   52    event8                             ""
53      SQLSMALLINT   1       2       ""   53    event9                             ""
54      SQLCHAR       2       20      ""   54    ucid                               SQL_Latin1_General_CP1_CI_AS
55      SQLCHAR       2       15      ""   55    dispvdn                            SQL_Latin1_General_CP1_CI_AS
56      SQLCHAR       2       9       ""   56    eqloc                              SQL_Latin1_General_CP1_CI_AS
57      SQLCHAR       2       15      ""   57    firstvdn                           SQL_Latin1_General_CP1_CI_AS
58      SQLCHAR       2       15      ""   58    origlogin                          SQL_Latin1_General_CP1_CI_AS
59      SQLCHAR       2       15      ""   59    anslogin                           SQL_Latin1_General_CP1_CI_AS
60      SQLCHAR       2       15      ""   60    lastobserver                       SQL_Latin1_General_CP1_CI_AS
61      SQLCHAR       2       24      ""   61    dialed_num                         SQL_Latin1_General_CP1_CI_AS
62      SQLCHAR       2       24      ""   62    calling_pty                        SQL_Latin1_General_CP1_CI_AS
63      SQLCHAR       2       16      ""   63    lastdigits                         SQL_Latin1_General_CP1_CI_AS
64      SQLCHAR       2       16      ""   64    lastcwc                            SQL_Latin1_General_CP1_CI_AS
65      SQLCHAR       2       2       ""   65    calling_ii                         SQL_Latin1_General_CP1_CI_AS
66      SQLCHAR       2       16      ""   66    cwc1                               SQL_Latin1_General_CP1_CI_AS
67      SQLCHAR       2       16      ""   67    cwc2                               SQL_Latin1_General_CP1_CI_AS
68      SQLCHAR       2       16      ""   68    cwc3                               SQL_Latin1_General_CP1_CI_AS
69      SQLCHAR       2       16      ""   69    cwc4                               SQL_Latin1_General_CP1_CI_AS
70      SQLCHAR       2       16      ""   70    cwc5                               SQL_Latin1_General_CP1_CI_AS
71      SQLCHAR       2       15      ""   71    vdn2                               SQL_Latin1_General_CP1_CI_AS
72      SQLCHAR       2       15      ""   72    vdn3                               SQL_Latin1_General_CP1_CI_AS
73      SQLCHAR       2       15      ""   73    vdn4                               SQL_Latin1_General_CP1_CI_AS
74      SQLCHAR       2       15      ""   74    vdn5                               SQL_Latin1_General_CP1_CI_AS
75      SQLCHAR       2       15      ""   75    vdn6                               SQL_Latin1_General_CP1_CI_AS
76      SQLCHAR       2       15      ""   76    vdn7                               SQL_Latin1_General_CP1_CI_AS
77      SQLCHAR       2       15      ""   77    vdn8                               SQL_Latin1_General_CP1_CI_AS
78      SQLCHAR       2       15      ""   78    vdn9                               SQL_Latin1_General_CP1_CI_AS
79      SQLCHAR       2       192     ""   79    asaiuui                            SQL_Latin1_General_CP1_CI_AS
80      SQLSMALLINT   1       2       ""   80    interuptdel                        ""
81      SQLSMALLINT   1       2       ""   81    agentsurplus                       ""
82      SQLSMALLINT   1       2       ""   82    agentskilllevel                    ""
83      SQLSMALLINT   1       2       ""   83    prefskilllevel                     ""
84      SQLSMALLINT   1       2       ""   84    icrresent                          ""
85      SQLSMALLINT   1       2       ""   85    icrpullreason                      ""
0
 

Author Comment

by:qube09
Comment Utility
85 columns each row col and row terminators are ','
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
The format file is built based on what exists in the data file you used to built it. I recommend you to try a simple bcp pr bulk insert with no format file and make the target table with at least varchar(255) each column.. The problem though is that it seems that a row of data has 2 lines in the file. At least that is what I saw in the data sample you attached.
0
 

Author Comment

by:qube09
Comment Utility
zberteoc
1. format file based on table
2. see my above comment - no row exceeds two lines
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
Comment Utility
Add one more line in the format file, 86, and leave it blank. I remember something like this.
0
 

Author Comment

by:qube09
Comment Utility
zberteoc is close
this is what I did. Using a text file hex editor I verified that the row terminator is 0d0a which can be expected since it is unix going to windows. but there is a column at the end which is also a column delimiter. Cant be the same for both row and column so bulk attempts to load the column in the last column and fails. Solution add a 1 character char column at the end of the table and without using a format file merely:
BULK INSERT  xx.dbo.sample FROM 'c:\sample.txt' WITH (FIELDTERMINATOR = ',')

I might have added that this has to be a staging table since I will need to modify a column for the final table.
tried merely adding a blank line to the format file but nogo.
0
 
LVL 26

Expert Comment

by:Zberteoc
Comment Utility
Try to build the format file starting from the source file and compare them. The problem with this approach is that you will have to adjust the sizes as the current file may not have the data with maximum size.

Have you tried an import wizard, or SSIS?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Here's a requirements document template for an integration project (also known as Extract-Transform-Load or ETL) based on my development experience as an SQL Server Information Services (SSIS) developer over the years.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now