I need to load various excel file into SQL server. For every excel file
i have mapping table mapping excel column to SQL column. I am currently
dynamically generating insert statement from this mapping table and
then use to load records into sql server. For taking data from excel i
am using distributed queries. So basically i am dynamically preparing
statement like this

SELECT (following columns...) into Table1 FROM
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\MyExcelFile.xls;Extended Properties=Excel
8.0')...Data$

Only problem with this is that when it fails with following error
"String or binary data would be truncated.", I don't know which column
data is the culprit. And there are about 150 columns in the excel
sheet. Is there a way to find the culprit column? Or i have to try out
every column one by one by hit and trial.

Is it a good way of doing dynamic column mapping ?

Other way of doing dynamic column mapping would have been to write
script for data pump task. Is that a better way? I couldn't figure out
code for doing that.

Please help.

Thanks
pm

pm,

Get the list of all character or binary columns first. From here there are
two ways out:

1) subsequently exclude columns from the statement to see when it stops
breaking;
2) in Excel calculate maximum length for each such column and compare it
with the column definition in SQL server.

Ilya

"pm" <mittal.pradeep (AT) gmail (DOT) com> wrote