any driver to import flat into MS SQL Server ??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

SQL Server Development (2000)

any driver to import flat into MS SQL Server ???

Author

Topic

lousueyesh
Starting Member

9 Posts

Posted - 2004-10-13 : 12:05:38

Hi,

Does anyone know whether there are any (preferrably free) drivers out there that can help import flat files into MS SQL Server???

Thanks,
lousueyesh

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-10-13 : 12:29:02

why not just write sql code for it ? - No Financial Cost involved except dev time)

[CODE]
BULK INSERT TableName
FROM 'Filename.txt'
WITH (FieldTerminator = '|')
[/CODE]

Duane.

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-13 : 12:46:20

or you can use the EM import wizard

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

lousueyesh
Starting Member

9 Posts

Posted - 2004-10-13 : 12:54:27

Thanks for your recommendations,

The flat file I am referring to is in binary format instead of text format.

Please tell me what is the EM import wizard and where can I get access to use it???

Thanks,
lousueyesh

quote:
Originally posted by tuenty

or you can use the EM import wizard

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-10-14 : 04:53:58

binary eh? that's kinda interesting. Fixed length defined?

see if this helps you at all:


declare @binstr	varbinary(100)

--A=41	B=42	C=43	D=44	E=45	F=46	G=47	H=48	I=49	J=4A	K=4B	L=4C	M=4D	N=4E	O=4F
--P=50	Q=51	R=52	S=53	T=54	U=55	V=56	W=57	X=58	Y=59	Z=5A
--0=30	1=31	2=32	3=33	4=34	5=35	6=36	7=37	8=38	9=39	

--Test String
set @binstr = 0x4649454C443156414C5545314649454C443256414C5545313939
--Display full string
select convert(char(26),@binstr)
--substring and convert values from the input string
select 	convert(char(6),substring(@binstr,1,6))AS First_Col,
	convert(char(6),substring(@binstr,7,6))AS First_Val,
	convert(char(6),substring(@binstr,13,6))AS Second_Col,
	convert(char(8),substring(@binstr,19,8))AS Second_Value

If you have a known, fixed format in the binary file, you could create a strucutre that contains that mapping, and use that structure to traverse through you input data - I am sure that the wiser heads here can give you a better approach, though ...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-15 : 12:59:27

IN THE ENTERPRISE MANAGER\TOOLS\WIZARDS\DATA TRANSFORMATION SERVICES\DTS IMPORT WIZARD OR JUST RIGHT CLICK A DATABASE OR TABLE AND SELECT ALL TASK\IMPORT WIZARD

ONCE IN THE IMPORT WIZARD SELECT THE APPROPIATE DRIVER (SOURCE), FILL IN DESTINATION INFORMATION, AND SPECIFY THE DATATYPE FOR EACH FIELD.

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources