OpenRowSet - SQL Server Forums

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

Transact-SQL (2005)

OpenRowSet

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-01-16 : 17:01:09

i was wondering if anyone would be able to help me adapt this code to work on a tab-delimited file instead of excel. Thanks in advance!

select phone,first,last,address,city,state,zip,email,internetsite
,timestamp,ipaddress
INTO ##Recieved
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
                'Excel 8.0;Database=C:\order.xls',
                'select phone,first,last,address,city,state,zip,email,internetsite
,timestamp,ipaddress FROM [Order$]')

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-01-16 : 17:19:20

http://technet.microsoft.com/en-us/library/ms190312.aspx

look at example 'E' on the link above

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-01-16 : 18:06:19

Alright, so i copied the code for values.fmt into c:\values.fmt and copied my data into c:\values.txt then i executed the code:


SELECT a.* FROM OPENROWSET( BULK 'c:\values.txt', 
   FORMATFILE = 'c:\values.fmt') AS a;

got the following error:

Msg 4862, Level 16, State 1, Line 1
Cannot bulk load because the file "c:\values.fmt" could not be read. Operating system error code (null).

i've never used format files, so this is all a little new to me :o(

Edit: here's a snippet of the data:


PHONE	NAME	FIRST	LAST	ADDRESS	CITY	STATE	ZIP	EMAIL	TIMESTAMP	IPADDRESS	INTERNETSITE	INCOME	CC	AGE	DATEAPPENDED
4087123182		se mi	lee	10591 n. de anza blvd	cupertino	ca	95014	sleeslee83@yahoo.com	12/10/2007 9:02	67.180.83.54	www.myamazingformula.com				01-15-08
4405373429		marilyn	brown	3865 w. 18th street	cleveland	oh	44109	too_nawty47@yahoo.com	12/10/2007 9:03	63.252.61.155	www.myamazingformula.com				01-15-08
3135295946		grace	hopson	11228beaconsfield	detroit	mi	48224	grace_hopson@yahoo.com	12/10/2007 9:03	71.238.148.10	www.myamazingformula.com				01-15-08
4193765275		melissa	meyers	2913 w. temperance rd.	temperance	mi	48182	shortcakes288@hotmail.com	12/10/2007 9:04	72.241.40.252	www.myamazingformula.com				01-15-08
2072336633		katherine	ralston	177 pilgrim road	south portland	me	4106	kayandmatt@att.net	12/10/2007 9:05	24.97.242.154	www.myamazingformula.com				01-15-08

BKNY
Starting Member

2 Posts

Posted - 2008-02-11 : 16:52:21

This error is resolved by putting a carriage return after the last line in the format file. I got this same issue when I copied the example from the OPENROWSET section on BOL. The sample does not have the carriage return.

BKNY
Starting Member

2 Posts

Posted - 2008-02-11 : 16:57:03

Just to be clear about my solution - Here is the format file from the example:

9.0
2
1 SQLCHAR 0 10 "\t" 1 ID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 40 "\r\n" 2 Description SQL_Latin1_General_CP1_CI_AS<<--Put missing carriage return here

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources