Link to home
Start Free TrialLog in
Avatar of newbieal
newbiealFlag for United States of America

asked on

TSQL: bulk insert - extra white space issue

i have the following bulk insert sp.  It inserts record into a table from a txt file.  The txt file has each record on a separate line, meaning we have \r

However, in the table the first char of the first row has a value, but all of the following records who a blank in that position.  What could the issue be?
DECLARE @bulk_cmd varchar(1000)
SET @bulk_cmd = 'BULK INSERT dbo.info
FROM ''' + @fname + ''' 
WITH (ROWTERMINATOR = ''\r'')'
EXEC(@bulk_cmd)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of dready
dready

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of newbieal

ASKER

Thanks, I downloaded a hex editor and opened the file but am having a difficult time deciphering what I am looking at.  I see that for the space it has the value 20, what does that mean?

Have not tried via DTS.
Avatar of dready
dready

20 is the ascii code for space, but that's not what you're interesting in. Try to find what is the Row terminator. So what comes right after a whole row (or just before the first column of each new row).

Just to see if the file can import, try importing it into a table via enterprise manager -> import data.