Need 2nd row of excel
-
I have an excel spreadsheet that I need to import and use for table update. It will be set up to run nightly. It has regular column heading(not a problem) but it also has a report heading in row 1. How do I tell it to start processing at row 2? I know you can set First row but then you HAVE to set Last row) and I never know how many rows the spreadsheet will have. The spreadsheet comes from a PeopleSoft query that forces the report heading so I can't make them change. Need help!
Changinagain
-
You can test on a key column of Excel the validity of the row
nonequal to bank thus not of heading
-
If you know the data in a column that identifies the row as a header, then you can use skip row.
However, you have to sepcify all DTSSource and DTSDestination details in the same script:
Function Main()
If DTSSource("Col001") = "HEADERTEXT" Then
Main = DTSTransformStat_SkipRow
Else
DTSDestination("Col001") = DTSSource("Col001")
DTSDestination("Col002") = DTSSource("Col002")
Main = DTSTransformStat_OK
End If
End Function
Not very nice and it does mean that each row is procesed one at a time, so it will be slow as well.
If the files are large and processing is to be kept at a minimum, then you will have to try something more complicated.
Many ways of doing this:
a)Open the excel file and count the number of rows using excel object. Put this into global variable and set as the last row to be imported.
b) Open ecel using excel object and delete row 2, then sabe it again before importing.
c_....etc....
------------------------------
The Users are always right - when I'm not wrong! -
Set Last Row to a very high number eg 999,999,999
Far away is close at hand in the images of elsewhere.
Anon. -
thanks all, I did set the last row as a high number. In checking with the user I found this file would probably never have more than 5000 rows. Great info tho, putting it under my hat for another time.
Changinagain
-
Watch out setting the row number to a large number as dts will process theses rows.
IE - Will take longer and fill your database up with empty rows.
May also cause errors if any columns are non nullable. You are also taking a chance at missing records if you set too low. The users always say one thing, but in reality, they can be wrong.
------------------------------
The Users are always right - when I'm not wrong! -
Watch out setting the row number to a large number as dts will process theses rows Not for me it doesn't
I only get the number of rows upto the last row with data in the spreadsheet irrespective of the Last Row value
Far away is close at hand in the images of elsewhere.
Anon. -
Apologies, David. Should have clarified a bit better.
Excel has an internal pointer that dts picks up on as the end of the spreadsheet. I forget what it is called.
But if say one day your spreadsheet has 5000 records and the next day, the same spreadsheet has only 3000 records. If the cells were deleted by highlighting the cells and then pressing the delete button, the pointer still stays at 5,000 and so dts will process these blank rows.
If deleted properly by highlighting rows and selecting edit/delete, then pointer shoots up to the last but one row deleted and dts works fine.
This has happened to me on many occassions one such instance where the pointer was left in 50,000 range and it took an age to import only 1,000 actual rows.
------------------------------
The Users are always right - when I'm not wrong! -
Yeah that has happened to me, sometimes either way works sometimes not
May be MS should call them 'Sticky Cells'
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply