Read TXT file with T-SQL 1

[zoranjagodina]

I have source file A.TXT

----------------------------------------------------
100140720102223470000103
200XXXX 000015000000010007000
200YY 000019000000010001600
200Z 000007000000010011200
3000000004100000



when do T-SQL
-- Create a table to hold the data
CREATE TABLE #textfile (line varchar(8000))
-- Read the text file into the temp table
BULK INSERT #textfile FROM 'c:\A.TXT'
GO
-- Now read it
SELECT * FROM #textfile
-- And then clean up
DROP TABLE #textfile
GO

I got target
-------------------------------
100140720102223470000103
200XXXX 000015000000010007000
200YY 000019000000010001600
200Z 000007000000010011200
3000000004100000

Why T-SQL kill blanks ?
( This editor kill blanks in my original post)

My original pos is on

http://www.simpletalk.com/community/forums/thread/93648.aspx

 

[djj55]

Are your "blanks" actually tabs?

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[zoranjagodina]

What is the different ?
I create txt file with NotePad with spaces and with tabs
no differents

 

[djj55]

Depending on how bulk insert is defaulting a tab could be one character.

Try using a format file or FIELDTERMINATOR even though your are only using one column.

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!

 

[zoranjagodina]

Hi djj,
help me about syntax of bcp and format file on my example

 

[djj55]

Hello,
After my last post I thought some more and the format file my or may not be needed.

I am running 2008R2 so I hope the results are the same as yours.

I created two files one without tabs (Test01.txt) and one with tabs (Test02.txt).

create table test1 (myfield varchar(500))

BULK INSERT test1 from 'C:\Test01.txt'
BULK INSERT test1 from 'C:\Test02.txt'

select * from test1

This gave me the spaces but replaced the several tabs with only one space.

My next guess would be the need for a language change to import the tab.

As to format files see "Creating a Format File" is the name of the page I found so try a search on that.

Sorry I cannot be more help but I am at work.

Good Luck,

djj
The Lord is My Shepard (Psalm 23) - I need someone to lead me!