Bulk Insert which Creates quote marks

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Bulk Insert which Creates quote marks

Author

Topic

Dubzzz
Starting Member

8 Posts

Posted - 2012-08-30 : 10:54:41

Hi All,
I am once more in the dark and I hope one of you will help me remove my sun glasses :)

I am uploading a .csv files containing 39 columns which are either text or numbers into a table...
To do so I create a temporary table and insert records from the csv file via Bulk Insert. This works fine but I do not understand why it seems that the Bulk Insert functions makes the text been surrounded by " quotation marks. These are not from the csv file as I checked it in not pad... fields are separated by a comma only.
Why is this happening?
Below is the script and after it is first lines of CSV File...

Thanks for your support

SCRIPT

CREATE TABLE tmp_CBSLEDGERIMPORT
([Journal Source] nvarchar(255),
[Journal Category] nvarchar(255),
[GL Year] int,
[GL Period] int,
Comp nvarchar(255),
Account nvarchar(255),
Org nvarchar(255),
Reg nvarchar(255),
Div nvarchar(255),
PG nvarchar(255),
PT nvarchar(255),
SCC nvarchar(255),
RCC nvarchar(255),
Spare nvarchar(255),
Curr nvarchar(255),
[Amount Entered] Float,
[Amount Functional] Float,
[Status] nvarchar(255),
[Batch Name] nvarchar(255),
[Batch Description] nvarchar(255),
[Journal Name] nvarchar(255),
[Journal Description] nvarchar(255),
[Jrnl Doc_Id] nvarchar(255),
Reversal nvarchar(255),
[Header Reserval] nvarchar(255),
[Line Num] nvarchar(255),
[Line Description] nvarchar(255),
[Subledger Doc_Id] nvarchar(255),
[Line Reference 1] nvarchar(255),
[Line Reference 2] nvarchar(255),
[Line Reference 3] nvarchar(255),
[Line Reference 4] nvarchar(255),
[Line Reference 5] nvarchar(255),
[Line Reference 6] nvarchar(255),
[Line Reference 7] nvarchar(255),
[Line Reference 8] nvarchar(255),
[Line Reference 9] nvarchar(255),
[Line Reference 10] nvarchar(255),
[GL Posted Date] nvarchar(255));

BULK INSERT tmp_CBSLEDGERIMPORT
FROM 'D:\DATA\GLlines\2011GLCBS_5_5.csv'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

csv

Journal Source,Journal Category,GL Year,GL Period,Comp,Account,Org,Reg,Div,PG,PT,SCC,RCC,Spare,Curr,Amount Entered,Amount Functional,Status,Batch Name,Batch Description,Journal Name,Journal Description,Jrnl Doc_Id,Reversal,Header Reserval,Line Num,Line Description,Subledger Doc_Id,Line Reference 1,Line Reference 2,Line Reference 3,Line Reference 4,Line Reference 5,Line Reference 6,Line Reference 7,Line Reference 8,Line Reference 9,Line Reference 10,GL Posted Date
Manual,Manual Journal,2011,1,ABX2,601000,3,90,161,98,98,998,G999,0,EUR,749510.82,749510.82,P,HB AR PL 50&60 02-FEB-2011 20:34:27,,HB AR PL 50&60,AR PL 50&60,33675,N,R,1,AR PL 50&60,,,,,,,,,,,,02/02/2011 20:39
Manual,Manual Journal,2011,1,ABX2,601000,3,90,161,CR,72,930,G999,0,EUR,-749510.82,-749510.82,P,HB AR PL 50&60 02-FEB-2011 20:34:27,,HB AR PL 50&60,AR PL 50&60,33675,N,R,2,AR PL 50&60,,,,,,,,,,,,02/02/2011 20:39
MassAllocation,Reclass,2011,1,ABX1,601095,3,90,211,BF,10,920,AAS1,0,EUR,-10.79,-10.79,P,MA: 83413280 Freight allocation P&L JAN-11,,FREIGHT TRANSFER P&L COS MERCH,FREIGHT TRANSFER P&L COS MERCH,1126149,N,,10,FREIGHT TRANSFER P&L COS MERCH,,,,,,,,,,,,01/02/2011 14:07

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 10:57:11

you can specify format file to avoid "" text qualifiers.

see

http://www.nigelrivett.net/SQLTsql/BCP_quoted_CSV_Format_file.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dubzzz
Starting Member

8 Posts

Posted - 2012-08-30 : 11:14:10

I am not too sure about the format file.
From what I read, this is to identify the format of the csv file. but the csv file doesn't contain any "" quote... they seem to be created by the Bulk Insert

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 11:19:15

quote:
Originally posted by Dubzzz

I am not too sure about the format file.
From what I read, this is to identify the format of the csv file. but the csv file doesn't contain any "" quote... they seem to be created by the Bulk Insert

hmm...I dont think so bulk insert wont insert text qulifiers like that

Have you opened file in notepad and checked if it doesnt have text qualifier by itself?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Dubzzz
Starting Member

8 Posts

Posted - 2012-08-30 : 11:23:19

Yes, I checked in notepad.. You can see extract of it at the end of the first post... Could it be when I create the temporary table

Dubzzz
Starting Member

8 Posts

Posted - 2012-08-30 : 12:34:20

Any other suggestions?

Dubzzz
Starting Member

8 Posts

Posted - 2012-08-30 : 13:19:12

Sorry I triple checked the file itself and it seems the data is inconsistently coded woth "," or , only!
Thanks for your help! Anyway

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 15:41:11

quote:
Originally posted by Dubzzz

Sorry I triple checked the file itself and it seems the data is inconsistently coded woth "," or , only!
Thanks for your help! Anyway

cool
then solution is to use format file as specified. but data delimiter has to be consistent anyways

or another way is to bulk insert or bcp to intermediate table using , delimiter and then use REPLACE() to strip off the " s before populating the target table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources