Solved

Bulk Insert Problem

Posted on 2013-06-25
14
1,335 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
ID: 39275194
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
ID: 39275203
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
ID: 39275246
Some other possibilities:
ROWTERMINATOR = '0x0a'
ROWTERMINATOR = '0x2c0a'
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:qube09
ID: 39275366
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
ID: 39275425
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
ID: 39275427
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
ID: 39275518
And if you try one of this

 (ROWTERMINATOR = ',\n')
 (ROWTERMINATOR = '''+CHAR(10)+''')
 (ROWTERMINATOR = ','''+CHAR(10)+''')
0
 

Author Comment

by:qube09
ID: 39275641
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
ID: 39275657
85 columns each row col and row terminators are ','
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 39275710
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
ID: 39276188
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
ID: 39276363
Add one more line in the format file, 86, and leave it blank. I remember something like this.
0
 

Author Comment

by:qube09
ID: 39278372
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
ID: 39278419
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

790 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