April 2, 2013 at 9:59 am
I am recreating my database as I have had some errors on some of the imports. I also am creating an IDENTITY column, but when I use my typical bulk insert, it doesn't work anymore as in my files I am importing does not have a column for the IDENTITY column in the database. So I have read about the format file, and created it but am now getting this error and I do not know where to go from here:
Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.
The statement has been terminated.
Here is the query for my table properties:
CREATE TABLE [dbo].[Names](
[ID] [int] IDENTITY(1,1) NOT NULL,
[File] [nvarchar](50) NULL,
[First Name] [nvarchar](50) NULL,
[Last Name] [nvarchar](50) NULL,
[Address] [nvarchar](75) NULL,
[City] [nvarchar](50) NULL,
[ST] [nvarchar](2) NULL,
[Zip] [nvarchar](5) NULL,
[Year] [nvarchar](4) NULL,
[Make] [nvarchar](50) NULL,
[Model] [nvarchar](50) NULL
) ON [PRIMARY]
This is the format file:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="75" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="5" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\r" MAX_LENGTH="50" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="File" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="2" NAME="First Name" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="3" NAME="Last Name" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="4" NAME="Address" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="5" NAME="City" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="6" NAME="ST" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="7" NAME="Zip" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="8" NAME="Year" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="9" NAME="Make" xsi:type="SQLNVARCHAR"/>
<COLUMN SOURCE="10" NAME="Model" xsi:type="SQLNVARCHAR"/>
</ROW>
</BCPFORMAT>
And this is my SQL query for the bulk insert:
BULK
INSERT [Names]
FROM 'D:\Mail Files\Kaiser Files\Old\jfinley55 052411.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR=',',
ROWTERMINATOR='',
FORMATFILE ='D:\Mail Files\format.xml'
)
What am I doing wrong? Thanks for the help in advance!
April 2, 2013 at 11:50 am
Typically you would encounter this error when there is an attempt to insert data that is larger than the column can take. For e.g., if you have a "CompanyName" column that is created as VARCHAR (10) and you are trying to insert a value larger than 10 characters, you would get this error.
If you want a quick way to figure this out, create another table similar to your current target table but with much larger lengths for all varchar/nvarchar datatypes. Load your data and query from this new table for rows that have values larger in these columns than allowed (meaning your original target).
Here's an I would take...
Create "Names_New" with larger lengths for varchar/nvarchar columns as below.
CREATE TABLE [dbo].[Names_New](
[ID] [int] IDENTITY(1,1) NOT NULL,
[File] [nvarchar](400) NULL,
[First Name] [nvarchar](200) NULL,
[Last Name] [nvarchar](200) NULL,
[Address] [nvarchar](150) NULL,
[City] [nvarchar](100) NULL,
[ST] [nvarchar](2) NULL,
[Zip] [nvarchar](15) NULL,
[Year] [nvarchar](4) NULL,
[Make] [nvarchar](100) NULL,
[Model] [nvarchar](100) NULL
) ON [PRIMARY]
Load data to Names_New
Now
SELECT * FROM [dbo].[Names_New] WHERE LEN (File) > 50
SELECT * FROM [dbo].[Names_New] WHERE LEN (FirstName) > 50
SELECT * FROM [dbo].[Names_New] WHERE LEN (LastName) > 50
SELECT * FROM [dbo].[Names_New] WHERE LEN (Address) > 75
SELECT * FROM [dbo].[Names_New] WHERE LEN (City) > 50
SELECT * FROM [dbo].[Names_New] WHERE LEN (Zip) > 5
SELECT * FROM [dbo].[Names_New] WHERE LEN (Make) > 50
SELECT * FROM [dbo].[Names_New] WHERE LEN (Model) > 50
With the above queries you would know which columns from the source have larger values than the target permits. I have discounted State and Year columns assuming their lengths are 2 and 4 respectively. You may want to include them if your problem persists. With my experience, I am pretty certain that it is your ZIP field that is exceeding 5 characters. It's very common for addresses to include 4 digit code at the end (E.g. 92780-1831).
I hope this helps.
Regards,
Rex
April 2, 2013 at 11:55 am
I have created the 2nd table and tried inserting the data into it, I receive the same error. As for the Zip column, our zip information is actually two separate columns and I delete that column with the extra four digits as it is irrelevant for this database.
April 2, 2013 at 11:57 am
Expanded the state and year column as well still same error.
April 2, 2013 at 11:59 am
Did you increase the column lengths to a bigger number in your new table? If this didn't help, get a bit innovative and try to load one column at a time, once with the original length and later with a larger length (if it fails). Try eliminating each column. This is the best I can think of.
- Rex
April 2, 2013 at 12:04 pm
Yea I created the table that you wrote up. I will work on it. I might just try and make them max, which I was trying to get away from.
April 2, 2013 at 12:08 pm
Well I just changed the column lengths to max on the database side and it imported. I looked at the data and every column was shifted left one column. So first name was in the file column, last name was in the first name column and address was in the last name column and so on. So what is wrong with my format file as the actual file with the data in it is perfectly fine.
April 2, 2013 at 12:14 pm
Instead of 1 thru 10, try numbering them 0 thru 9.
- Rex
April 2, 2013 at 12:15 pm
That didnt work either.
April 2, 2013 at 12:38 pm
I figured it out. I had to create a view of the database and use it like this:
CREATE VIEW v_names as
SELECT [File], [First Name], [Last Name], [Address], [City], [ST], [Zip], [YEAR], [Make], [Model]
FROM Names;
GO
Use Kaiser;
GO
BULK INSERT v_names
FROM 'D:\Mail Files\Kaiser Files\Old\jfinley55 052411.csv'
WITH
(
FIRSTROW = 2,
FORMATFILE ='D:\Mail Files\format.xml'
)
and from now on I can do this instead since the view is created:
Use Kaiser;
GO
BULK INSERT v_names
FROM 'D:\Mail Files\Kaiser Files\Old\jfinley55 052411.csv'
WITH
(
FIRSTROW = 2,
FORMATFILE ='D:\Mail Files\format.xml'
)
April 2, 2013 at 12:50 pm
That's pretty neat. Thanks for sharing the solution.
- Rex
April 2, 2013 at 2:24 pm
How would the format file look like for the following
I have the follwing txt file with quoted comma delimted file
"col1","Col2","col3","col4","col4","col5","col6","col7"
"text for col1","123 NW St, Suite 23",,,"12345","AZ"
I would have no problem with this, except sometimes in the street address has a comma, then bulk insert thinks it's a new column. then with empty records/values there are no quotes. I have seen some examples using the with format file. How wold I use a format file for this type of file?
bulk insert #temptbl
from 'C:\test\Test.txt'
with (FORMATFILE='C:\test\formattest.txt',rowterminator='')
I also want to add, those empty filed when they have values, they have quotes.
Below is the format text file.
10.0
7
1 SQLCHAR 0 50 "," 1 Col1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 500 "," 2 Col2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 150 "," 3 Col3 SQL_Latin1_General_CP1_CI_AS
......
7 SQLCHAR 0 500 "," 7 Col7 SQL_Latin1_General_CP1_CI_AS
Hmmm...for some reason it doesn't like the 'backslash n' on this post. But as of right now, it's splitting up Col2 as two columns because of the comma.
April 2, 2013 at 2:27 pm
I think you need to change your ROWTERMINATOR to quotes or something like that.
April 2, 2013 at 3:13 pm
I try to use "|" as column separator. It will be a much more cleaner option, if you can have your source text file generated this way.
- Rex
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply