December 30, 2002 at 10:12 pm
Hi,
I am trying to import data from Excel to a Table, but iThu, 05-Dec-02 10:03 AM imports all the rows of sheet including Blanks, i sthere any way to import only the rows which contains data?
Secondly i am facing an error that buffer overflow althiugh i have set the column datatype varchar(4000) and excel cell contains max 255 chars, but it gives an error, is it a usual error?
waiting for reply
Kind Regards,
Affan
December 31, 2002 at 2:38 am
While using DTS, select copy views and tables from source database, then select excel sheet to import and click TRANSFORM for column mappings. You can simply ingore source and destination colums which are not required.
It would be even better if you first import excel data to access db and from access to SQL server, you would have better control over data import!!
December 31, 2002 at 4:32 am
As for the 255 when the transformation is built the default value for the new column is 255 nvarchar on an import of excel. On the destination table you can look and alter the length of the field and datatype.
If the sheet name is the same every time you should be able to use a Select statment on the source tab to reference the data and not show the blank lines.
For example my first row has the column name COl1 and the sheet is Sheet1$ I do
SELECT Col1 FROM [Sheet1$] WHERE Col1 <> ''
and the blanks will not show.
December 31, 2002 at 4:36 am
What if my sheet name varies??
Secondly if have changed the length thru transform to varchar 4000 but it still giver buffer overflow error
Regards,
Affan
December 31, 2002 at 5:34 am
Then you either do not have it large enough for your largest data from the Excel file or you have the wrong column. If the sheet name varies then I suggest use ActiveScripting with VBScript to open the source and do the processing so you can read the sheet names and build a dynamic SQL string similar to the one I demonstrated, specific to the sheet in question.
December 31, 2002 at 4:36 pm
I bet the excel file has a column formatted across the whole sheet as a header that's causing the overflow. You might check that, as Sql only actually samples a few rows of the sheet to get it's format. It doesn't actually check the largest length in the file. I agree with Antares686 on the VB scripting direction, as well.
December 31, 2002 at 9:51 pm
But when i set the length to 4000 through tranformation, shouldn't it take the column length as varchar 4000???
Kind Regards,
Affan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply