Adding a column during Excel import into SQL Server table

Question

I have a script that creates a table as well as a column for tracking the filename that is responsible for the import.

select *, 'file1.xls' as 'Filename'
into dbo.SQLServerTable 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\testing.xls;HDR=YES','SELECT * FROM [Sheet1$]')

I then need to perform additional imports which have different filenames. I don't believe this statement is syntactically correct and it is preventing the import.

INSERT INTO dbo.SQLServerTable 
Select *, 'File2.xlsx' as 'FileName' 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel      12.0;Database=D:\Convert\Converted\File2.xlsx;HDR=YES', 'SELECT * FROM [Worksheet$]')

The error I'm getting is "String or binary data would be truncated" — Geoff Dawdy
– Geoff Dawdy, Commented Dec 19, 2012 at 20:23
Do you know in advance which columns are comping from Excel file? — Comfortably Numb
– Comfortably Numb, Commented Dec 19, 2012 at 20:27
I believe what is happening - first SELECT INTO creates a table with columns of a certain VARCHAR length. When you import second file some data in that file exceed that length. You can try either CREATE TABLE in advance with VARCHAR(max) columns or try setting Registry option HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows to 0 So excel would guess column type based on entire column and not just first 8 rows — Comfortably Numb
– Comfortably Numb, Commented Dec 19, 2012 at 20:35
You always have 109 columns? Then you should really take the time to create ONE table with those 109 columns (with sizes large enough to stop the truncation error), plus an additional column containing the country name. Then you only ever have to worry about one table. — Nick.Mc
– Nick.Mc, Commented Dec 19, 2012 at 23:39

Schuere · Accepted Answer · 2013-01-08 13:38:54Z

1

I encountered this problem to, this because of default tables values, for string values this would be for example a varchar(50) where i needed a varchar(120)

My solution there was to place the excel into a csv file (standard save in excel) and then use the sql Import/export UI to import the files into a table. During this process you can see all the default values and then via the advanced tab change these values to your own preferences.

answered Jan 8, 2013 at 13:38

Schuere

1,64921 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Adding a column during Excel import into SQL Server table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related