Hi,
I'm attempting to use BULK INSERT to import data from a .txt file to a table in my DB.
The problem i'm having is that despite working correctly, the data in the SQL table is imported together with its text qualifier, in this case ".
My code is as follows:
One would expect that the BULK INSERT statement would have a 'TEXTQUALIFIER' property, afterall it has 'FIELDTERMINATOR' and 'ROWTERMINATOR' etc
By using the import/export wizard, i'm able to specify a text qualifier and the data is inserted correctly, is there a way to accomplish the same using BULK INSERT or am i going to have to remove the " after the import?
Any help would be appreciated.
Cheers,
Leigh
Sure, if it has a microchip in it, it must be IT... Now what seems to be the problem with your toaster...?
I'm attempting to use BULK INSERT to import data from a .txt file to a table in my DB.
The problem i'm having is that despite working correctly, the data in the SQL table is imported together with its text qualifier, in this case ".
My code is as follows:
Code:
SET NOCOUNT ON
GO
TRUNCATE TABLE Client_Raw
GO
DECLARE @Practice int
DECLARE @File varchar(255)
DECLARE @SQL varchar(8000)
SET @Practice = 101
SET @File = 'E:\RxWorks\DataExtracts\' + CONVERT(char(3), @Practice) + '_C_Data.txt'
PRINT @File
SET @SQL = N'BULK INSERT Marketing..Client_Raw
FROM ''' + @File + '''
WITH
(
FIELDTERMINATOR = ' + ''',''' + ',
ROWTERMINATOR = ' + '''\n''' + ',
FIRSTROW = 2,
KEEPNULLS
)'
PRINT @SQL
EXEC (@SQL)
GO
SET NOCOUNT OFFOne would expect that the BULK INSERT statement would have a 'TEXTQUALIFIER' property, afterall it has 'FIELDTERMINATOR' and 'ROWTERMINATOR' etc
By using the import/export wizard, i'm able to specify a text qualifier and the data is inserted correctly, is there a way to accomplish the same using BULK INSERT or am i going to have to remove the " after the import?
Any help would be appreciated.
Cheers,
Leigh
Sure, if it has a microchip in it, it must be IT... Now what seems to be the problem with your toaster...?