How to keep format when importing Excel into SQL.

Can anybody help me please?
When I import Excel file into SQL, a field that formated as 0000000000 
(custom), loosing leading zeros. I tried to change data type in SQL after 
importing. No luck.

I appretiate any help.
0
Utf
4/28/2010 1:46:01 AM
sqlserver.server 1327 articles. 0 followers. Follow

3 Replies
512 Views

Similar Articles

[PageSpeed] 32

See if using IMEX=1 helps:
http://pratchev.blogspot.com/2007/10/importing-excel-data-into-sql-server.html

-- 
Plamen Ratchev
http://www.SQLStudio.com
0
Plamen
4/28/2010 2:03:11 AM
Plamen,
I tried this, but got an error: "The OLE DB provider 
"Microsoft.Jet.OLEDB.4.0" has not been registered."

Thank you very much for looking into this.


"Plamen Ratchev" wrote:

> See if using IMEX=1 helps:
> http://pratchev.blogspot.com/2007/10/importing-excel-data-into-sql-server.html
> 
> -- 
> Plamen Ratchev
> http://www.SQLStudio.com
> .
> 
0
Utf
4/28/2010 3:00:01 AM
IP
Looks like you do not have Microsoft.Jet.OLEDB.4.0 on the machine, do you 
have 64 bit?


"IP" <IP@discussions.microsoft.com> wrote in message 
news:7FE54749-AA4A-4FC2-B483-2D6AB22A9800@microsoft.com...
> Plamen,
> I tried this, but got an error: "The OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" has not been registered."
>
> Thank you very much for looking into this.
>
>
> "Plamen Ratchev" wrote:
>
>> See if using IMEX=1 helps:
>> http://pratchev.blogspot.com/2007/10/importing-excel-data-into-sql-server.html
>>
>> -- 
>> Plamen Ratchev
>> http://www.SQLStudio.com
>> .
>> 


0
Uri
4/28/2010 5:47:30 AM
Reply:

Similar Artilces:

How to customize the Office / Excel 2007 Theme Colors
Hi NG, Any idea how to customize the Office / Excel 2007 Theme Colors - Color Graduations? When you define Theme Colors you can define the base color for color Graduations, BUT there is not way to find out (a) how the color graduations are calculated, and (b) how to customize the color graduations Thanks, Andreas ...

Excel and PDA's
I want to use a excel form that I created using Macros, but my PDA using Windows doesn't not recongnize them. Is there a way for the PDA to use the macros? Thank you, Dan Hi Dan As far as I know, no. Pocket Excel is emphasis Pocket. However, if you have a spare amount of time and money, you can create just anything for Windows Pda's with Visual Studio.NET. -- HTH. Best wishes Harald Followup to newsgroup only please. "Dan Young" <d1rmrc@sbcglobal.net> wrote in message news:3CBDF40E-530A-4DFC-8174-9207167AE90B@microsoft.com... > I want to use a excel form that ...

SQL Server Update Problems KB970892
Hopefully this will help someone. I am running XP Home. I also could not install the download many times (now I don't know how to get rid of that yellow shield on the bottom of the screen) but anyway: I checked back to see if the SQL Server 5 had indeed completely installed - it didn't and when trying to finish the install I found out that my RAM memory had to be increased (AGAIN) before the install could be completed. So off to see the Geek Squad to see about getting this done. Getting a mgs. of low virtual memory once is what led me find out this information. > ...

Re page numbers in excel
Is it possible to move where headers and footers are shown, like you can do in word. My example a report with landscape and portrait and i would be the page number to be at the side of the landscape document Thanks ...

IV00100 Sql Join stmt.
I have the following sql stmt which will pull in my last receipted cost per itemnmbr. I want to join this to my item master, iv00101 and pull in the standard cost and current cost with each line item. How could I join these 2. Any help will be appreciated. If there is a system report that already does this please let me know. select a.itemnmbr, a.docdate, a.trxloctn, a.unitcost, a.dex_row_id from iv30300 a inner join ( select itemnmbr, max(dex_row_id) as dex_row_id from iv30300 where doctype = 4 group by itemnmbr) b on a.dex_row_id = b.dex_row_id thanks -- Craig Craig, Now, that&...

SQL Trusted Data Source Error
I was wondering if anyone has experienced this problem. After sucessfully installing GP, I launched GP and when the log in screen comes up it asks for a UserID and password (SQL Server). I enter the user SA and password then I get the following error” You’re attempting to log in from a data source using a trusted connection. Update the SQL Server settting for this data source to disable trusted connections and try logging in again." How do you disable this trusted connection? Or fix this so I can start GP? Any help would be appreciated. Thanks. Open the control panel, go to ad...

Cannot see change in SQL DB
I am connected to a SQL DB View via Access. One of the columns has had its size increased from varchar 64 to varchar 128 but I don't see the new size even after relinking. WHAT DO I DO!? HELP! Something sticking in my head about need to re-create the view on the SQL Side as well...but I can't recall the specifics... Can you drop and re-create? -- Kevin Hill 3NF Consulting http://www.3nf-inc.com/NewsGroups.htm Real-world stuff I run across with SQL Server: http://kevin3nf.blogspot.com "RocketMan" <ImaChessNut@gmail.com> wrote in message news:1176146473.71419...

Is it possible to set an alarm in EXCEL?
Hello, does anyone know if it is possible to set an alarm in Excel. I' working with a lot of dates in Excel and I've got one column which i called "DATE OF NEXT ACTION" and at that date I'll need to call specific client. I know that I can filter the data on that column. But is there a way t set Excel so that something pops up on the screen? Or any other possibility that Excel checks the dates in that column an makes me aware if today's date is the same as the one in the column? If not, is there another software that can do that? Thanks so much for any help yo...

How can I graph the #of times a certain word is found in Excel?
For example I have a list of reasons of what brand of butter a group of customers choose to buy.... How can I take the number of times "I can't believe it's not butter" is listed as compared to "Brommel and Brown", etc. and graph them accordingly onto one graph so if I have 512 occurances where customers chose "I can't believe it's not butter" and 256 occurances where customers chose "Brommel and Brown" - then I would like the graph is twice as large for the "I can't believe it's not butter". Hi, Just put in an ...

Missing Leading zero when importing in excel
I have exported item list as a csv (as well as tab delimited) but when I import it to excel I loose leading zeros in my item lookup codes. Is there a way to avoid this, thx. Change the properties of that field to "Text" in Excel. Kinnard L. Kohler Business Machines Systems 6101 South Shackleford Road Little Rock, AR 72204-8606 (T) 501-375-8380 (F) 501-375-0043 (Cell) 501-412-5686 Email: kinnard@bmsar.com "MOI" wrote: > I have exported item list as a csv (as well as tab delimited) but when I > import it to excel I loose leading zeros in my item lookup codes. Is...

Problem of Truncation on Excel 2003
When I open a spreadsheet (saved on an earlier version of Excel) in Excel 2003, the "cell wrapped" cells lose most of the ending information, as if there were some sort of limit on the amount of data to be stored. The spreadsheet in question has links, which I do NOT want updated. I have been working with Excel for as many years as it has existed, therefore you may consider I am not making a mistake. We need this problem fixed ASAP. Additional information: Just discovered that the problem is in the handling of MERGED cells. Only the first cell of the merged group is kept ...

Small Business Edition and SQL
Can we use a separate SQL Server with the Small Business Edition to bypass the SQL database limitation? Unfortunately, the SBS of CRM only works when installed on SBS Premium and uses the SQL instance on the primary server. I think a lot of us would prefer that we could install it using a separate server with SQL on it instead... "Cindy Mikeworth" wrote: > Can we use a separate SQL Server with the Small Business Edition to bypass > the SQL database limitation? > > > We are installed on the SBS server, using seperate SQL (2000) server. No problems. &q...

where can i get a excel league table template?
for a league of 12 teams each team playing 11 home and 11 away games http://www.xldynamic.com/source/xld.LeagueTable.html -- HTH RP (remove nothere from the email address if mailing direct) "steve" <steve@discussions.microsoft.com> wrote in message news:E70B7EB1-F42D-4B54-9E9C-76F4394B34FC@microsoft.com... > for a league of 12 teams each team playing 11 home and 11 away games ...

Can't import OE6 account information into Outlook
I've got a whole load of mail in my OE6 box and am trying to migrate it to Outlook 2002. All the messages etc have come through, but the account information for all the email addresses I have is not being done. If I manually try to Import via Import -> Internet Mail & Account Settings, I get "No Internet Accounts were found to Import". Help appreciated. do you have more than one identity in OE? -- Diane Poremsky [MVP - Outlook] http://www.poremsky.com - http://www.cdolive.com Outlook Tips: http://www.outlook-tips.net/ Expert Zone http://www.microsoft.com/windowsxp/ex...

Help formating
I have cells which can accpet percentages and currency values my questions is when i enter a value <1 i want the cell to format as % if the value is >1 then i want it to be currency. can some one help me out i cant do this using condityional formating. any one have ideas ??? Regards, Alexandre Use a custom format instead: Format/Cell/Number/Custom [<1]0.00%;$#,##0.00 You don't say what should happen if the value is =1. I assumed currency format. In article <1122487381.944331.179000@g43g2000cwa.googlegroups.com>, "Alexandre Brisebois (www.pointnetsolutio...

Contact Email address format
Hi! I'm trying to programmatically access the my contacts from outlook, which I did using the following code: var outlookApplication = new ApplicationClass(); NameSpace mapiNamespace = outlookApplication.GetNamespace("MAPI"); MAPIFolder searchFolder = mapiNamespace.GetDefaultFolder(OlDefaultFolders.olFolderContacts); The problem is that any email address (Email1Address) retrieved which is connected to the same local Microsoft exchange server is retrieved in the following format: /o=First Organization/ou=Exchange A...

Excel help #5
Hi, Can anyone please help me. I was trying to enter the below no in excel. 0000081563 But when i enter it in the excel cel. it becomes 81563. how can i enter zero's infront of the number. Any help will be great. THanks -- kaushi Try formatting entry cell as "Text" before you enter the 0000... value. HTH Regards, Howard "Kaushi" <Kaushi@discussions.microsoft.com> wrote in message news:45786724-0795-4E0D-AA9C-7C9EFF8AD564@microsoft.com... > > Hi, > > Can anyone please help me. > I was trying to enter the below no in excel. > > 000008...

excel 2000 Program error
When try to work on excel sheet I get this error "EXCEL.exe has generated errors and will be closed by windows. you will need to restrat the program. An error log is being created" Thx Seenu ...

Adding Excel Spreadsheets to a website #2
Anybody? Bueller -- tommy2 ----------------------------------------------------------------------- tommy20's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=281 View this thread: http://www.excelforum.com/showthread.php?threadid=27745 ...

Calling a Public Sub inside Excel from Access
I have an Excel spreadsheet that I am opening from within Access: Dim xl As Excel.Application Dim wrk As Excel.Workbook Set xl = New Excel.Application Set wrk = xl.Workbooks.Open(Filename:="file.xls") I would like to call a routine within the Excel file after opening it (I want to set up a commandbar depending on how and where Excel is opening from my database application). However, I can't figure out where to put such a routine so that it is accessible. I tried putting a Public Sub into a module file, then calling it like: wrk.MySub "param" Howe...

Custom Format : Gb/Mb/kb/bytes : Need help debugging command
Hi Everyone, I'm trying to format a few series of bytes as something that is a little more legible, and truncating the number of decimal points. While the link 'Local Tips on Custom Formatting eg.' (http://www.exceltip.com/st/Formatting_numbers_according_to_criteria_in_Microsoft_Excel/71.html) has been a great aid in getting the general drift of how to write the custom format strings, it doesn't aid debugging something that errors. I have the following line: > [<1099511627776]((#,##0.000)/1099511627776)" > TB";[<1073741824]((#,##0.000)/1073741824)"...

Formatting Percentages Redux
Ages ago I asked a question about formatting percentages (specifically in TextBox controls on a UserForm) to display a plus or minus sign as appropriate - something that the 'FormatPercent' function doesn't support. I received a most helpful answer from either Jay or Doug - I've slept a few times since then and can't remember exactly who it was - describing a method for using the basic 'Format' function to meet my needs, and things have been working a treat ever since. Until now... Here's the situation. I need a method whereby percentage values...

Look at this important package
--shomfgfthuwqxtbw Content-Type: multipart/related; boundary="bublemlox"; type="multipart/alternative" --bublemlox Content-Type: multipart/alternative; boundary="sirkubqqlions" --sirkubqqlions Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Consumer this is the latest version of security update, the "October 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to help maintain the security of your computer from these...

NDR won't time out and keeps killing Outlook
Hi, I have a user who's Outlook has been crashing periodically for about a week. We think we've traced it back to an NDR he has been getting repeatedly for a week from the System Administrator, and I've been able to duplicate the crash on another system. The NDR appears to be in response to a recall request he made on a message to an outside company (not realizing this wouldn't work I guess). Each time the NDR comes into his mailbox, his Outlook shuts down. He doesn't even have to highlight it. There is nothing in the app or sys log on either the client or the ser...

Excel always in bold
When I do entries on the excel spreadsheet in Gill Sans its looks ok ie Gill Sans font size 10 normal but when I print it always comes out in bold even though the bold is not highlighted. any suggestions please? Check to see which Gill Sans font you chose. There are four choices two which have bold as part of the font. "Scarlett50" wrote: > When I do entries on the excel spreadsheet in Gill Sans its looks ok ie Gill > Sans font size 10 normal but when I print it always comes out in bold even > though the bold is not highlighted. any suggestions please? ...