Bulk insert from non-delimited file

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 2008 Forums

SSIS and Import/Export (2008)

Bulk insert from non-delimited file

Author

Topic

eax
Starting Member

1 Post

Posted - 2011-02-15 : 07:04:12

Hi,
I have text file which is non-delimited, and I need to import it into my table.
Table is:
CREATE TABLE TempTable
(
Field1 char(10),
Date1 date,
Field2 varchar(10),
Number1 int
)

and my import file:
myfile:
abcdefghij2011-01-01abcdefghij98765
abcdefghij2011-01-02abcdefghij99999
abcdefghij2011-01-03abcdefghij00033
abcdefghij2011-01-04abcdefghij12345
abcdefghij2011-01-05abcdefghij00001

I need to import first 10 characters to Field1 column, next 10 to Date1, next 10 to Field2, and next 5 to Number1,
I've tried with openrowset and format file, but doesn't work

Regards

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-15 : 09:34:58

You can use substring function

substring(file,1,10),substring(file,11,10),substring(file,21,10),substring(file,31,5)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources