April 20, 2009 at 12:49 pm
Hi,
I have a datafile that contains a terminator within the datavalues (,) and when importing that data into a data table the data returns double quotes within the file. I came across this issue when using the Bulk Insert.
SET QUOTED_IDENTIFIER OFF
BULK INSERT GloverTest
FROM 'C:\merged.csv'
WITH (
DATAFILETYPE = 'char',
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
I receive the following results in the datatable:
HIGHS-4"1 High Street LLC"
HIGHS-4"1 High Street LLC"
The CSV file is
"HIGHS-4","1 High Street,LLC"
"HIGHS-4","1 High Street,LLC"
The issue is the comma within the data itself how can BULK Insert resolved this. In Access the text qualifier is used.
Thanks,
April 20, 2009 at 4:58 pm
Why QUOTED_IDENTIFIER is OFF?
_____________
Code for TallyGenerator
April 20, 2009 at 5:54 pm
I was trying to load additional parameters. I didn't have that set before and still encountered the same issue
April 20, 2009 at 9:59 pm
Set up a linked server to text file and load from the file like from remore table.
Or use OPENQUERY.
See BOL for details. Topic "Linked Servers".
Example from there:
--Create a linked server.
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\data\distqry',
NULL,
'Text'
GO
--Set up login mappings.
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
GO
--List the tables in the linked server.
EXEC sp_tables_ex txtsrv
GO
--Query one of the tables: file1#txt
--using a 4-part name.
SELECT *
FROM txtsrv...[file1#txt]
_____________
Code for TallyGenerator
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply