August 19, 2005 at 1:05 pm
Greetings,
I have imported excel spreadsheets into a SQL Server database table many times, but I'm running into something I haven't seen before.
I receive data in Excel format from several sources. I then combine them into one excel spreadsheet (actually, one worksheet in one spreadsheet.)
The first column is Social Security number. The problem is that when I import the spreadsheet, many of the SSNs are converted to <NULL>. I've tried reformatting the column in the spreadsheet to where they are all formatted the same (at least the best I can tell they are the same) but that doesn't help any.
Any suggestions would be greatly appreciated!
Bob
August 19, 2005 at 1:15 pm
Long shot but does this help?
August 19, 2005 at 2:04 pm
Have you been able to work out a pattern - if you look back at the source data, do the fields that come through as nulls have anything in common?
When you talk about formatting, I presume that you mean more than just Format/Cells? This command changes only the way in which Excel displays data, not the data itself.
Regards, Phil
August 19, 2005 at 4:22 pm
Try the code below to use Excel as linked server or import the data to a temp table before moving it to the destination table. One of the above should solve the problem. Hope this helps.
/* Excel as a linked server */
/* Assuming we have an Excel file 'D:\testi\Myexcel.xls'
with following data in the first sheet:
id name
1 a
2 b
3 c
*/
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'D:\testi\Myexcel.xls',
NULL,
'Excel 5.0'
EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Sheet1$
CREATE TABLE test_excel
(id int,
name varchar(255))
GO
INSERT INTO test_excel
SELECT *
FROM ExcelSource...Sheet1$
SELECT *
FROM test_excel
/* Now define two ranges in Excel on the 2nd sheet as tables */
/* Select the range, Insert->Name->Define */
/* Note: sp_tables_ex does not recognize the defined tables */
/* We can still refer to the tables explicitly */
EXEC sp_tables_ex ExcelSource
EXEC sp_columns_ex ExcelSource
SELECT *
FROM ExcelSource...Table1
SELECT *
FROM ExcelSource...Table2
Kind regards,
Gift Peddie
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply