Hi,
I am using the import wizard to import an excel file into my database. It seems that the field in excel is number. It has however, leading zeros. When I import to sql it creates a table on the fly and the field is float. This imports all memberids with leading zeros as NULL.
I can't fix it and it's driving me crazy!!!
Any suggestions please?
Thanks
Is a memberid numeric? If so, it won't be imported as NULL. Also, note that the wizard allows you to change the DDL that creates the table to what you want.
See if you can control how the table is created. Microsoft uses the first few rows of data to determine the datatype.
Does the Excel cell with leading zeros have a leading single quote?
Hi,
No it doesn't have single quote.
Normally, if Excel interprets a column as a number, it will not keep the leading zeros. So I suspect it is a text column. If that is the case, by default, when you import using import/export wizard, it should have kept the leading zeros, and created a varchar/nvachar column in the database. So I suspect what djj55 is suggesting is the reason.
You can see and change the data type of the table that gets created in one of the import/export wizard dialogs. see screenshot below.
When I preview the column mappings it says float. Tried to convert it to integer and same problem. Trying to change it to nvarchar and it won't let me run the package. Says that I have to save the package.
I tried changing it to csv. When I import the file to sql it adds to the the numbers with leading zero a strange character þ
þ006942320þ.
Have you considered using OPENROWSET?
select *
into [dbo].[tmpData1]
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;Database=C:\put full path to your excel file her\put name of your excel file here .xlsm;HDR=YES;', this bit imports any headers ie column headings
'SELECT * FROM [Sheet1$]' ) as tmpData1
end
You might find it useful. It will create a new table, in this case tmpData1. From there it is quite straight forward to then create a procedure that will import it into the table of your choice. If tmpData1 exists then OPENROWSET will fail and if the excel file is open for reading or writing then again it will fail. After using OPENROWSET if the leading zeros are still there you could LTRIM them.
Thanks for the reply. Tried OPENROWSET and i get this error:
OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
I am using sql server 2008r and excel 97-2003
This is likely to be caused because you need to configure advanced options in sql. To do this run the following procedure
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
I get the following messages when I run the query:
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
correct. standard messages that the changes worked
Ok. When i run the query to import the worksheet I still get the message:
Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
Could this be it?
Yep this is it. Checked my excel and sql versions now 
This is a problem? How can I delete the leading zeros.
In excel when I stand on the column it says that it's numeric...so it doesn't explain why I see leading zeros.
I received the excel from someone and that memberid column was created with a formula.
I tried copying the values to another worksheet but it imports it with strange characters before and after the numbers with zeros.
Have a look at this article https://support.office.com/en-za/article/Remove-spaces-and-nonprinting-characters-from-text-023f3a08-3d56-49e4-bf0c-fe5303222c9d
I suggest that you make a copy of your spreadsheet first and try cleaning the cells on the backup rather than the original.
Clean doesn't help. The problem numbers have strange characters in front of them.
Uploaded image.
http://postimg.org/image/dybytecgx/
So you have bad data. I think that's what @squealer meant by "clean". You need to locate and fix the bad data, by hand.
The strange thing is that I only see the strange characters when I copy it to SQL query analyser.
Looking again at the image you posted it seems you have a line break char in the cells.
Have a look at this article https://www.ablebits.com/office-addins-blog/2013/12/03/remove-carriage-returns-excel/#delete-line-breaks-excel-formula
It suggests that this formula =SUBSTITUTE(SUBSTITUTE(B2,CHAR(13),""),CHAR(10),"")
should do what you want. But in case it does not the article provides more information and help.
hi,
The formula doesn't work either.
I added an X in front of the data. It imports to sql and the value isn't null.
However, it still adds the strange character after the x and after the last number.
I tried replace but it doesn't work only if i hard code the value.
I can't even paste the character here as it pastes it as empty. Only in query analyser when I copy the number I see that character.