How to import data from a text file as is

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to import data from a text file as is

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-10 : 13:49:52

How to import data from a text file as is, I have noticed the the file delimited content differs compare to table content after import.

I have tried using DTS and bulk insert,



create table MyTab ( Id int identity, Junk Varchar(max))
Create view MyView as select Junk from MyTab

bulk insert Dev..MyView from 'C:\etc_file.txt'

The file is actually having space and tab as delimiter, however when I checked the junk column the tab is coming as single space.

I dont want to loose the source content, I want to bring in the source file data as is in the junk column

-Neil

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-10 : 13:56:12

Not sure what yoou are looking for but maybe it's just the way you are displaying the result - have you set the result to text? Try looking at the data in the table as ascii.
Try it with
bulk insert Dev..MyView from 'C:\etc_file.txt' with (fieldterminator='!"£$')

Are the space and tab row delimiters? Do you mean space+tab?
Or are they embedded in the row and yoou are losing the tab and getting the space? If so I would suspect a display rather than data issue - otherwise you are getting down to collations.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-10 : 15:17:38

quote:
Originally posted by aakcse

How to import data from a text file as is, I have noticed the the file delimited content differs compare to table content after import.

I have tried using DTS and bulk insert,

create table MyTab ( Id int identity, Junk Varchar(max))
Create view MyView as select Junk from MyTab

bulk insert Dev..MyView from 'C:\etc_file.txt'

The file is actually having space and tab as delimiter, however when I checked the junk column the tab is coming as single space.

I dont want to loose the source content, I want to bring in the source file data as is in the junk column

-Neil

Try this:

exec master..xp_cmdshell 'bcp yourDB..yourtable in c:\yourfile /c /U /P /r\n'.

--------------------------
Get rich or die trying
--------------------------

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-11 : 08:26:15

exec master..xp_cmdshell 'bcp MyTab in C:\Users\Neil\Desktop\All Clients\AIX\Data Files\etc_file.txt'the above is giving error

Syntax Error in 'Client\AIX'.
usage: bcp [[db_name.]owner.]table_name[:slice_num] [partition pname] {in | out} [filename]
[-m maxerrors] [-f formatfile] [-e errfile] [-d discardfileprefix]
.
.
.
NULL

bulk insert Dev..MyView from 'C:\etc_file.txt' with (fieldterminator='!"£$')

both the above command are not working, the 1st one is giving error and the second one is taking tab as space/.

space and tab are column delimiter, no problem with row delimiter which is linefeed&carriage return

-Neil

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-11 : 10:26:41

try explicitly specifying column delimiter using format file


BULK INSERT Dev..MyView 
FROM 'C:\etc_file.txt' WITH (FORMATFILE=<your file path here>);
GO

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2012-07-11 : 13:36:50

BULK INSERT MyView
FROM 'C:\Users\Neil\Desktop\etc_file.txt' WITH (FORMATFILE = C:\Users\Neil\Desktop\etc_file.txt);

this is giving me syntax err

-Neil

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-07-12 : 09:18:01

You need single quotes

BULK INSERT MyView
FROM 'C:\Users\Neil\Desktop\etc_file.txt' WITH (FORMATFILE = 'C:\Users\Neil\Desktop\etc_file.txt');

and all files are expected to be available in server by default until you use UNC path

Madhivanan

Failing to plan is Planning to fail

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2012-07-12 : 09:20:04

quote:
Originally posted by aakcse

BULK INSERT MyView
FROM 'C:\Users\Neil\Desktop\etc_file.txt' WITH (FORMATFILE = C:\Users\Neil\Desktop\etc_file.txt);

this is giving me syntax err

-Neil

what's the err please?

--------------------------
Get rich or die trying
--------------------------

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-12 : 09:32:39

quote:
Originally posted by aakcse

BULK INSERT MyView
FROM 'C:\Users\Neil\Desktop\etc_file.txt' WITH (FORMATFILE = C:\Users\Neil\Desktop\etc_file.txt);

this is giving me syntax err

-Neil

are all these paths server paths?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources