Sometimes when I export files from SQL Server Management Studio in delimited text format, some of the fields end up being fixed width rather than trimmed (trailing spaces removed). Is this a bug? Other than specifically trimming every variable in my select, is there anything I can do about it? Also, how do I report a bug?
View 2 RepliesHas any one managed to get Report Manager to have the option to export to pipe delimited? There are documents out there how to do it on SSRS; I need it on Report Manager. Any Suggestions?
DGraham
vbwrangler@yahoo.com
I'm trying, through the SQL 2005 Mgt Studio, to export a simple table in a delimited format. I'm selecting a double quote as the text qualifier. My expectations were that only text type fields would be exported with the double quotes an numerical fields would not have any quotes around them. SQL 2000 does this just fine, but 2005 is exporting all my text type and numeric fields with double quotes. Is this a change to SQL 2005 or am I doing something wrong.
Thanks for the help,
Tim
In exporting from a OLEDB connection to a flat file.
In the originating table the field for DOB is in a varchar(10) format ex. 01/17/2007. The flat file connection destination is setup as a DT_STR. When you look at the OLEDB connection table preview you see it as 01/17/2007. When it is export to the delimited <CR><LF> <|> pipe delimited the format looks like this 01/17/2007 00:00:00. The issue would be resolved with a right ragged fixed width file. But this is not the requirement for the project format fot the file. I have tried delete and recreating the connections, and even tried doing a data conversation from the OLEDB connection to a char(10). Also, thourgh the transformation services with out any luck. On the flat file data connection I am using expressions to map to a declared variable path and variable name and I listed the expression language below also:
@[User::varPATH]+ @[User::varFileName]+ RIGHT("0" + (DT_WSTR, 2) MONTH( GETDATE() ), 2) + RIGHT("0" + (DT_WSTR, 2) DAY( GETDATE() ), 2) +RIGHT("0" + (DT_WSTR, 4) YEAR( GETDATE() ), 4) + ".txt"
If you can give some help in getting the file to export to a delimited "|" file in the format of "01/17/2007" this would be greatly aprreciated. I also forgot to mention that I have also tried putting a text qualifier in like" on the flat file destination column layout and get the other format still.
Thanks in advance.
Scott
Hi. Im new to SQL and I need to export a SQL table as a comma delimited text file which is straight forward. However two of the fields are integers and I need these to be right justified with zero's.
In Access I would use something like format(columnname, "00000000") to get it to work, but SQL Server doesn't like this.
How can I do this?
I want to allow visitors to filter a list of events to show only those belonging to categories selected from a checklist.
Here is an approach I am trying:
TABLE Events(EventID int, Categories varchar(200))
EventID Catetories
--------------------------
1 ‘6,8,9’
2 ‘2,3’
PROCEDURE ListFilteredEvents
@FilterList varchar(200) -- contains ‘3,5’
AS
SELECT EventID FROM Events
WHERE (any value in Categories) IN @FilterList
Result:
EventID
----------
2
How can I select all records where any value in the Categories column
matches a value in @FilterList. In this example, record 2 would be
selected since it belongs to category 3, which is also in @FilterList.
I’ve looked at the table of numbers approach, which works when
selecting records where a column value is in the parameter list, but I
can’t see how to make this work when the column itself also contains a
comma delimited list.
Can someone suggest an approach?
Any examples would be greatly appreciated!
Gary
Hi,
Good evening! I have a problem. I have a namelist coming from a distribution list of an active directory. When I converted it to csv file, the members reside in just one column and separated by a comma. I want the names to be separated in a row one by one. I tried it on excel and I used the transpose column but to no avail. My last resort is to import it on sql but the names on the column was cut and not complete. Do you have any idea how to do this. Your help is highly appreciated.
this is the sample file..
names
kelly.yap, lizzy.fox, yahoo, finance.dep, hope.miller, porly.john
the maximum names in a row is 566.
thanks in advance.
myBU
Hi.
I have a data source file which I need to load into my SQL table using DTS package. | is used as delimited to separate each field. Recently, i discovered some of the fields came with | has part of the field content. This has caused some problem with the loading process because the field has been split into 2 because | is recognised as a new field.
What can I do now? I can't change the delimited to something else or neither can I stop my data source from using | as this is beyond my control.
Please advise. Thank you.
Regards
John Smith
Does anyone know if it's possible to get a tab delimited export function available from the web interface? I saw a couple posts on this, even one person saying they got it working, but adding
<Extension Name="TXT" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<OverrideNames>
<Name Language="en-US">TXT (Tab Delimited Text File)></Name>
</OverrideNames>
<Configuration>
<DeviceInfo>
<FieldDelimiter>	</FieldDelimiter>
<Extension>TXT</Extension>
<Encoding>ASCII</Encoding>
<NoHeader>true</NoHeader>
</DeviceInfo>
</Configuration>
</Extension>
to my rsreportserver.config under the Render tag, it didn't seem to make any difference. Thanks a ton for any help since I'm in a bit of a bind since my project's due in a little over a week and this new requirement was just sprung on me.
Jeff
Would like to have a view created to display the result at the bottom of this message. We will be using Dreamweaver to display the information from this view. Also, for the record, we are using sql 2000. Any help would be greatly appreciated.
tblservers
servid servername
1 server1
2 server2
3 server3
tblapplications
appid appname
1 app1
2 app2
3 app3
tblapplink
id appid servid
1 1 1
2 1 2
3 1 3
4 2 1
5 2 3
6 3 1
we want to display this information as below:
appname serverlist
app1 server1, server2, server3
app2 server1, server3
app3 server1
Thank you very much,
mrtwo
parsing any delimited string (in above example it is using ',' as parsing delimiter. This query can be useful in many business scenarios where in we have input data as a long string containing delimited values.
declare
v_sql VARCHAR2(2000);
v_pos INTEGER;
v_differentiator VARCHAR2(10);
[code]...
Trying to export a SQL Server 2012 table to CSV file, with tab delimited
Using the Export wizard it completes it no problem but behaving as expected,
Lets say I have 3 Columns
address1 NVARCHAR(50)
address2 NVARCHAR(50)
address3 NVARCHAR(50)
It is exporting it is doing
Column Width 50 (with value) |tab| Column Width 50 (with value) |tab| Column Width 50 (with value) |tab|
I was hoping for
Actual Value Length |tab| Actual Value Length |tab| Actual Value Length |tab|
Suppose I have a table named Test (referred in the query below)
Category Indicators
ctgy1Y,,,,
ctgy2Y,Y,Y,N,
ctgy3,Y,,Y,
and If I would like to transform this table to
Category Indicators
ctgy1Y
ctgy2Y
ctgy2Y
ctgy2Y
ctgy3Y
ctgy3Y
I am able to do it using the logic below
CREATE TABLE dbo.Numbers (Number INT IDENTITY(1,1) PRIMARY KEY CLUSTERED)
WHILE COALESCE(SCOPE_IDENTITY(), 0) < 5
BEGIN
INSERT dbo.Numbers DEFAULT VALUES
END
SELECT category,
SUBSTRING( Value, Number, CHARINDEX( ',', Value + ',', Number ) - Number ) as program
FROM Test
inner
JOIN Numbers
ON SUBSTRING( ',' + Value, Number, 1 ) = ','
and CHARINDEX( ',', Value+',', Number ) - Number <> 0
where Number <= Len(Value) + 1
But I would like to Transform this table into something like the one below (where if 'Y' before 1st comma then Q1, if 'Y' Before 2nd comma then Q2 and so on
Category Indicators
ctgy1Q1
ctgy2Q1
ctgy2Q2
ctgy2Q3
ctgy3Q2
ctgy3Q4
What is the best and efficient way to obtain this? Any help will be greatly helpful.
Thanks
Ram
hi all,
While importing a tab delimited file..
it seems ssis interprets the incoming col as 50 chars in length even
though it is far smaller.
any ideas how this could be??
any help would be appreciated.
used bcp utility to send data to output file in tab-delimited format (-t ), but headerfile is separate entity in this query.
when I set FILEheader = firstname,lastname...what must I use to change the comma to tab in the header string. I have tried various ways , {t}, [-t], and others. what am I missing?
Is there a faster way to create my pipe delimited BCP file, besides from creating a format file? Actually, my problem is that I am having issue with the file. It looks perfect, like:
Code Snippet
Marie|32|brown|single
Gay|33|black|married
But when I load it to DataStage it puts the entire row as one column. I already specified the | as the delimiter in DataStage. I think the issue is from the column collation. If my data is as simple as my example above, what column collation should i use for the format file? Currentyl, i have something like:
Code Snippet8.0
4
1 SYBCHAR 0 4 "|" 1 emp_id ""
2 SYBCHAR 0 4 "|" 2 emp_cand_id ""
3 SYBCHAR 0 4 "|" 3 emp_statusid ""
4 SYBCHAR 0 4 "
" 4 emp_type SQL_Latin1_General_CP1_CI_AS
I generated this through prompts given by BCP for each column. Then I changed the 2nd column values all to SYBCHAR and 0 prefix length..
I have a question for all the t-sql gurus.
I have a table with millions of rows, 1 particular attribute "FromPerson" contains a string
of email addresses, names, etc...
for example (formatting done by this window... not exists in database...completely cleaned to semi-colon delimited)
tomh@gmail.com; Snyder, John N.; jsnyder@yahoo.com; Miller, Jim; millerj@gmail.com; Tenbrow, Jack; Katie Winslow (can be x number of names, emails...)
as you can see... some of the delimited values match up with an email address, others have just names, or emails...
I have successfully split this into 1 row per value per say, but how do i get them to match up the values that do.
I think the splitting is really not helping the cause...
what i want is this...
tomg@hotmail.com tomg@hotmail.com
Snyder, John N. jsnyder@yahoo.com
Miller, Jim millerj@gmail.com
Tenbrow, Jack Tenbrow, Jack
Katie Winslow Katie Winslow
there could/can be other attributes, like datetime stamp, domain(if any)... etc... but I think I can add that
later...
any ideas?
Hello all,
I am trying customize the CSV rendering extension with different device information settings. I would loke to change the CSV extension to a Tab delimited output. However, whenever I add a new extension line to the RSReportServer.config file I run into problems.
1. What is the correct character to use in the delimiter to specify a Tab character? I seem to be unable to get this correct
2. Why when I override the name in the rendering extension does it still show up as the CSV (comma delimited) name in the export dropdown.
Here is my customized rendering extension.
<Extension Name="TXT" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<OverrideNames>
<Name>TXT (Tab Delimited Text File)></Name>
</OverrideNames>
<Configuration>
<DeviceInfo>
<Encoding>ASCII</Encoding>
<FieldDelimiter><![CDATA[#x9]]></FieldDelimiter>
<NoHeader>true</NoHeader>
</DeviceInfo>
</Configuration>
</Extension>
Thanks
Justin
I need to import comma delimited text files into sql tables. in one of the column, there is a comma in the string itself. e.g.
Cust_ID Name Phone Address1 Address2
Date that I have:
001,juia, anderson,4694568855,,Cedar Spring
The data does not have double quote as text qualifiers. but as you see, on the Name column, there is a comma, which is not a delimiter. can anybody give any suggestions on how i can deal with that? i would appreciate it so much.
thanks
I have a tab delimited file with 122 columns. Can any one let me know if there is a better way of parsing/extracting few columns (say about 15) from the file and loading it into a table using SSIS.
Hi
I am new to VB, and am looking to write some code to import a delimited (by a ~) .txt file into a SQL server table. It doesn't need to append, just to totally overwrtie the table. Is this possible? I have been looking at the Bulk Insert, but this doesn't seem to be quite right. Can anyone help?
Cheers,
S
Is it possible to get a comma delimited list of the views in a DB?
View 7 Replies View RelatedHow can I get a comma delimited list of the views in my db?
("view1","view2","view3",etc...)
Dear SQL,
Lets say I have a field: User_Names
and it can have more than one name
example: "Yovav,John,Shon"
How can I select all the records that has "Yovav" in them ?
I try some like this:
SELECT * FROM User_Table WHERE User_Names IN ('Yovav')
but it only works if the field User_Names only has "Yovav" in it with no extras...
is there some SQL function to tell if string is found in a field ?
Any hope 4 me ?
Hi I'm pretty new to using Microsoft Visual C# .NET and I want to upload a comma delimited text file from my local machine into a table in an sql server database through a web app. How would I go about programming this and what controls do I need? Any help would be much appreciated. Thanks in advance.
View 4 Replies View RelatedIs this possible? I find it hard to believe that this could be sooo difficult. I have a simple select stored procedure that has one parameter. My application is passing a comma delimited string of values to be used in the IN clause.
Ex: Where x In(@parametername)
the x column is an integer. How can one work around this???
Thanks!
Hi,
I have a complex query where each row in the final dataset is a
product.
However each product has a number of authors associated with it.
What I
would like to do is have a query/subroutine join the authors to the
product,
as a string:
ProductID
Title
Authors
1 The Sacred and the Profane John Rieggle, George
Alexi
2 Dancing
in the Dark Dan
Brown, Peter Kay, Paul
Dwebinski
Products
Table
==============
ProductID
Title
Authors
Table
=============
AuthorID
Name
Product Authors
Table
=====================
AuthorID
ProductID
Is this at all
possible?
Thanks
jr.
I am trying to build a dynamic where statement for my sql stored prcoedure
if len(@Office) > 0 select @strWhereClause = N'cvEh.chOfficeId in (select id from dbo.csfParseDeLimitedText( ' + @vchOffice + ',' + ''',''' + '))' + ' and ' + @strWhereClause
In this case users can enter comma delimited string in their search criteria. So if they enter we1, we2, we3 then my sql statement should look like
select @strWhereClause = cvEh.chOfficeId in (select id from dbo.csfParseDeLimitedText('we1', 'we2', 'we3'),',')
My csfParseDeLimitedText function looks like this
Create FUNCTION [dbo].[csfParseDeLimitedText] (@p_text varchar(4000), @p_Delimeter char(1))RETURNS @results TABLE (id varchar(100))ASBEGIN declare @i1 varchar(200)declare @i2 varchar(200)declare @tempResults Table (id varchar(100))while len(@p_text) > 0 and charindex(@p_Delimeter, @p_text) <> 0beginselect @i1 = left(@p_text, charindex(@p_Delimeter, @p_text) - 1)insert @tempResults select @i1select @p_text = right(@p_text, len(@p_text) - charindex(@p_Delimeter,@p_text))endinsert @tempResults select @p_text
insert @resultsselect *from @tempResultsreturnEND
My problem is it does not put quotes around the comma delimited stringso I want to put 'we1' , 'we2'. These single quotes are not coming in the dynamic sql statement. How can I modify my query so that single quotes around each entry should show up.
Any help will be greatky appreciated.
Thanks
I have a row in a SQL table that has 4 numerical values, separated by comma. I'd like to take this and make it 4 separate columns. Values are not always the same length, but are always delimited by commas.
Any ideas how I could do this in T-SQL?
Hi,
I have the following query:
SELECT *
FROM News INNER JOIN
Newsletters ON News.ID = Newsletters.fkNewsID
My problem is that fkNewsID can contain a comma delimited list of various IDs. Is there a way to properly do the join in this case?
I have a text file that is delimited by nulls. Any idea on the best way to get this into a SQL Server table?
View 9 Replies View RelatedI posted this originally in the incorrect forum I believe so I am reposting this in here which I believe is the proper place.
I need to move essentially a flat file from one server to another one and export it into a database on the second server. Does anyone have an easy process to accomplish this? I am currently at a loss. Any advice would be much appreciated.
Thanks
Hello
I have a table with column Options where each field contains a comma delimited list of ID numbers.
I want to select any records where a certain ID number appears in that list.
So something like:
SELECT * FROM Table t1
WHERE MyID IN (SELECT Options FROM Table t2 WHERE t1.ID = t2.ID)
But that gives me the error:
Syntax error converting the varchar value '39,20' to a column of data type int.
I feel I'm close though! Could anyone point me in the right direction?
Many thanks
Square