How to import a Txt file in which collums have different blanks?

Last Post 19 Aug 2004 04:29 PM by hptru64. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
hptru64
New Member
New Member

--
17 Aug 2004 08:58 PM
Hi,
I want to import a .txt file using DTS. The .txt file's format is like that:

Mike KanmuRold Man 20
AlexRober Penxion Woman 35
Dogger Phills Man 40
Johnson KaiNensetings Woman 16

In every rows, the 1st and 2nd field has 9 blanks; the 2nd and 3st Filed has 3 blanks and 3st and 4st has 4 blanks.

Many thanks for answers!!!

hptru64
New Member
New Member

--
19 Aug 2004 12:22 AM
Thanks for your answer and I still look forward to you help!!!
This forum trunted the blanks,so the text file like a normal file. But it is not a fixed width file!
I means different blanks is placed between fields in every row. Such as:

In the 1st row, field1 is "Mike" and field2 is "KanmuRold", which has 9 blank space;

field2 is "KanmuRold" and field3 is "Man", which has 3 blank space;

field3 is "Man" and field4 is "20", which has 4 blank space.


Thanks again!
hptru64
New Member
New Member

--
19 Aug 2004 04:29 PM
The column delimiter is "white space"!
kselvia
New Member
New Member

--
22 Aug 2004 02:02 AM
--This should work if your data looks like you defined.
--Put some sample data in c:\temp\names.txt.
master..xp_cmdshell 'echo Mike<9 spaces>KanmuRold<9 spaces>Man<4 spaces>20>c:\temp\names.txt'
master..xp_cmdshell 'echo AlexRober<9 spaces>Penxion<9 spaces>Woman<4 spaces>35>>c:\temp\names.txt'
master..xp_cmdshell 'echo Dogger<9 spaces>Phills<9 spaces>Man<4 spaces>40>>c:\temp\names.txt'
master..xp_cmdshell 'echo Johnson<9 spaces>KaiNensetings<9 spaces>Woman<4 spaces>16>>c:\temp\names.txt'

-- Create a view to load text file and replace spaces with commas
Create View v_csv_names AS
SELECT Replace(Replace(F1,'<9 spaces>',','),'<4 spaces>',',') NameList FROM
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source="c:\temp";Extended Properties="Text;HDR=No;FMT=FixedLength"')...names#txt

--Export new csv file as c:\temp\names.csv. Change <mydatabasename> to your database name
exec master..xp_cmdshell 'bcp "SELECT NameList FROM <mydatabasename>..v_csv_names" queryout c:\temp\names.csv -c -Slocalhost -T'

--Create table to import into
Create Table names (firstname varchar(30), lastname varchar(30), sex varchar(10), age int)

--Insert data
BULK INSERT names FROM 'c:\temp\names.csv'
WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n' , TABLOCK)

--Looks OK
SELECT * from names

firstname lastname sex age
------------------------------ ------------------------------ ---------- -----------
Mike KanmuRold Man 20
AlexRober Penxion Woman 35
Dogger Phills Man 40
Johnson KaiNensetings Woman 16
You are not authorized to post a reply.

Acceptable Use Policy