March 8, 2021 at 6:49 pm
Hi, I am trying to import the attached REF_RF1MAS.txt file but am getting numerous bulk load errors. I was given the attached A_spec that is supposedly the import specs but it does not seem correct. I tried the code below. Any ideas on what the issue is? Thanks.
Msg 4863, Level 16, State 1, Line 27
Bulk load data conversion error (truncation) for row 1, column 2 (REFTYP).
Msg 4864, Level 16, State 1, Line 27
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 6 (SYSDAT).
Msg 4864, Level 16, State 1, Line 27
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 6 (SYSDAT).
Msg 4863, Level 16, State 1, Line 27
Bulk load data conversion error (truncation) for row 4, column 2 (REFTYP).
USE BG
--Create REF_RF1MAS table
CREATE TABLE BG.DBO.REF_RF1MAS
(
PARENT VARCHAR(4) NULL,
REFTYP VARCHAR(4) NOT NULL,
REFVAL VARCHAR(8) NULL,
USERAD VARCHAR(10) NULL,
USERID VARCHAR(10) NULL,
SYSDAT INT NULL,
LSTCHG INT NULL,
DESCRP VARCHAR(50) NULL,
DESCSH VARCHAR(20) NULL,
ALTTYP VARCHAR(1) NULL,
SEQNUM INT NULL,
ALTCOD VARCHAR(2) NULL,
RELTYP VARCHAR(2) NULL,
HISFLG VARCHAR(1) NULL,
REFCAT VARCHAR(8) NULL,
FEDVAL VARCHAR(8) NULL,
FILGRP VARCHAR(1) NULL
) ON [PRIMARY]
--Populate REF_RF1MAS table
BULK INSERT BG.DBO.REF_RF1MAS FROM 'C:\Users\test\Desktop\REF_RF1MAS\REF_RF1MAS\REF_RF1MAS.txt'
March 9, 2021 at 7:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 11, 2021 at 3:47 pm
Well it looks like you have a few different issues to resolve only some of which I can help with.
First, here are a couple PowerShell commands that can help you to peek inside the file.
# outputs the first 100 lines to a new file
Get-Content "C:\temp\REF_RF1MAS.txt" | select -First 100 | Out-File "C:\temp\first100.txt"
# this is is getting a sample in the middle
Get-Content "C:\temp\REF_RF1MAS.txt" | select -First 65321 | select -Last 100 | Out-File "C:\temp\sample.txt"
Once I peeked inside the file, I found that it looks like the first 10 lines may be some kind of header records. To resolve that you can specify FIRSTROW = 10 in your bulk insert command.
BULK INSERT BG.DBO.REF_RF1MAS FROM 'C:\Users\test\Desktop\REF_RF1MAS\REF_RF1MAS\REF_RF1MAS.txt'
WITH (FIRSTROW = 10)
The next thing I found was that your file appears to have six fewer columns than the definition of columns that you have. I'm not sure which ones are missing and which ones are there. I removed PARENT, USERID, ALTTYP, SEQNUM, ALTCOD, and HISFLG, but I have no idea which columns you really have there.
With that change, it doesn't throw an error until line 65221. There it looks like there are 14 lines that perhaps have a Tab character ending the DESCRP which in effect would put another column between DESCRP and DESCSH. But it appears you will run into more issues after that because I tried removing those line and only got about 200 more lines down the file before another error.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply