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.
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:
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.
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"
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?)
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.
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
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.
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.
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?
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..
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.
(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.)
'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.
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?
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.
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
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.
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)
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
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.
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:
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?
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??
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).
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
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?
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?
