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.
| Author |
Topic |
|
sinclair
Starting Member
7 Posts |
 Posted - 2009-09-21 : 11:59:57
|
| I am using openrowset to import a text file but I have a problem. I need to specify the last row number. If I use:SELECT * FROM OPENROWSET( BULK 'C:\temp\test2.txt', FORMATFILE = 'C:\temp\testFormat.Fmt',lastrow=6207) AS a;This works without errors.How can I find the lastrow number dynamically or not need to use it?If I remove the "lastrow=6207" it displays all the data but gives an error:Bulk load: An unexpected end of file was encountered in the data file.Msg 7399, Level 16, State 1, Line 1The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Msg 7330, Level 16, State 2, Line 1Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". |
|
|
sinclair
Starting Member
7 Posts |
Posted - 2009-09-28 : 07:58:12
|
| Found a work-around. Instead of using OPENROWSET I used BULK INSERT into a temporary table. Another advantage was the temp table acted as the FORMATFILE so no need for another file. In-case any-one ever needs it here is code (the "extra" columns are for columns in text file that that won't be inserted in real table)CREATE TABLE #a ([extra1] varchar(50) null, [IDField] [varchar](10) NOT NULL, [title] [varchar](10) NULL, [surname] [varchar](50) NOT NULL, [forename] [varchar](20) NULL, [extra2] varchar(100) null, [extra3] varchar(100) null, [dept] [varchar](100) NULL, [extra4] varchar(100) null, [extra5] varchar(max) null)BULK INSERT #a FROM 'C:\temp\test2.txt' WITH (Fieldterminator ='\t', ROWTERMINATOR = '\n' ) select * from #a drop table #a"By three methods we may learn wisdom: First, by reflection, which is noblest; Second, by imitation, which is easiest; and third by experience, which is the bitterest."Confucius |
 |
|
|
|
|
|
|
|
openrowset end row