Commas In Excel
May 10, 2008
Hi
I have an excel file with 2 columns:
article art_group
kb art,tp,key
portal grup,port,li
I have a table in the database called tb_artGroups with the columns
article art_group
Now for each row in the excel i have to write the values after the comma in a new row such as:
article art_group
kb art
kb tp
kb key
portal grup
etc...
How can i do that with SSIS?
Thanks
Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.
View 4 Replies
ADVERTISEMENT
Mar 12, 2007
Hi,
I have table Article(ID,Title,FAID)
I need a query that will select all the Article.ID records where the FAID contains the number of the article ID
For exapmle, Article Table content is:
1, "Title1","2,6"
2, "Title2",""
3, "Title3","6,1"
6, "Title6","2"
Lets say I want to get all titles of article ID 1. I am going to its FAID which is "2,6"
So the query will return : "Title2", "Title6"
Can you advice how to write this? I can do a walk around solution where I will open a new table name FA but I rather not to.
View 3 Replies
View Related
Feb 18, 2008
I have a normal nvarchar(50) field in a table that has a single comma in it but when i read it using OleDbDataReader the comma gets removed.
any ideas what i am doing wrong ?
many thanks
View 4 Replies
View Related
Sep 4, 2007
I am using SQL 2005 and have a query that returns seven columns of data. In one the columns, the results contains commas. For example, one of the results is 'brooke, nick, dustin, and me.jpg'. The results are saved into a .csv file so it can be imported into Excel. The user named the file with the commas and I need to preserve the original file name.
My question:
Is there some way to format the data inside of SQL so that when it gets saved as a .csv file, the extra commas don't throw off the column layout?
I don't want to have to manually clean up possibly several hundred rows of data everytime I run the query.
Thanks in advance.
View 4 Replies
View Related
Mar 7, 2000
Do anyone know of a function, that I can use to removed commas
from a string? eg. "The lady, cross the road" should be "The lady cross the road"
Thanks, Vic
View 2 Replies
View Related
Jul 30, 1999
Hello all,
I am using SQL Server 6.5 SP5a.
I have to use bcp to import two text files everyday for database update. The problem is that some of the character fields that are being imported have double-quotes and/or commas in them. When these are imported into the SQL Server tables additional double quotes are being added into these strings.
Example:
INCOMING STRING = a"a
IMPORTED STRING = "a""a"
INCOMING STRING = b,b
IMPORTED STRING = "b,b"
I have searched through BOL and have not been able to find any information.
Does anyone know what is causing this and if so how to correct it?
Thanks,
Bryan Ziel
View 3 Replies
View Related
Jul 4, 2007
Hello Nice simple T-SQL question that will no doubt prove totally uncontroversial :) Anyone know the easiest way to get from:123456789to123,456,789in T-SQL? Currently I cast my int as money, use convert to varchar with style 1 and then lop off the .00. Is there an easier way? Two things before you get all upset and start squealing about front ends:1) These are admin functions that I am running from SSMS. I don't want to go to the bother of creating an application when SSMS is perfectly adequate for what I need (namely to-a-large-degree unformatted result sets).2) I'm not too fussed about it just curious. Ta & tra la!
View 2 Replies
View Related
Nov 17, 2014
I have a hierarchy of product categories and I want to string them together to show the complete path (breadcrumbs) to the item. Each category/subcat must be separated buy a comma. The catch is that not all items have the same number of cats/subcats. Here is what I am currently doing, but as you can see, this results in extra commas where the subcats are null.
Code:
create table #ItemCat(
itemNo int,
Cat varchar(50),
SubCat1 varchar(50),
SubCat2 varchar(50)
[Code] ....
Is there some way to concatenate these, separated by commas, but ignoring the NULL fields? For example, ItemNo 1 should show "Kitchen, Appliances"
View 3 Replies
View Related
May 15, 2008
I'm trying to export a query to a csv file. Everything works great
until I hit the name field. Is there a way to remove commas from a select statement.
Here's what my Data looks like
Item # | Name
-------------------------
1111 | Acme, Inc
1112 | Test Company, Inc
I'd like it to print out as
Item # | Name
-------------------------
1111 | Acme Inc
1112 | Test Company Inc
Thanks for any help in advance.
View 1 Replies
View Related
Jul 23, 2005
I am pulling several numbers from a SQL table, adding them and doingvarious calculaitons. The numbers do not display a comma to separatethousands. What is a way to format this?
View 5 Replies
View Related
Jan 1, 2008
Hey everyone. I've never posted on this site before so if I miss anything, please let me know.
I need to order the following data
18
14,15
13.2
14.2
15,16
15
0
14.1
12
6
15,16,18
11
16
15,17
13
14
14,15.2
17
into this order:
0
6
11
12
13
13.2
14
14.1
14.2
14,15
14,15.2
15
15,16
15,16,18
15,17
16
17
18
Because the data contains commas, I've had to change the data type to varchar. I've seen several instances of PATINDEX used however I have not ran across any of them that would put the above in the order in which I need. Btw, I have about 20 columns with this type of data and I eventually plan to use the results of each column to populate dropdownlists . Thank you all in advance.
View 11 Replies
View Related
Feb 5, 2015
I have a simple statement where I am trying to remove all commas that may be in the xn_workordeld column.
SELECT
xn_workordeld = case replace(xn_workordeld, ',', '') as "WORKORDER LD"
from workorder
View 3 Replies
View Related
Jun 3, 2008
Have a column in my DB called _Venue, which has been populated with address information separated by commas, like this:
Address1,address2,address3,address4
The address has sometimes only a few lines, for example I may have just Address1 or just address1 and address2 filled etc.
I need to write a query that will individually select addressX, so I can pick and choose what part of the full address to use.
Can these be done?
Thanks in advance.
View 9 Replies
View Related
Mar 24, 2006
Hi,
Can you help
I have a string that has multiple commas:
'AAA,BBB,CCC,DDD,EEE,...'
I need to pick any characters between commas or find all commas positions.
View 4 Replies
View Related
Jul 13, 2007
I am trying to create a SSIS package with a csv flat file for the both the destination and source. I cannot control the source file. I need to be able to handle an extra comma in the source file which occasionally contains suffixes i.e. {John, Smith, Jr,} which could cause some records to be 9 fields and others 10. Any ideas?
View 1 Replies
View Related
Dec 19, 2007
How can I convert a field with commas, cents and decimal to a numeric without commas and decimals and cents...?
Sample nvarchar values to desired output (integer whole value, truncated or rounded doesn't matter):
0.00 to 0
14,000.04to 14000
139,234.31to 139234
14,017,300.00to 14017300
View 2 Replies
View Related
Mar 7, 2008
Hi All,
Is there a way to remove Leading & Trialing Commas (,) in a string using SQL code ? Appreciate any thoughts.
Thanks
View 10 Replies
View Related
Jul 20, 2005
Once I've converted my floats to chars using STR, is there an easy wayto put commas in separating the thousands.i.e. convert53000000.12to53,000,000.12I'm thinking I'll have to do it with a user defined function and thevarious string functions myself but was wondering if anyone had aneasier way?CheersDave
View 6 Replies
View Related
Jun 26, 2007
HI
I have three different columns as email1,email2 , email3.I am concatinating these columns into one i.e EMail like
select ISNULL(dbo.tblperson.Email1, N'') + ';' + ISNULL(dbo.tblperson.Email2, N'') + ';' + ISNULL(dbo.tblperson.Email3, N'') AS Email from tablename.
One eg of the output of the above query when email2,email3 are having null values in the table is :
jacky_foo@mfa.gov.sg;;
means it is inserting semicoluns whenever there is a null value in the particular column. I want to remove this extra semicolumn whenever there is null value in the column.
Please let me know how can i do this
View 6 Replies
View Related
Jan 12, 2005
Hello,
This may be a strange request, but I am going to ask about it anyways.
Say for example if I have a table named TEST and in the table there is a column named NUMBERS, such that it is like this:
NUMBERS
1
2
3
4
How could I use a select statement in a way that a comma would seperate every return value, such that if I go 'Select NUMBERS from TEST' I would get:
1,2,3,4
Instead of:
1
2
3
4
Any ideas?
Thanks
View 1 Replies
View Related
Jun 24, 2007
Hello,
I recently used the REPLACE command, as described in a previous topic on this forum, to remove unwanted commas however I've now got a new problem, the column has become half a mile long. I was asked to raise a new topic and give examples, see below:
CAN ANYONE TELL ME:
1. Why is the column now bigger?
2. How can I redue the size of the column to it's origional size?
I have already attempted to use CONVERT, RTRIM and CAST around the replace command, all give an error.
Example query and result before REPLACE:
select ICMAFinInstName,CptyCode from tradedetails
ICMAFinInstName CptyCode
---------------------------------------------------------------------- ----------------
Example query and result using REPLACE:
select replace (ICMAFinInstName,',',' ')AS NoCommaInst,CptyCode from tradedetails
NoCommaInstrument CptyCode
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------
(I see in the preview that this forum has removed the spaces between the titles, but the dashes (--) show the size of the columns
Thanks
JM
View 20 Replies
View Related
Mar 11, 2008
I use SQL Server 2000.
I have some names stored in a table in the following format...
eg
"Royales, Jon"
"Smith, Alan"
"Jones, Tom"
what I would like to see is...
"Jon Royales"
"Alan Smith"
"Tom Jones"
I assume I should search the string for a comma (,) and use split and then rejoin the values. I need to do this as part of my select statement... is it possible?
I have searched the forums for similar posts but didn't find anything helpful.
Jon
View 11 Replies
View Related
Aug 3, 2015
I have what seems to be a common problem where I have names like "Last, First" that I need to be able to select using a multi-value parameter, which of course, SSRS passes as comma-delimited and gets messed up. I need to find a way to use a different delimiter or pass CHAR(44) instead of the comma, or something. Even worse, some people have suffixes and multiple commas in one name!
View 4 Replies
View Related
Oct 4, 2006
How to display money type data with commas every 3 digits?
I tried :
CONVERT (money, ProjectCost, 1) AS ProjectCost
but it gives me the type as 1234.56 no commas in it?
can anyone help me with that?
thanks!
View 4 Replies
View Related
Mar 20, 2006
If I pull a value from a MSSQL field with is defined as money, how can I get it to display in a textbox with commas and NO decimals?
87000.0000 = 87,000
I can currently remove the decimals like below but is there a way to add the commas as well?
decRevenue = drMyData("Revenue")
txtRevenue.Text = decRevenue.ToString("f0")
It current shows "87000".
View 1 Replies
View Related
Jul 9, 2015
All I have a situation where I need to split the column to rows which is delimited using commas and semicolons. Please find the below sample data.
Data in Tables
Test1, Test2, Test3
Test4;Test5;Test6
Desired output
Test1
Test2
Test3
Test4
Test5
Test6
Is there any way that I can get this output in SQL other than using the XML Conversion, since the data has some special characters in this.
View 7 Replies
View Related
Mar 9, 2011
I have a table that I am exporting via a SQL server table to a csv file, via the flat file connection manager within SSIS. My issue is that one of the fields in my table contains "," (commas), so when the file is created it creates new columns in the csv file because the field is "," comma delimited on the column. Is there anyway to get round this without having to get rid of commas in my table (which I've tried and works)?
View 12 Replies
View Related
Mar 29, 2007
Is there any nice way when saving a result set grid to CSV (right click, save to CSV) to properly delimit text fields that contain commas? As it is, text data that includes commas cannot be safely exported to CSV.
Example:
SELECT 'Hi there, friend!', 'Hi' UNION ALL
SELECT 'Bye now', 'Bye'
View 14 Replies
View Related
Sep 1, 2011
We have recently upgraded to SP1 of SSRS 2008. As a result, when we export a blank report to CSV, we now get a line of commas below the headings. Or found a way to not include the commas?
New SSRS Output
Portfolio_Reference,Portfolio_Name,R,TR,TD,TC,D,
Old SSRS Output
Portfolio_Reference,Portfolio_Name,R,TR,TD,TC,D,
View 4 Replies
View Related
Jul 25, 2015
Trying to upload excel in server where excel is not installed. BIDs was there in the server, when i am trying to craete Excel source I am not able.what the workround for this.. How to upload excel without excel installed on the server.
View 4 Replies
View Related
Nov 19, 2007
I am using a Excel Source to get the data from an excel file to sql server 2005 table. A couple columns are coming in a double precision float, but some values have characters in them, but those values are coming out as null, even though I changed the datatype from float to unicode string. Any inputs on resolving this will be much appreciated.
Thanks,
Manisha
View 4 Replies
View Related
Sep 13, 2015
We have 10 sheets in Excel File and 10 sheet contains errror data. How to load 9 sheets data in to 1 destination and error data in to other destination?
View 4 Replies
View Related
Feb 22, 2006
I am trying to get the contents of the Excel Files dynamically and dumping into the SQL Database using SSIS. Through WMI Event Watcher, I could find when one or more Excel files dumped in a particular folder and using ForEach Loop Container I was able to take all the filenames and pass it through Variables. But at the same time in the Data Flow, I have to pass each Sheet of an Excel File to the Excel Source control and export the data to my SQL Database using OLEDB Destination.
For that I need to get the names of each sheets in an Excel File and pass it to the Excel Source Control through variables. But when I give Data Access Mode as "Table name or view name variable" and provide the variable name in that, then it is giving an error message as "A destination table name has not been provided".
And at the same time, Since I was not able to provide an static Filename (as I am passing through Variables), when I tried to map the columns in the OleDB Destination, it is not allowing me to map the columns.
So all these things I should do at Run-time using Variables in SSIS. I don't want to hard-code any filenames or Sheet names. If any one of you have a solution, please share with me.
Thanks & Regards,
Prakash Srinivasan
View 3 Replies
View Related
