Numeric Strings Will Not Import From Excel
Sep 28, 2007
Hello,
I have a table in SQL Server 2005 that has an NVarchar(50) column called AcctCode. I am trying to populate this column from an Excel source. I am doing this from the Import/Export Wizrds in Management Studio. When I attempt the import from the Excel file, all the the AcctCode data that is alpha numeric or just alpha will import, but the all numeric data comes in as NULL.
If I make AcctCode NOT NULL, I get an integrity contraint error from the wizard. The rest of the records import. I've tried to make the column in the Excel file type 'text', and have tried it with no formatting on this column.
Why won't the all numeric Acctcodes import?
Thank you for your help!
cdun2
View 3 Replies
ADVERTISEMENT
Mar 27, 2007
Apologies if this has been done before, but I couldn't find a completed example. If anyone has time, I'd love to see some improvements...
Where's fribble when you need him?
/*
function:
numeric_order
arguments:
@numeric_string - a string of mixed alpha and numeric values
@max_digits- the maximum length of digits to compare
description:
Function numeric_order creates an orderable string based on the "numeric" value of @numeric_string
which can be ordered alphabetically.
Ideally the strings should really be broken up into constituent parts and ordered properly,
but occasionally you come across data where it's just not worth the while.
eg
select title from regulations order by title
returns:
Regulation 1(a) section 3
Regulation 11 section 100(b)
Regulation 11 section 2(c)(iii)
Regulation 2 section 1
Regulation 21 section 3 (b)
whereas
select title from regulations order by dbo.numeric_order(title,10)
returns:
Regulation 1(a) section 3
Regulation 2 section 1
Regulation 11 section 2(c)(iii)
Regulation 11 section 100(b)
Regulation 21 section 3 (b)
which is the order most users would expect.
NOTE: because the original strings are mixed, the user may include alphas between digits which are to be
sorted alphabetically, which means the string must keep all alpha parts of the original string as is.
expected output:
select dbo.numeric_order('Part 1(b) subsection 1',4)
returns
Part 0001(b) subsection 0001
test data:
use test
--go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[regulations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[regulations]
--go
create table regulations (id int identity(1,1), title varchar(200))
--go
insert into regulations (title) select 'Regulation 1(a) section 3'
insert into regulations (title) select 'Regulation 11 section 100(b)'
insert into regulations (title) select 'Regulation 11 section 2(c)(iii)'
insert into regulations (title) select 'Regulation 2 section 1'
insert into regulations (title) select 'Regulation 21 section 3 (b)'
--go
select title as [Incorrectly Ordered] from regulations order by title
--go
select title as [Correctly Ordered] from regulations order by dbo.numeric_order(title, 10)
--go
drop table [dbo].[regulations]
--go
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[numeric_order]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[numeric_order]
go
create function [dbo].[numeric_order](
@numeric_string as varchar(1000),
@max_digits as int)
returns varchar(8000) as
begin
declare @return varchar(8000)
declare @part varchar(1000)
declare @digit_position int
declare @rest varchar(1000)
declare @non_digit_position int
declare @numeric_term varchar(1000)
declare @after varchar(1000)
declare @between varchar(1000)
declare @first_time int
declare @digits varchar(100)
--create a string of zeros equal in length to max number of digits of enclosed numeric values
set @digits = replace(space(@max_digits),' ','0')
--handle length issues
--worst case scenario is a number every second character multiplied by padlen < 8000
--which would potentially add len(@numeric_string)/2 * padlen characters - so subtract this from the string
set @part = left(@numeric_string, ((len(@numeric_string)/2) * @max_digits))
--starting values
set @non_digit_position = 0
set @first_time = 1
set @return = ''
--loop while not at end of string
while ((@non_digit_position > 0 or @first_time > 0) and (len(@part) > 0))
begin
--if there are digits in the string
set @digit_position = patindex('%[0-9]%', @part)
if @digit_position > 0
begin
--get the part of the string after the first digit
set @rest = substring(@part, patindex('%[0-9]%', @part) + 1, len(@part)-patindex('%[0-9]%', @part) + 1)
set @non_digit_position = patindex('%[^0-9]%',@rest)
--extract the string of digits
set @numeric_term = case
when @non_digit_position > 0 then substring(@part, @digit_position, @non_digit_position)
else substring(@part, @digit_position, len(@part) - @digit_position + 1)
end
--keep track of the rest of the string after and between the digits
set @after = substring(@part, @digit_position + len(@numeric_term), len(@part) - @digit_position + @non_digit_position)
set @between = '' + substring(@part, 1, @digit_position - 1)
--build return string
set @return = @return + @between + right(@digits + @numeric_term, @max_digits)
end
else
begin
--no more digits, just add back the rest of the original string
set @return = @return + @part
set @after = ''
end
--iterate
set @first_time = 0
set @part = @after
end
return @return
end
go
--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"
View 9 Replies
View Related
Jul 29, 2015
I am trying to import an xlsx spreadsheet into a sql 2008 r2 database using the SSMS Import Wizard. When pointed to the spreadsheet ("choose a data source") the Import Wizard returns this error:
"The operation could not be completed" The Microsoft ACE.OLEDB.12.0 provider is not registered on the local machine (System.Data)
How can I address that issue? (e.g. Where is this provider and how do I install it?)
View 2 Replies
View Related
Jul 20, 2006
I have the Excel Connection Manager and Source to read the contents from an Excel file. For some reason couple of numeric fields from the Excel worksheet are brought over as nulls even though they have a value of 300 and 150. I am not sure why this is happening. I looked into the format of the fields and they are set to General in Excel, I tried setting them to numeric and that did not help.
All the other content from the excel file is coming thru except for the 2 numeric fields.
I tried to bring the contents from the excel source to a text file in csv format and for some reason the 2 numeric fields came out as blank.
Any inputs on getting this addressed will be much appreciated.
Thanks,
Manisha
View 5 Replies
View Related
Jul 20, 2005
I am having a problem importing an Excel spreadsheet. I have a column inan Excel sheet with alphanumeric text and some of the cells are numeric.Some of the cells contain numbers like 12345.6 and when DTS is doneimporting it into a field that is nvarchar the results are"12345.600000000001". I have tried:1. Changing the format of the Excel column to text2. Using the formula =text(a1,0) which only truncates the .63. Using the formula =t(a1) which will remove some numeric representations4. Exporting the sheet to CSV or TXT first which will not enclose the cellcontents with ""5. Beating the computer with a nine ironNone of these options work. Any idea anyone?Don VonderBurg
View 3 Replies
View Related
Jun 14, 2006
l've the following situation,
l've some excel files controlled by Vendor which changing frequently. The only thing does not change is the header name of each column.
So my question is, is there any way to create a new table based on the excel file selected including the column name in SSIS? So that l can use the data reader as source to select those columns l am interested on and start the integration.
Thanks.
Regards,
Yong Boon, Lim
p/s : The excel header is at the row 7.
View 3 Replies
View Related
Jul 6, 2015
While importing data from Excel source , some column is getting null value even though excel column has value.To Resolve the issue we tried with
HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftOffice14.0Access Connectivity EngineEnginesExcel
1.Change the Value of the Row TypeGuessRows from 8 (Default value) to 0 and ImportMixedType = text
• xls
HKEY_LOCAL_MACHINESOFTWAREMicrosoftJet4.0EnginesExcel
1.Change the Value of the Row TypeGuessRows from 8 (Default value) to 0 and ImportMixedType = text
the connection string of the excel
UPPER(REVERSE(SUBSTRING( REVERSE(@[User::VarInputExcelFile]), 1, 5) ) ) == ".XLSX" ? "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + @[User::VarInputExcelFile] + ";Extended Properties="Excel 12.0;HDR=Yes;IMEX=1";":"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + @[User::VarInputExcelFile] + ";Extended Properties="EXCEL 8.0;HDR=Yes;IMEX=1";"
by doing the above setting also , the column is coming as null from excel source even though there is data in excel.
View 2 Replies
View Related
Sep 4, 2015
I am developing a SSIS package with VS2013 to send data from SQL Server 2014 to an Excel Destination. But in the SSIS package, from the excel destination advanced editor, when I set the format of the excel destination external columns to double precision float DT_R8, it is returned to DT_WSTR automatically.Due to that, data sent to Excel are not processed as numeric but as text and formatted as such. I need the column to be created as numeric.
View 7 Replies
View Related
Dec 13, 2007
Hi All,
Xl file containts column A, B, C and D
A B C D
1 L1 100 200
1A L2 250 350
1B L3 150 887
2 L4 100 200
2A L5 250 350
3 L6 100 200
3A L7 250 350
3B L8 150 887
XL Source i have write a query Select f1,f2,f3,f4 from [sheet1$a18]
In preview i am getting the following records :
A B C D
Null L1 100 200
1A L2 250 350
1B L3 150 887
Null L4 100 200
2A L5 250 350
Null L6 100 200
3A L7 250 350
3B L8 150 887
The issue is here why the XL source returning Null value against the numeric value pls. somebody help me to sort out this issue.
Thanks in advance,
Syed
View 1 Replies
View Related
Nov 3, 2006
Hi,
I have found a problem with my SSIS package. The package takes a Excel File using the Excel Source/Excel connection manager objects. There's a Data Conversion Object which sets all the fields to be DT_STR and then I import the data into a Holding table using a OLEDB Destination Object.
My Excel file contains a list of Code/Description pairs. The problem I have is that I can have data such as the following
CODE
A101
A102
A103
12
1.2i
What seems to be happening is that when the data is imported into the holding table and the first rows are Alphanumeric then the fields where there's a code such as 12 get converted to NULL for some reason. If all the codes are the same type (either Alphanumeric or numericit doesn't matter, as long as they are all the same) the import works perfectly well.
I need to be able to import all of these codes without SSIS converting some to NULL. Has anyone come across this problem before? If so, is there something I am doing wrong or a workaround?
Many thanks in advance
Rob
View 3 Replies
View Related
Oct 26, 2006
i've been reading some problems with excel source data being force as
numeric type when there are string/numeric type in the data, but adding
IMEX=1 to the extended properties will fix this problem...this is true
but not in my case...
say my excel file have about 40 rows..if row 1-39 in column A are all
NULL and row 40 has a string in it, the string in row 40 will not be
converted and the excel source is forcing this column A data type to be
numeric..having IMEX=1 in there does not work..however..if i add a
string anywhere in row 1-8 in column A, the the string in row 40 will
be converted because the external data type now is a string..
anyone know how to solve this problem?
View 3 Replies
View Related
Jun 28, 2007
I have a ssis package created in VS2005 that transforms a SQL query result into an excel spreadsheet. The DataFlow uses a DataReaderSource to query the data and an ExcelDestination to transform the data. The problem is that in the resulting spreadsheet any columns containing string data have a single quote inserted in front of the data. So a string like [My data] becomes ['My data]. When I preview it in the Excel Destination Editor it looks right (without the single quote). What is happening here, is it a bug or does something need to be configured. I checked all the properties that I could find. This is working nice, if I could only overcome this problem.
View 1 Replies
View Related
Jun 7, 2007
Anyone know why my xls does not have number formats when exporting from a report with a matrix?
I keep having to do 'paste special multiply by 1' on the whole thing.
Ridiculous!
View 1 Replies
View Related
Mar 4, 2008
File Structure:
--**************************************************************************************************************************
USE [Test]
GO
/****** Object: Table [dbo].[tbl_Test] Script Date: 03/04/2008 09:51:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Test](
[TestID] [int] IDENTITY(1,1) NOT NULL,
[TestValue] [numeric](18, 2) NOT NULL CONSTRAINT [DF_tbl_Test_TestValue] DEFAULT ((0)),
CONSTRAINT [PK_tbl_Test] PRIMARY KEY CLUSTERED
(
[TestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
--**************************************************************************************************************************
Stored Procedure:
--**************************************************************************************************************************
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[proc_Ins_Test] Script Date: 03/04/2008 09:52:23 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
/*
Name: proc_Ins_Test
*/
ALTER PROCEDURE [dbo].[proc_Ins_Test]
(
@TestValue numeric(18,2)=0
)
AS
BEGIN TRAN
BEGIN
INSERT INTO tbl_Test (TestValue) VALUES (@TestValue)
END
COMMIT TRAN
SELECT MAX(TestID) AS MaxValue FROM tbl_Test
RETURN 1
--**************************************************************************************************************************
(I have it returning a value through SELECT to simulate returning a key value from the newly inserted record. I also use the RETURN value to fetch any error codes.)
'***************************************************************************************************************************
Public Sub PSInsertTest(cnConn As ADODB.Connection)
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim stProcName As String 'Stored Procedure name
Set rst = New ADODB.Recordset
Set cmd = New ADODB.Command
'Defines the stored procedure commands
stProcName = "dbo.proc_Ins_Test" 'Define name of Stored Procedure to execute.
cmd.CommandType = adCmdStoredProc 'Define the ADODB command
cmd.ActiveConnection = cnConn 'Set the command connection string
cmd.CommandText = stProcName 'Define Stored Procedure to run
'Append Parameters
With cmd
.Parameters.Append .CreateParameter("@return_value", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@TestValue", adNumeric, adParamInput)
.Parameters.Item("@TestValue").NumericScale = 2
.Parameters.Item("@TestValue").Precision = 18
.Parameters.Item("@TestValue").Value = 123.45
End With
'Execute stored procedure and return to a recordset
Set rst = cmd.Execute
If Not rst.EOF Then
rst.MoveFirst
MsgBox (rst.Fields("MaxValue").Value)
Else
MsgBox ("No Value")
End If
'Close database connection and clean up
If CBool(rst.State And adStateOpen) = True Then rst.Close
Set rst = Nothing
End Sub
'***************************************************************************************************************************
It goes ahead and inserts the record, but gets an error when I try to retrieve the "SELECT"ed value. I get the same results with the following statement:
rst.Open cmd, , adOpenStatic, adLockReadOnly
The error is: Operation is not allowed when the object is closed
If I use the following command, I get the error below:
rst.Open cmd.Execute
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
Any insights would be helpful
Bob
View 19 Replies
View Related
May 8, 2009
I'm using SSIS 2005 Enterprise edition, I'm creating a package that reads an excel (xls) file using the "excel source" component, and it dumps the data into an OLEDB destination (a sql server). When I drag the excel source component and create the excel connection to my file the component automatically reads the columns and their datatypes.
The problem is that I have a column which has numeric data and the package uploads as NULL every number that starts with a zero. (note: in excel this column is formatted as "text", despite it has only numbers, because it's the only way excel maintains the left sided zeros).
So I checked the data types by right clicking the excel source component -> show advanced editor and my surprise is that this column's data type is detected as double-precision float, and it doesn't let me change it. URL... but it only works when the first row of data has a number beginning with zero on this column. How to get the data imported correctly?
View 15 Replies
View Related
Jan 24, 2008
Hi
I want to import excel in SSIS via sql command and change the data types. can anyone guide me on this or give me some sample command on how to change datatypes explicitly.
View 1 Replies
View Related
Jul 21, 2015
I'm importing a multi tab spreadsheet using Import wizard, which I understand to use the same internals as SSIS. The total number of columns in the spread sheet will be over 500. The import wizard defaults everything to varchar 255. I understand there is an XML file I can manipulate to change this and they are located
C:Program FilesMicrosoft SQL Server100DTSMappingFiles
Assuming one of these will control Excel defaults, which one is it? None of the names lend themselves to the Excel as a source. SqlClientToMSSql10?
View 6 Replies
View Related
Feb 19, 2007
I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.
I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.
This is the basic logic goes soemthing like this:
select case new_accountant = 1 then 'acct/' end +
case new_advisor = 1 then 'adv/' end +
case new_attorney = 1 then 'atty/' end as String
from new_database
The output would be
Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/
So far, nothing I have tried has worked.
Any ideas?
View 2 Replies
View Related
Jun 10, 2014
when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric
declare @a numeric(16,4)
set @a=99362600999900.0000
The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.
View 2 Replies
View Related
Mar 21, 2006
Guys
I'm getting the above when trying to populate a variable. The values in question are :
@N = 21
@SumXY = -1303765191530058.2251000000
@SumXSumY = -5338556963168643.7875000000
When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right
When I try the following ie to populate a variable with that value I get the error -
SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)
Any ideas ??
View 6 Replies
View Related
Apr 11, 2003
Hi,
I am importing data from Excel to SQL Server. My Excel files are .html file format. Using ActiveX Script I am saving that file to xls file format as follows
Set myXL = CreateObject("Excel.Application")
Set myXLWorkBook = myXL.Workbooks.Open(filename)
Set myXLWorkSheet = myXL.ActiveSheet
myXLWorkBook.SaveAs filename, 43 '43-xlExcel9795
Set oPkg = DTSGlobalVariables.Parent
Set oConn = oPkg.Connections("Excel")
This script works fine but it prompts me that the file already exists do you want to replace it. How can I avoid this prompt
Secondly this file contains data like that
Col1 Col9 Col10
Row-1 Report Name
Row-2 From Date
Row-3 To Date
Location Count Amount
9999 1 100.00
9876 10 20.40
Where Row are the excel rows
The TransformData Task thinks that the Col 1 is of nvarchar(255) but after the tranfer all values in Col1 is NULL. How can I convert a nvarchar to Char or varchar
Here is ActiveX Script for Transformation
Function Main()
Dim Result
DTSDestination("Amount") = DTSSource("F10")
DTSDestination("Count = DTSSource("F9")
DTSDestination("StoreNumber") = DTSSource("ReportName")
Result = DTSTransformStat_OK
Main = Result
End Function
:)
View 6 Replies
View Related
Feb 10, 2006
I'm needing to import a .csv file into a SQL database. This needs to be done through automation so I need the DOS commands, if any. Or if someone knows a better way, feel free to enlighten me.
I've tried looking in books online for SQL server but haven't had much luck.
View 5 Replies
View Related
Oct 29, 2004
I've been asked to create an automated DTS import which is to take place each and every evening. The import routine is to import data into an SQL table from an Excel spreadsheet.
The spreadsheet is not massively complicated though is does contain formulas and calculations.
When using the DTS Wizard:
"Choose Data Sourse"
Data Source = Microsoft Excel 97-2000
I then browse to the file name.
"Choose Destination"
I select the Server name and Database to import into.
"Specify Table Copy or Query"
I select 'Use a query to specify the data transfer' then click Next.
I then click on the query builder and here's where I get stuck. The Source tables panel contains many rows and when expanded they all contain the same thing (field headings in the Excel spreadsheet).
I'm totally stuck here - can anyone help? :confused:
View 1 Replies
View Related
May 18, 2007
Hi all,
Can anyone suggest a method of importing an Excel spreadsheet into SQL as a table?
TIA,
SamuelT
View 2 Replies
View Related
Dec 7, 2007
Hii
I want to import data from excel 2003 to sql.(sql server 2005). i am using windows xp.
when i am using the code i get the error.
the code is :
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 11;Database=D:BAESystemsTest_Log.xls;HDR=YES','Select * From [Sheet1$]')
The error is :
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
please get me out of this trouble.
View 10 Replies
View Related
Nov 18, 2007
Hi,
I have to import Excel file to SQL Server 2005 using SSIS. I am having alot of problems and was wondering if it's no trouble if you could please instruct me how to proceed.
My scenario:
I have an excel file that contains a heading and then subheading such as
HEADING
Acc_g Acc_n
12 n_a2
13 n_b3
My database has a table called accounts with fields acc_g and acc_n.
I need to enter the values from the excel into the table. I tried doing it but SSIS also tries to insert the rows following the last line in excel and returns nulls and the table does't allow null values.
How can i resolve it?
Thanks
View 3 Replies
View Related
Jun 21, 2006
I am trying to import data from an excel spreadsheet. The file comes from a client and sometimes it may not have any data, that is, it will be blank. It seems that the Excel Source in SSIS does not like empty files, is there any way around this? I suppose I can check to see if it is empty and abort the import process but I am not sure how to do this. Any other ideas on how to get around this??
View 3 Replies
View Related
Feb 27, 2008
i want a simple way (straight forward) to import data from excel to sql, not only this, but the excel file is not located on the SQL server itself.
my situation is the a person upload a file via asp.net and the file should be imported to excel(i want to use sql to transform and not parse in .net, then insert into database).
This can be done?
Thanks in advance
View 5 Replies
View Related
Jun 15, 2007
Hi, I'm building a web application in VB.NET 1.1 where users will upload Excel files at this webpage and the web application will transfer the data from the uploaded Excel file into MS SQL 2000. There are around 600-700 records and about 56 columns in the Excel file which is to be uploaded daily. There will be a few different files which I need to implement for as well.
I've sourced for and tried alot of methods. But either they can't satisfy my requirements or they just can't simply work.
- Most simple and direct way I've tried is creating a oledb connection to the Excel file in the following codes...
Function TestUploadData1(ByVal vFile As String) As String Dim Oleda As System.Data.OleDb.OleDbDataAdapter Dim Olecn As System.Data.OleDb.OleDbConnection Dim dt1 As DataTable
Olecn = New System.Data.OleDb.OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & vFile & ";" & _ "Extended Properties=Excel 8.0;HDR=Yes") Olecn.Open()
Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT INTO [ODBC;Driver={SQL Server};Server=(local);Database=dbSSC;Trusted_Conn ection=yes].[tblOutstanding] FROM [Report$];", Olecn) ExcelCommand.ExecuteNonQuery()
Olecn.Close() End Function
But from the above codes I kept getting the error "Could not find installable ISAM." at the line Olecn.Open(). I've tried to set the registry to C:WindowsSystem32msexcl40.dll but to no avail...
- I've tried another method using the OpenRowSet in the following codes Function TestUploadData2(ByVal vFile As String) As String Dim cn As SqlConnection Dim cm As SqlCommand Dim strSQL As String cn = New SqlConnection("server=localhost; user ID=accessSSC; password=accessSSC541; database=dbSSC")
cn.Open()
strSQL = "INSERT INTO tblOutstanding SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=" & vFile & ";HDR=Yes','SELECT * FROM [Report$]')" cm = New SqlCommand(strSQL, cn) cm.ExecuteNonQuery()
cn.Close() End Function
For the above codes, I kept getting the error "Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server." at the line cm.ExecuteNonQuery() and I've tried to disable the Ad Hoc settings in the registry and tried to create a link server but to no avail as well...
- I've tried DTS but I'd need to DTS package to run whenever the users upload a file. Is there an easy way that I can run the designated DTS package to run through my .Net web application?
Any help would be deeply appreciated. Thanks & Regards, Keith Chang
View 8 Replies
View Related
Feb 9, 2004
Hi:
I imported data from Excel to a table on SQL Server throught Data Transformation Services.
All data with a under score(like 1_5, 7_5) were replaced by Nulls. Is there any comment
about that?
Thanks a lot!
Regards,
Kevin Jin
View 6 Replies
View Related
Apr 24, 2001
I would like to know:
How can I import tables from the Enterprise Manager to Excel (copy and paste)?
Is it possible?
thanks
View 1 Replies
View Related
Feb 28, 2003
Hi:
I am writing a custom application which will allow users to upload data in a Excel to SQLServer.
The problem I have is that Excel can come with a number of worksheets. I would ideally like to read from the default work sheet - is there a way I can specify that my code should only pick up the default worksheet?
Thanks,
Bianca
View 3 Replies
View Related
Feb 10, 2005
Hi guys,
I need to import data from excel to MS SQL table, but I already have data in MS SQL table, I just want to update one column.
How can I do that?
View 1 Replies
View Related
