October 27, 2004 at 8:57 am
Once a DTS has been set up between a spreadsheet is there any way that the columns in the spreadsheet can be changed without changing the DTS. Ideally I would like to ignore the column headings in the spreadsheet and just import the data from row two onwards.
I can't see any way of this being done as when the DTS is setup the transformations seem to need to remain the same as the transformations.
November 1, 2004 at 8:00 am
This was removed by the editor as SPAM
November 2, 2004 at 10:47 am
I have had enough strange problems importing from Excel, that I no longer do it. I save the Excel sheet out as a tab-delimited text file, and import from that.
November 3, 2004 at 3:02 am
The Excel ODBC driver which is used by DTS to suck the data in off the worksheet, relies on row 1 for headings, so changing the colums is difficult. It also guesses the datatype of the excel column so that can be problematic. I with the previous post, try not to use Excel. Dropping of leading zeros, decimal places rounding etc has had me up late many nights that i will skip excel as a import tool at all costs.
Can you not get at the data source the xls file came from.
Other wise you could wite vbscript to open the xls file and read it in cell by cell and update a batch recordset. I did that once and it was sufficiently quick for 2000 rows, 12 columns worksheet. I no longer have the code else i would post it.
Regards,
Goldfinger
Is the Fuel yummier than the Fisher?
November 3, 2004 at 3:35 am
If you use a SQL task rather than the Excel connection it is possible, plus you eradicate most of the problems that come with the Excel connection. One major problem this this connection is that is samples a set of data from the spreadsheet and then forces each column as a single data set, so for example, if column a has the first 10 lines populated with integers and later there are some decimals in the same column when using this connection it will fail as it will try and read the whole column as integers, the same happens mixing strings and numeric. By using SQL syntax you get past this “intelligent improvement” annoyance
Here’s an axample -
INSERT INTO Your_Imput_Table (AccNo1, AccNama1, AccNamb1) – always specify the columns
SELECT F1 AS AccNo1
, F2 AS AccNama1
, F3 AS AccNamb1
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0; HDR=NO; IMEX=1;Database= YourSpreadSheetLocation‘
, 'SELECT * FROM [TourSpreadsheetName$]' – use select * to return headerless rows
)
Hope this helps.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply