Working With NULLs When Importing From Excel
Jul 31, 2006
I'm trying to use a DTS package to import data from an excel file. A few tables keep throwing errors about not being able to insert null values.
Is there any way to just skip a row when a column is null? I know there are certain columns that will never be null, so if they are null, I know that I just have a messed up row in Excel.
I could probably do it by implying an SQL query and having it ignore null rows, but I can't find anything useful on SQL syntax where Excel files are concerned.
View 5 Replies
ADVERTISEMENT
Jul 19, 2007
I am trying to import an Excel Spreadsheet into SQL2005. There is a column in the spreadsheet that has character values, and numbers. I have formatted the numbers as text on the spreadsheet. I have declared the column on the table as char/varchar/nchar, but whatever I do, the numbers don't get imported into the table, but show up as nulls. Any idea why?
Thanks
Mangala
View 1 Replies
View Related
Jul 27, 2006
I have a field that may be null which is valid, and I am findingsomething I didnt expect when working with nulls.SELECT NULL/4.0 will return NULL (which I expect), however, when I testit with a case it does not:SELECT NULL/4.0 AS 'TEST1', TEST2 = CASE NULL/4.0 WHEN NULL THEN'HURAY' ELSE 'OH DARN' ENDI can work around by testing for NULL first else CASE ..., but I'd liketo understand why the CASE does not test for null.TIArobSQL 2005 Enterprise x64, SP1
View 3 Replies
View Related
Mar 31, 2008
hi,
i am importing files using SSIS but i notice that attributes that are empty are imported as ZERO lentgh character instead of NULL.
is there some option that i can choose for maintaining nulls instead of a char() type? i though this was the default setting with DTS.
many thanks,
Nicolas
sample file with pipe delimiter:
|some data|another data||previous with no data|
View 4 Replies
View Related
Dec 4, 2006
I have an instead of trigger for update on particular table which adds3 or so columns and puts the total in a 4th No matter what the input is(as long as there is one NULL) the total is always NULL. More thanlikely is that only one of the fields has a value in it and the restnull but i don't want to do the MAX in case that is not true.a quick visual aid from input and desired outputInput DatacolA colB ColC tot5 NULL NULL NULLCurrent OutputcolA colB ColC tot5 NULL NULL NULLDesired OutputcolA colB ColC tot5 NULL NULL 5How do i Manipulate the TSQL command to work correctly?Update tbl_lossChecksSETclc_totalFees = Inserted.mny_LegalFees + Inserted.mny_AdjusterFees +Inserted.mny_Expense ,
View 4 Replies
View Related
Jul 20, 2005
Hi folks,I'm doing calculations based on data in a table, but the data has somezeros in the field I'm dividing by. I'm trying to write a script toreplace any field with 0 or null with 1, but it's not working. HEre'swhat I've got:Update A Set A.deptcode = A.deptcode,A.type = A.Type,A.Volume = (case A.VolumeWhen Null Then 1When 0 then 1Else A.VolumeEnd)From Data_Unsorted A Join Data_Unsorted B OnA.deptcode = B.deptcode and A.type = B.TypeMy table is data_unsorted and deptcode and type are my primary keysVolume is the item I want to put 1 if null or zero, and I'd thing theabove statement would work, but it doesn't. This table has 383 rows,and it says it updates 383 rows, but when I run the following query totest:select a.deptcode, a.type, a.volumefrom data_unsorted awhere a.AveMonthVolume = 0 or a.AveMonthVOlume is nullIt didn't work... still TONS of nulls and zero's. Is there a trick tothis???Thanks,Alex.
View 2 Replies
View Related
Feb 27, 2008
I have an Excel spreadsheet with a field that may contain blanks (empty). When I filter on that field to show only the blanks, I get 4000 records returned. But if I import the spreadsheet into SQL Server, the test in there for blanks ('') returns nothing. However, if I test for Nulls in that field, I get 5000 records returned. I don't know which one (Excel or SQL Server) is giving me the right answer. Can anyone help me understand what's going on?
Thanks!
View 1 Replies
View Related
Sep 11, 2007
Hi,
I need to import an SQL string from MS Excel 2003 to SQL SERVER 2000.
The string I need to import is composed by 5 different several blocks and looks like:
Code Snippet
CommandLine01 = "USE mydb"
CommandLine02 = "SELECT Block ..."
CommandLine03 = "GO
ALTER TABLE Block...
GO"
CommandLine04 = "UPDATE Block..."
CommandLine05 = "SELECT Block..."
The detail of the SQL string is at:
http://forums.microsoft.com/msdn/showpost.aspx?postid=2093921&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1
I am trying to implement OJ's suggestion:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2117223&SiteID=1
to use multi - batch processing to import the string to SQL SERVER, something like:
Code Snippet
Dim SqlCnt, cmd1, cmd2, cmd3
'set the properties and open a connection
cmd1="use my_db"
cmd2="create table mytb"
cmd3="insert into mytb"
SqlCnt.execute cmd1
SqlCnt.Execute cmd2
SqlCnt.Execute cmd3
Below is the code (just partial) I have, and I need help to complete it.
Thanks in advance,
Aldo.
Code Snippet
Function TestConnection()
Dim ConnectionString As New ADODB.Connection
Dim RecordSet As New ADODB.RecordSet
ConnectionString = "Driver={SQL Server};Server=myServer;Database=myDBName;Uid=UserName;Pwd=Password"
ConnectionString.Open
CmdLine01 = " USE " & myDB
CmdLine02 = " SELECT ACCOUNTS.FULLNAME FROM ACCOUNTS" ...
CmdLine03 = "GO
ALTER TABLE Block...
GO"
CmdLine04 = "UPDATE Block..."
CmdLine05 = "SELECT Block..."
RecordSet.Open CmdLine01, ConnectionString
RecordSet.Open CmdLine02, ConnectionString
ConnectionString.Execute CmdLine01
ConnectionString.Execute CmdLine02
'Retrieve Field titles
For ColNr = 1 To RecordSet.Fields.Count
ActiveSheet.Cells(1, ColNr).Value = RecordSet.Fields(ColNr - 1).Name
Next
ActiveSheet.Cells(2, 1).CopyFromRecordset RecordSet
'Close ADO objects
RecordSet.Close
ConnectionString.Close
Set RecordSet = Nothing
Set ConnectionString = Nothing
End Function
View 7 Replies
View Related
Apr 20, 2001
I have an excel file with 5 columns. 1st column values are -
^^234
^^456
678
123
456
SQL 2000 DTS, doing straight copy into an Access table takes only first two
rows from 1st column ( see top 2 rows with trailing white spaces). How can I
import all of rows ?
Thanks for your assistance !
_Ivan
View 1 Replies
View Related
Apr 25, 2006
Is there any way at all to import from Excel to MS SQL without using the DTS package and without doing it manually? I really do not like the lack of flexability that DTS has and I need something else that will always work. Basically I am importing shipping documents that are in excel and putting them into a table. This has 2 issues, 1 I don't know the file name, just the folder it is in and 2 there is a large amount of header data in the file, some of which I need, some of which I don't. Is there any solutions out there? Thanks!
View 1 Replies
View Related
Aug 21, 2004
Hi there, could let me know how to reference to an excel database in SQL code?
I think
Select * from OPENROWSET ('Microsoft.Jet.OleDB.4.0', 'EXCEL 8.0;Database=C:MyExcel1.xls',Sheet1$)
Would import everything from a spreadsheet but im not sure how you can reference individual columns?
View 1 Replies
View Related
Aug 26, 2004
I need to copy the data in two spreadsheets into two
tables in "SQL Server 2000". Below are the details:
One spreadsheet contains the data that needs to be added
to an existing table in SQL server. All field names in the
spreadsheet match the DB table column names.
Another spreadsheet contains the data that needs to be
copied to a new DB table. The table currently does NOT
exist in the DB.
I'm not sure how to accomplish this. ANy help would be
appreciated.
View 2 Replies
View Related
Oct 23, 2006
Hello Everyone,
Here is my code in Excel VBA:
Sub command_dyer3()
Dim conn As New Connection
Dim rec As New Recordset
Dim comm As New Command
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("command")
conn.Open "Provider=microsoft.jet.oledb.4.0;" & _
"Data Source=" + ThisWorkbook.Path + "FromDyer.mdb;"
Set comm.ActiveConnection = conn
comm.CommandText = _
strSQL = "USE CHEC" & _
"SELECT DISTINCT" & _
"DAT01.[_@051] AS Branch," & _
"DAT01.[_@550] AS LoanType," & _
"DAT01.[_@040] AS Date," & _
"DAT01.[_@LOAN#] AS LoanNum" & _
"FROM DAT01 INNER JOIN [DATE_CONVERSION_TABLE_NEW]" & _
"ON DAT01.[_@040] = [_@040]" & _
"INNER JOIN [SMT_BRANCHES]" & _
"ON DAT01.[_@051] = SMT_BRANCHES.[BranchNbr]" & _
"WHERE" & _
"DAT01.[_@040] Between '06/01/2006' And '06/30/2006'" & _
"AND DAT01.[_@051] = '540' " & _
"And DAT01.[_@LOAN#] Like '2%' " & _
"And DAT01.[_@550] = '3' " & _
"Group BY" & _
"DAT01.[_@051]," & _
"DAT01.[_@550]," & _
"DAT01.[_@TP]," & _
"DAT01.[_@040]," & _
"DAT01.[_@LOAN#]" & _
"ORDER BY --[DATE_CONVERSION_TABLE_NEW].MONTH," & _
"DAT01.[_@051]"
comm.CommandText = strSQL
'comm.Parameters(0) =
rec.Open comm
ws.[a1].CopyFromRecordset rec
rec.Close: conn.Close
End Sub
I get the following error mesage:
Command text was not set for the command object on the following line:
rec.Open comm
What am I doing wrong here?
TIA
Kurt
View 20 Replies
View Related
Dec 14, 2007
I have a list (200+) that I would like to pull off of an excel sheet and insert into the table. I have the script,
INSERT INTO tax_jurisdiction SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:TaxJuris.xls;Extended Properties=Excel 8.0')...[TaxJuris$]
I get the error: Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.
I'm at a loss. I've looked at all the fields and modified the deceimal fields to number format. What else should I be looking for?
View 2 Replies
View Related
Jul 19, 2007
I have been working on some code to get excel data into a datagridview and also into my database.
What i have works to get the data but it does not display it in the view
It does show that there are records but will not show any data
It shows that there are 9 records that i can navigate but all it does it does is change the record number.
And it shows one blank row.
I have seen quite a few threads and examples but i can't quite put this one together.
Can someone help me get the data into the view?
Thanks
Dim opendlg As New OpenFileDialog
opendlg.Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"
If opendlg.ShowDialog() = Windows.Forms.DialogResult.OK Then
Dim pathname As String = opendlg.FileName
'MsgBox(pathname)
Dim connect As System.Data.OleDb.OleDbConnection
Dim adapter As System.Data.OleDb.OleDbDataAdapter
Dim dataset As New System.Data.DataSet()
connect = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _
"data source=" & pathname & ";Extended Properties=Excel 8.0;")
'C: estoledb.xls
adapter = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", connect)
connect.Open()
adapter.Fill(dataset)
this is where the problem is
Me.Table1DataGridView.DataSource = dataset
adapter.Fill(Me.Database1DataSet.Table1)
connect.Close()
MsgBox(CType(dataset.Tables(0).Rows(0).Item(0), Object).ToString)
End If
View 2 Replies
View Related
Jul 17, 2007
Hi,
I know this issue exisits in DTS but needs to check still is in SSIS, Also you guys may have a better solution for it.
Issue: When I try to import a column from excel which has data like A,B,C,D,E,4,5 in the destination table has the data type as varchar it imports only A,B,C,D,E and 4 & 5 as nulls. How to fix this.
View 10 Replies
View Related
Oct 4, 2006
Hello,
I am very new to sql server 2005, and i wanted to know if anyone can help me in creating a SSIS packge that can do the following:
1. Import an excel sheet
2. Delete uneeded columns
3. Merge two fields together, only if first column is not null, and if it is null delete that record.
4. Dedupe records
5. export into dbf format
6. If possible, records that get exported get marked as exported in database.
Any help would be very much appreciated.
View 3 Replies
View Related
Jul 31, 2007
How do i import a Excel file into a table i have created in my database in SQL server 2005???
View 10 Replies
View Related
May 23, 2008
Hi i have an excel spreadsheet in which I want to take the data and put them in a table, the table and excel speadsheet have the same unique-ID, what i need to do is retrieve the extra fields of the excel spreadsheet and match them up with the table. Is this possible, if so how?
View 6 Replies
View Related
Jun 27, 2005
Dear AllI am trying to import data from excel sheet to sql server database, by using method 2, it creates a table on fly but it never shows any table in database tables' list but when i execute the code again, system throws an exception that table already exists.On the other hand method two assumes that there is an existing table in db, but after execution, it never shows data, table remains empty. Here is code, please tell me whats wrong with this code.Regards<code>Dim ExcelConnection As New System.Data.OleDb.OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\annieanna.xls;Extended Properties=Excel 8.0;")ExcelConnection.Open()'For existing Table.................METHOD 1Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [User ID=sa;Data Source=CBS101;Initial Catalog=IIETesting;Provider=SQLOLEDB.1;Workstation ID=CBS003].[anna] SELECT * FROM [Sheet1$];", ExcelConnection)
'For new Table......................METHOD 2Dim ExcelCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [User ID=sa;Data Source=CBS101;Initial Catalog=IIETesting;Provider=SQLOLEDB.1;Workstation ID=CBS003].[anna] FROM [Sheet1$];", ExcelConnection)ExcelCommand.ExecuteNonQuery()ExcelConnection.Close()</code>
View 3 Replies
View Related
Feb 3, 2006
Here is the scenario: I have an excel spreadsheet that contains 182 columns, and I need to move this data into a semi-normalized database for reporting. The SQL Server database schema has 11 tables. Some of the tables are going to use identity columns for their PK, other tables are using a value that comes from this spreadsheet for their PK values.Anyway, I have never done a DTS package of any significance before, and know I most likely need to write some VBScript to handle sticking data into the proper data tables, etc.I am just hoping someone can point me at a good resource, give me an alternative means of doing this (this is a process that will need to happen whenever a new Excel spreadsheet is dropped into a folder or on a schedule, either one). I would love to write some C# code to handle these things, but a DTS package would probably be the best, I just don't know where to start.Thanks,
View 2 Replies
View Related
Jan 24, 2008
i used to be able to import Excel spreadsheets and convert them into SQL tables using MS SQL Server Enterprise Manager
however, i haven't been able to find out how to do this in Visual Studio 2005 - does anyone know how to do this (or whether it can be done at all) ?
View 1 Replies
View Related
Apr 17, 2006
I have an excel document that I need to import into a table. The format of the Excel spreadsheet has three columns:
|First Name | Last Name | Zip code|
This data is not normalized. I want to convert the data in the spreadsheet to foreign key values for two existing tables.
The table I'm importing to will contain records with a many-to-many relationship.
For example,
|John|Smith|77079
imports as:
|1|1|
Where "Smith" is the first record in the 'rep' table and "77079" is the first record in the 'zip' table.
How can I bring the data in and then convert the de-normalized data into ID values from my existing tables?
I have thousands of records that I need to do this for and want to automate the process.
Thanks for any help.
Regards,
-D-
View 1 Replies
View Related
Sep 12, 2006
Hi.
I have done this successfully the last time but just couldn't get it to work this time round. Please help.
I was trying to import data from an Excel file into MS SQL table. NONE of the numeric or value fields (right justified) works because they shown <NULL> in MS SQL table after the import via DTS. Those string fields were able to import ok into the table. DTS didn't show any error message during the DTS run.
Any advise? Thank you.
Best regards
Teck Boon
View 3 Replies
View Related
Feb 3, 2004
i have a spreadsheet with 4 workfiles and i want to import into sql. i want to make one workfile into one table in sql
how do i go by doing this? and spreadsheet has a lot of macro too
thanks
View 1 Replies
View Related
Apr 14, 2008
I am using SQL 2005 the developer edition and need to import a couple thousand records while I try and create a web tool that will filter the data.
I know unltimately I will need to create some sort of application to move the data over. But I am not going to waste my time do that unless I am successful on the web site side.
What is an easy way to import the data. I tried the import wizzard multiple times and have recieved an error...multiple times.
View 6 Replies
View Related
Sep 26, 2007
Hello,
Am very conversant with MS SQL 2K, however i just downloaded 2005 express edition and will like to know how to
1. How to import tables using DTS graphic view to Management studio
2. How to get to Query Analyzer ?
thanks
Mary
Yes O !
View 1 Replies
View Related
Dec 17, 2007
Hi am trying to import data from a excel file into my 2005 DB using a SSIS package.
This first thing i've done is create a Excel source and then a derived column task as i need to format my date, so am using substring to format the date but the expression am using will not work am geting a error on it
the data in the excel file is like 8122007
here is my expression
substring(date,1,1) +"/"+ substring(date,2,2) +"/"+ substring(date,4,7)
Any idea i think it's something got to do with the data type
View 9 Replies
View Related
Dec 11, 2007
What do others do if you need to import excel files into SQL Server?My main problems are1) zipcode formatting issues. If the column is a mix of zip and zip+4,I have problems retrieving all zipcodes.2) If the last column contains NULL no information is imported.All this with using the Management console using Import data in SQLServer 2005. I am simply trying to import the data into NEW databases.The excel files vary in structure. Right now I am working on case bycase basis.Does anyone see these types of problems?What I am doing now is converting the excel file to a tab delimitedfile and that seems to work.TIA.
View 3 Replies
View Related
Jul 20, 2005
Hello,I nee to write something that will transfer excel data into an SQL Servertable.I have for another database application I wrote have it importing Excelspreadsheet data using cell by cell, row by row method. This is fullyautomated so the user can choose whatever spreadsheet they want to importand press a button which sits on a VB6 frontend.This has been good for that situsation but it can be very slow when thereare large amounts of data to process.I am just wondering are there any faster, better alternatives that wouldstill enable a user to select which excel spreadsheet to importas the application I am writing now will sit on a website frontend, usingASP, and I'd really like to try and speed things up if I could.any advice would be much appreciated.Thankyou,Oh, and hello, this is my first post here!Jayne
View 4 Replies
View Related
Jan 16, 2006
When I try to import data from Excel with the dts wizard I get the following eror message when I select Excel as a data source:
TITLE: SQL Server Import and Export Wizard
------------------------------
An error occurred which the SQL Server Integration Services Wizard was not prepared to handle.
------------------------------
ADDITIONAL INFORMATION:
Exception has been thrown by the target of an invocation. (mscorlib)
------------------------------
The connection type "EXCEL" specified for connection manager "{D4D59FCE-C0A4-4AA2-B374-766665A74159}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({B2F473AA-8E07-40AA-AF01-9AD13DE7B4B8})
------------------------------
The connection type "EXCEL" specified for connection manager "{D4D59FCE-C0A4-4AA2-B374-766665A74159}" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
({B2F473AA-8E07-40AA-AF01-9AD13DE7B4B8})
------------------------------
BUTTONS:
OK
------------------------------
That is the complete error message as I get it. I used the copy message text feature and pasted it directly here.
I really hope someone can help with this one as it has me completely stumped and also unable to finish an assignment.
View 1 Replies
View Related
Jun 19, 2007
Hi guys!
I'm having troubles trying to import an excel file saved as XML.
Tried XML Source, Excel Source, OLEDB Source with no success.
Could someone point me to the right direction?
Thanks in advance.
View 1 Replies
View Related
Dec 13, 2007
Hi,
I am new to integration services. I need to import data from excel sheets to a sql server 2005 tables. I have 3 sheets which should be loaded into three different tables in sql server. I need the connection strings to be dynamic as the excel file names will change daily.Please get me some samples or some links to study to solve the above problem.
Thanks
View 3 Replies
View Related
