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.
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 ?
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
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
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.
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.
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.
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 '-'?
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:
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"
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?
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'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.
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
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?
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 ?
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!
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.
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?
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.
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.
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?
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>
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,