July 25, 2008 at 7:28 am
Hi there,
I am writing a SP that imports data from an Excel file to a SQL2005 table:
ALTER PROCEDURE [dbo].[ImportExcelFile]
@SourceVARCHAR(1000),
@SourceSheetVARCHAR (100),
@blnHasColumnNamesTINYINT
AS
DECLARE @hdr VARCHAR(3)
SET @hdr = CASE WHEN @blnHasColumnNames=1 THEN 'YES' ELSE 'NO' END
SET @SourceSheet = '[' + LTRIM(RTRIM(@SourceSheet)) + '$]'
EXEC('SELECT * INTO [IMPORTTABLE] FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', ''Excel 8.0;HDR='+@hdr+';Database=' + @Source + ''', ' + @SourceSheet + ')')
It does work, BUT:
The Excelsheet has 30 (filled) rows; however 321 rows are imported (the bottom 290 or so filled with NULLs). Why is this?
I did write a routine to delete those empty rows.
More troubles I am having with telephonenumbers. In the Excelsheet they all start with a leading zero. For some reason this column is imported as a float, and the leading zero is omitted. If I predefine this column as a char field, the phonenumbers are imported with exponential signs!! For example: 1.243516 E8.
Even worse, if some telephone numbers are entered with a dash, like '035-55436625' it doesn't get exported at all, instead a 0 (zero) is put in my table!
What I want is SQLServer to stop trying to be smart and simply copy the data from the Excel sheet to a table as charfields. How can I do this?
Please note that DTS or so is *not* an option. I am writing this for customers, who each time will have a different Excel sheet that needs to be imported. These customers have no direct access to the DTS on the server
Thanks,
Ray
July 25, 2008 at 7:37 am
Excel does a lot of funny stuff to data-types and some of them do not import easily into SQL, you can try and set the datatypes in the spreadsheet to generic character datatypes to avoid this.
An easier solution would be to import the file as a raw .csv file and leave all the dataypes out.
Also it looks like excel thinks that there is data in the extra rows that are imported, check the spreadsheet to enusre that these rows are completly blank.
July 25, 2008 at 8:16 am
Hi,
I did change the column with telephone numbers to 'Special'|'Telephonenumbers' in Excel. It still gets imported as a float in SQL2005, with leading zeroes omitted and phonenumbers with a dash set to zero. :((((
July 25, 2008 at 8:21 am
Try setting them to TEXT and then doing modify the datatypes once you have imported it
July 25, 2008 at 8:32 am
I already tried TEXT (in Excel) as well. My big friend Excel tells me that all numbers will be treated as text, and be written as they were entered. Great, thanks! But even then.... the leading zeroes get omitted! Excel says it won't touch the data but still does it!
And stranger: even when I set the phonenumbers column to TEXT in Excel, it STILL GETS IMPORTED AS FLOAT in sql2005 !!???!! I am sure I am looking right (changed filenames, tablenames etc.), so it's not an old unupdated table I keep looking at. The file IS generated by my SP from an Excel file WITH A TEXT phonenumber column and it IS imported by sql as a float.
I really don't know what to do now.....
July 25, 2008 at 8:55 am
I usually avoid excel imports if possible for these very reasons, are you able to save the spreadsheet as CSV and import from there. would save a lot of headaches
July 26, 2008 at 3:59 am
I can't avoid using Excel sheets to import data - all my (potential) customers work this way.
Anyway I solved the problem (I added the solution to this forum but somehow the post has gone....????): you have to add "IMEX=1" to the querystring.
Thanks to everyone,
Raymond
July 26, 2008 at 5:40 am
R. Van Laake
The Excelsheet has 30 (filled) rows; however 321 rows are imported (the bottom 290 or so filled with NULLs). Why is this?
A characteristic of Excel:
When you save a workbook, Microsoft Office Excel stores only the part of each worksheet that contains data or formatting. Empty cells may contain formatting that causes the last cell in a row or column to fall outside of the range of cells that contains data. This causes the file size of the workbook to be larger than necessary and may result in more printed pages when you print the worksheet or workbook.
To avoid these issues, you can locate the last cell that contains data or formatting on a worksheet, and then reset that last cell by clearing all of the formatting that may be applied in empty rows or columns between the data and the last cell.
Let's say you enter any text or number in cell A10 and then enter a valid date in cell A20 of a new Worksheet. Now delete the date in cell A20 and save. This is because entering a date in A20 has caused Excel to automatically format the cell from "General" to a Date format.
Also refer to:
http://support.microsoft.com/?id=244435
With the above Excel treats those "empty" cells as not being empty and this could be the source of one of your problems. Take a Excel sheet that has given you blank data and manually clear the cells as directed in the support article and see if this solves one of your problems.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply