Problems Importing Negative Numbers From Excel

Aug 14, 2007

Hi there,

I am trying to import about 3300 lines of data from excel. Some of the columns in excel have negative, positive and even 0 as numbers.

I have matched those columns in the DB and have tried all the different data types that i thought should work, but on import, all negative and 0 numbers get dropped.

I have played with the excel formatting as well, but nothing seems to work.

Anyone encounter this before?
Thanks,

View 14 Replies


ADVERTISEMENT

Query Analyzer Shows Negative Numbers As Positive Numbers

Jul 20, 2005

Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?

View 7 Replies View Related

Problems Importing Data From Excel (numbers Stored As Text Error)

May 21, 2008

i have an excel sheet
in it there is a column that holds values of item numbers
some of these values are preceeded with zeros E.G "00123" (with out the ")
when i view the data in excel i get this little green corner whice states (when pressed) that these values are numbers stoerd as text.
no inside the excel sheet i have no problem with that
but when i try to import the excel sheet into ssis using an excel source task
all these values are imported as nulls!!!

i am lost
i tried converting the format of these cells to numbers but then i loose the leading zeros
what i done temporarly to solve this problem is to accept excel's suggestion and turn these values into numbers
i then import them and convert them to strings in ssis and then ad dthe zeros.
now althouge this works, this isnt realy a solution.
i canot manualy correct each excel file each time i get a new copy
and in the future i will not have the luxuery of having fixed lenght values (so i wont be able to know how many zeroe i"d need to add)

there must be a better way
please help
thanks in advance
Daniel

View 3 Replies View Related

Dataflow To Excel - Convert Numbers Stored As Text To Numbers Excel Cell Error

Mar 27, 2007

I'm trying to write data to excel from an ssis component to a excel destination.

Even thought I'm writing numerics, every cell gets this error with a green tag:

Convert numbers stored as text to numbers

Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.

I'm hearing this a common problem -

On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:

<style>.text { mso-number-format:@; } </style>

is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.

Maybe some setting in my write drivers - using sql servers excel destination.


So close.. Thanks for any help or information.

View 1 Replies View Related

Sp_spaceused With Negative Numbers?

Mar 1, 1999

Hello:

I have gotten the following information from sp_spaceused:

----------------------------------------------------------------------
database_name database_size unallocated space
------------------------------ ------------------ ------------------
EP_tran2 700.00 MB 273.21 MB

reserved data index_size unused
------------------ ------------------ ------------------ ------------------
437034 KB 295106 KB 1447796 KB -1305868 KB
--------------------------------------------------------------------

I unfortunately since this is for a test database that I have put the data and log on the same device.

My questions are:

1) Why do I have negative numbers?

2) Should I be concerned about this?

3) for a live database can I drop the log and put the log on another device? What do I need to do?

4) has anybody seen this before?

5)what can I use to get accurate numbers?

THanks. Any information will be greatly appreciated.

DAvid Spaisman

View 1 Replies View Related

How To Round With Negative Numbers?

Jul 23, 2005

I am using a select statement to obtain a result set back with aggregateddata. The problem is that I am seeing column data with 11 to 13 digitsafter the decimal point. I tried using the STR function, but then the OrderBy clause does not sort properly because there are negative numbers in theaggregated data... I tried using Round, but that does no good either - itstill ends up displaying too many digits after the decimal point. Right nowI'm just using Query Analyzer to display the data, so I can live with it fornow. But, in the future, my app will be getting a result set back and Iwould prefer not to have to go through each row and do a round on it fromthe program. Does anyone know how to solve this problem?Thanks for any help,Bob

View 4 Replies View Related

Can't Convert Negative Numbers From String Format

Oct 11, 2007

Hello,
I'm having problems with a column of numeric string data coming from a tab delimited CSV file. When a number in the column is negative, it is expressed this way; 1,240.52-

The negative symbol occurs at the end of the numeric string. The destination column is in a SQL Server 2005 table. I've tried to change the data type of the table column, and three or four different data types in a data conversion task, but nothing has worked so far to bring the data over.

Any ideas?

Thank you for your help!

cdun2

View 4 Replies View Related

The Zero Line Seperating Postive And Negative Numbers Should Be Darker

Aug 14, 2007



Hi,
I am having chart which has positive and negative numbers.I need dark line(gridline) at 0.00th location.So that it differentiates between positive and negative levels.So please help me out in solving this asap.

Thanks in Advance
Shri

View 4 Replies View Related

Reporting Services :: SSRS Is Not Subtracting Negative Numbers It Adds Them

Apr 20, 2015

=Sum(Fields!balance_due.Value)
Customer Name Balance Due
1001 Bob 100.99
1002 Jim 10.95
1003 Kim -12.67
1004 Lisa 13.23
Total $137.84

It should be like this:

Customer Name Balance Due
1001 Bob 100.99
1002 Jim 10.95
1003 Kim -12.67
1004 Lisa 13.23
Total 112.5

How can I get SSRS to add and subtracted numbers.

View 10 Replies View Related

Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers

Feb 1, 2007

I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.

I already tried to set the value as CDbl which returns error for the cells containing a string.

The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.

Any suggestions?



View 1 Replies View Related

DTS Import Negative Excel Values

Aug 8, 2007

Hello everybody,

I have a DTS package that imports excel spreadsheets.
The excel spreadsheet cells contain numbers based on vlookups. Now negative values appear in these cells as -999 but when loaded into the table they appear as (999).
Is there anyway way to configure it to just load -999? The tricky part is I don't have control over the format of the spreadsheet cell and the destination table field is varchar. I am hoping there is some way that the DTS interprets and picks up the value as it is displayed in excel.
Or do I have to stick with converting it manually by replacing '(' and ')' with nothing and appending '-'?

Thanks for any help.

View 1 Replies View Related

TSQL + VBA Excel 2003 - Importing Data From MS Excel 2003 To SQL SERVER 2000 Using Multi - Batch Processing

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

Excel Not Recognizing Numbers

Oct 31, 2013

I've got a SQL query returning data into MS Excel.

One of the columns returns numbers that I then have a SUMIF formula totalling.

However, when the numbers get to Excel, they are not recognised as numbers so my SUM doesn't work. Even if I format the cells as number, general or currency, the sum doesn't work.

Excel knows that there is something in the cell but doesn't treat it as a number in any way.

Can I include something in the SQL statement so that it forces the results to be a number?

View 4 Replies View Related

Excel Numbers Format

Feb 22, 2007

Hi all i'm having an issues when i trying to do a report:



i make a report and there is a field which contains data that can be numeric and numeric with letter: "77756" or "345WS" when i export my report to excel i and i open it i get like a make in the upper left coner of the cells that have only numbers asking me to convert this to numeric format, but this think is that i need this values to be string so i can apply filtering. is there a way that i can format my cell to string so the excel take it like that and allowme to do the autoifilter.



I would really appriciate your Help

Thanks

View 11 Replies View Related

Numbers Converted To Text When Exporting To Excel

Dec 11, 2007

Hi all,

I'm having an issue with data from an OLE DB source being converted from numeric [DT_R8] to text when I export it to Excel. I had the column in the spreadsheet formatted as Numeric, but when I set it up as the data destination, it is showing in SSIS as [DT_WSTR].

The SSIS job runs OK, it just converts my numeric data to text in the spreadsheet. I'm surprised that the job actually runs, since I have to do explicit data conversions for my OLE DB string data in order to convert it to unicode. From what I have read elsewhere, the decision was made in SSIS not to use implicit data conversion, but to require explicit conversion. Yet this export is converting my numeric data to text.
I would appreciate any information on how to set up the Excel Destination to properly handle numeric information.

View 8 Replies View Related

Report Table Numbers Are Text In Excel

Apr 22, 2008

Hi
I have a report, a matrix as always, were the numbers are text when I save the report to Excel. What did I do wrong or what properties do I have to change?
Kind regards

View 8 Replies View Related

When Exporting To Excel - The Numbers Show Up As Text

Apr 4, 2007

Hi,

is there a way to force the numers to show in excel as numbers, not as text?



Thanks,

Igor

View 1 Replies View Related

Unable To Format Excel Destination Column For Numbers

Oct 31, 2006

Hi,

I have an excel export with numeric values. When the SSIS package writes into the excel it treats all data types as strings and attaches an apostrophe in the beginning. I tried formatting the excel sheet with the number data type and saving it . It doesnt work.

Other strange thing is that when I go into the advanced editor for the excel destination and look into the properties of the external columns all of them have the data type as Unicode String[DT_WSTR] irrespective of what the data type is from the input.The UI allows me to change it to numeric for numeric columns without any error but the value is not saved at all which is very frustrating. I also tried changing it in the xml file of the package, it some how seems to overwrite it after validation. It would be really nice if it threw an error saying that it cannot be changed.

Anyways there seems to be no way of changing the destination data type if it s an existing file and if I create a new excel sheet there is no way of formatting data. Is there any way out?

Thanks

View 1 Replies View Related

Numbers(format=N2) Showing As Strings In Matrix-&&>excel Export

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

Importing An Excel

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

Importing From Excel To Ms SQL Without DTS

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

Importing From Excel

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

Importing From Excel

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

Importing SQL To Excel Almost There

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

Importing From Excel

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

Help With Excel Importing

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

Importing Excel

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

Need Help Importing Excel

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

Importing And Excel File

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

Importing Data From Excel Into Sql

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

Importing Excel To Sql Server....

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

Importing Excel Data

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

Importing Excel To Sql Table

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







Copyrights 2005-15 www.BigResource.com, All rights reserved