Bulk Insert help

Last Post 14 Jul 2014 07:12 AM by gunneyk. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Balance
New Member
New Member

--
10 Jul 2014 03:28 PM
I have the following data in a .txt file

fname,HTMLContent,email
"Kelly","Solutions","kelly@reynold.com"

Using this format file:

9.0
3
1 SQLCHAR 0 150 "," 1 fname ""
2 SQLCHAR 0 150 "," 2 HTMLContent ""
3 SQLCHAR 0 150 "\r\n" 3 email ""


I then 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

DROP TABLE #TmpStList

And the above code yields this:

fname HTMLContent email
"Kelly" "Solutions","kelly@reynold.com"

Not sure if this is obvious given the forum's editor, but basically the value for HTMLContent is being truncated:

"Solutions","kelly@reynold.com"

What terminator should I specify in the format file for the HTMLContent field such that it ignores commas inside the fields?

TIA
Balance
New Member
New Member

--
10 Jul 2014 03:32 PM
Just realized my OP wasn't formatted properly and I don't see an edit function, so here's some clarification...

The content of the txt file is below:

 fname,HTMLContent,email
"Kelly","<span style="font-family:Arial,sans-serif; font-weight:normal; color:#0c2577; font-size:16px;">Solutions","kelly@reynold.com"


SELECT *
FROM #TmpStList

returns this:

 fname    HTMLContent    email
"Kelly"    "<span style="font-family:Arial    sans-serif; font-weight:normal; color:#0c2577; font-size:16px;">Solutions","kelly@reynold.com"
Balance
New Member
New Member

--
11 Jul 2014 06:36 AM
Anyone?
gunneyk
New Member
New Member

--
11 Jul 2014 01:47 PM
OK this isn't my area of expertize but I think you have a few issues. One is that your columns are actually varchar but your format file specifies a length of 150 chars. It is my understanding you want to specify a 0 for the length and just use the column terminator with variable length data. Since you have a comma inside the HTML string you need to specify the column terminator is "," not just , . Try this to see if it works.

BULK
INSERT #TmpStList
FROM 'D:\TxtFile.txt'
WITH (
DATAFILETYPE = 'char', FIELDTERMINATOR = '","' , ROWTERMINATOR = '\r\n'
, FIRSTROW = 2
)
Balance
New Member
New Member

--
13 Jul 2014 05:57 PM
gunneyk,

Your code returns 0 rows.

Any other ideas?
gunneyk
New Member
New Member

--
14 Jul 2014 07:12 AM
Unfortunately I don't. I would suggest using the Import wizard in SSMS to try and import the file using various options and then see what works. I don't think you will be able to use just plain comma's for a delimiter since the HTML has a comma and it has a double quote which also complicates things. You may be better off replacing the "," with a "|" and use pipes as the delimiter to avoid the comma issue.
You are not authorized to post a reply.

Acceptable Use Policy