July 11, 2014 at 1:17 pm
Have the following .txt file:
fname,HTMLContent,email
"Kelly","<span style=""font-family:Arial,sans-serif; font-weight:normal; color:#0c2577; font-size:16px;"">Solutions","kelly@reynold.com"
Using the following format file:
9.0
3
1 SQLCHAR 0 150 "," 1 fname ""
2 SQLCHAR 0 150 "\",\"" 2 HTMLContent ""
3 SQLCHAR 0 150 "\r" 3 email ""
When I run this:
CREATE TABLE #TmpStList (
fname varchar(150)
, HTMLContent varchar(150)
, email varchar(150)
);
BULK
INSERT #TmpStList
FROM 'D:\TxtFile.txt'
WITH (
FORMATFILE = 'D:\formatFile.fmt'
, FIRSTROW = 2
)
SELECT *
FROM #TmpStList
I get 0 rows back (no error).
I believe the issue is with the HTMLContent terminator but I'm not sure how to specify a terminator to account for the double quotes in the data.
Any ideas?
TIA
July 13, 2014 at 7:16 pm
Anyone?
July 13, 2014 at 8:31 pm
Hi
Try this as a format file
9.0
5
1 SQLCHAR 0 1 "\"" 0 fquote ""
2 SQLCHAR 0 150 "\",\""1 fname ""
3 SQLCHAR 0 150 "\",\""2 HTMLContent ""
4 SQLCHAR 0 150 "\"" 3 email ""
5 SQLCHAR 0 1 "\r" 0 nline ""
and make sure there is a carriage return on line 2
July 13, 2014 at 8:53 pm
I get the following error with the format file provided and a carriage return on line 2:
Msg 4832, Level 16, State 1, Line 9
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 9
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 9
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
July 13, 2014 at 9:05 pm
Sorry it appears that slash n's are stripped from the forum. Put a \ n after the \ r for line 5 and try that. Without spaces of course:-)
July 13, 2014 at 9:12 pm
Also, before I forget I ran it with this
BULK
INSERT #TmpStList
FROM 'c:\Temp\TxtFile.txt'
WITH (
FORMATFILE = 'c:\Temp\formatFile.fmt'
, FIRSTROW = 1
)
The unquoted header records just got stripped
July 14, 2014 at 11:11 am
You, my friend, are a savior!
After some tweaks, I was able to get it working with my production data file (which has over 40 fields).
Is there a particular resource you use to build format files? This seems to be a poorly documented area of MS SQL.
Thanks!
July 14, 2014 at 1:01 pm
Yeah the documentation isn't that great. I spent a fair amount of time trying to change oracle sqlloader scripts to BCP with limited success. In the end I had to change to a SSIS process. I found BCP quite limited if your flat files aren't completely straight forward.
The XML format file seems a bit more flexible, but requires a bit more work to create. The documentation for that is also a bit better.
For a resource, these forums a great. Most problems that people have encountered have been posted here and answered. I found a post that was almost identical to your own http://www.sqlservercentral.com/Forums/Topic1503039-392-1.aspx
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply