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
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply