I'm in a flat file connection manager editor and I have a flat file where the columns are separated by a space. Does anyone know how to specify a space in the column delimiter option? I've tried {' '} and {s} but these don't work. Not sure what the syntax is for indicating a space. Thanks in advance
View 1 RepliesI am getting an error importing a csv file both using SSIS and SSMS. The csv is comma delimited with quotes for text qualifiers. The file gets partially loaded and then gives me an error stating The column delimiter for column "MyColumn" was not found. In SSIS it gives me the data row which is apparently causing the problem but when I look at the file in a text editor at the specific row identified the file has the comma delimiter and it looks fine. I am using SQL Server 2008.
View 9 Replies View RelatedHello Everyone,
Hope someone will be able to help me out here.
I have a text file exported from my DTS package and it requires an '!' as a custom column delimiter.
Does anyone have any idea how I can use the '!' mark instead of the Tab or Vertical Bar as my Column Delimiter?
Would appreciate any suggestions.
Thanks,
Kay
Hi,
is there any way to change the delimiter in a FlatFile Source Adapter dynamically? You can do that with the row delimiter but there is no expression for the column delimiter... Is there any other way besides changing the XML code?
Thanks,
hi guys,
i am working on a project witch involves creating packages on-the-fly to import data from txt/csv/xls files according to some definitions on the database.
so far, i have been doing fine.
now we are planning the ASP.net page that enables the customer to define the input file format that will be imported to the system. we want it to have the same listBox as the FlatFileConnectionManager Editor has to define some properties, such as - column delimeiter.
the code to set the column delimiter looks like this:
SSISRunTime.IDTSConnectionManagerFlatFile90 myFilecn = null;
myFilecn = (SSISRunTime.IDTSConnectionManagerFlatFile90)package.Connections["InputFileConnection"].InnerObject;
DtsConvert.ToConnectionManager90(package.Connections["InputFileConnection"]);
SSISRunTime.IDTSConnectionManagerFlatFileColumn90 col
col = myFilecn.Columns.Add(); //.....
string colDelimiter ="|" ; // it actually gets the data from the database... but it is the same thing
col.ColumnDelimiter = colDelimiter;
when we deal with the simple characters- "," , ";" , "|" ... we have no problems with setting the delimiter. but how can i set the delimiter to Tab? or {CR} ? {LF}?
i tried to look at the dtsx- XML , and i see that the column delimiter that is defined when i choose Tab is: _x007C_, but when i try to do something like this:
col.ColumnDelimiter = "_x007C_" ;
it doesn't work. the same happens when I try "{t}" or "Tab".
how do i solve this problem, and enable the user to select Tab as a column delimiter?
Thanks!
Good day everyone,
I have a package that reads data from a CSV file, transforms it and finally loads it in a destination DB table.
My current problem lies in the parsing of the input flat file. I shall illustrate it using a small example.
Source File:
P;Product-1;Short Description for product 1
P;Product-2;Short Description for product 2
Problem:
I configured the flat file connection manager to use semicolon as the column separator. But then I have received some sample flat files where I found that the semicolon might be sometimes used as content of a column data.
Possible Solutions:
I have thought about 3 different solution and I would like to get your feedback and recommendations about them.
Alternative 1:
Use a complex column delimiter, which wouldn't be used in the data.
Example:
P#~#Product-1#~#Short Description for product 1
P#~#Product-2#~#Short Description for product 2
Question 1:
- Is it possible to define such a customized column delimiter for the Flat File Connection Manager?
- If yes, how can I do this?
Alternative 2:
Use double quotes around the data, which the Flat File Source Adapter must somehow recognize and trim before pushing the data down the Data Flow.
Example:
"P";"Product-1";"Short Description for product 1"
"P";"Product-2";"Short Description for product 2"
Question 2:
- Is it possible to configure the Flat File Source Adapter to work as described?
- If yes, how can I do this?
Alternative 3:
Use a Script Component and write the needed code for parsing the Flat File.
Question 3:
- Do you have further suggestions/ideas for solving this parsing problem?
Thanks in advance and my regards,
Samar
I am building a generic SSIS where it takes a text source file and converts to a destination source file . However in the process I want to set the row delimiter and the column delimiter dynamically through the package variable. So that I can use it for any file transfer.
I saw the option for row delimiter in the file connection string property but did not see any column delimiter option.
I am trying to import data from a text file to SQL database through a DTS package. i am always getting "Column Delmimeter not found" error at record number 8000. i copied the 8000th record into the beginning of the file to test if there is any problem with data but still i got the error at 8000 th record. i know it was a problem before and was fixed with sp1. i installed SP4 and still gettting the same error.
View 4 Replies View RelatedHello
I created a table with column name "description" as varchar(8000). My doubt is if I am not storing 8000 characters in this column, will SQL Server use memory space needed for 8000 characters ? or will it use only the space that needs for my text. ?
Thanking You
Navaneeth
Hi,
I am declaring a table with 2 column Id, and Name.
Id is a integer column but Name is a Varchar.
I am declaring Name as VARCHAR(100) you can say that varchar(100) is more than enougf for a name column. But lets suppose ifn futuer user will enter data only till 100 characters but lets suppose i put or declare it as NAME VARCHAR(4000), That means a column requires only 100 memory space but i am giving 4000 to that column
So in case of SQL any drwbacks are there for this approach. Meaning my 3900 space will not be used so will it a wrong thing or we can declare it as ,,, it will put no effect???
or what are the drawback??
I have a huge table in it own tablespace (250 GB). I just deleted more thanhalf of the columns in that table, but I didn't see any change in allocatedspace. I did DBCC CLEANTABLE and DBCC SHRINKFILE, but nothing. MS also saysthat CLEANTABLE only reclaims space for columns with text, char datatypes.How can I get space back for integer, decimal, number datatypes? I'm justall out of ideas.
View 7 Replies View Related
I need a query that checks each row in a column (hardness) to see if the last character is a "space". The column holds numbers (73.45) but is a varchar 50 type. It will always be the 5th character but it would be nice to not make this assumption. Please help!!
Thanks.
I have a table in which there is a column called 'Forecast' of type 'int', that is null for about 25% of the rows in this table.
Will null value for this column consume storage bytes?
I hv dropped the useless columns for a table but the table size is remain unchanged by executing sp_spaceused. pls advise how to release table spaces.
thx
As I understood, if SPARSE is used on a column, which have many NULL marks, then the storage could be efficently used (we need less spaces to save NULL marks, hence a table which has many NULL marks with SPARSE property needs less storage than the same table, but without SPARSE. I created two table as follow:
/******* Table with Sparse ******/CREATE TABLE Sprstb(
unsprsid INT IDENTITY(1,1) NOT NULL,
Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Tel int NOT NULL,
adress nvarchar(60) SPARSE NULL)/***** Table without Sparse*******/CREATE TABLE Unsprstb(unsprsid INT IDENTITY(1,1) NOT NULL,Firstname varchar(20) NOT NULL,
Lastname varchar(20) NOT NULL,
Tel int NOT NULL,
address nvarchar(60) NULL)
I have populated the Sprstb with 5 Milion records. It needs 509,961 MB storage. Then I have copied this table into Unsprstb
SET IDENTITY_INSERT [dbo].[Unsprstb] ON
Insert [dbo].[Unsprstb](unsprsid,Firstname,Lastname,Tel,adress)
SELECT unsprsid,Firstname,Lastname,Tel, adress FROM [dbo].[Sprstb]
SET IDENTITY_INSERT [dbo].[Unsprstb] OFF
The Unssprstb need only  466,031MB !
That means the Table with SPARSE column need more storage, Why?Â
By the way, in table Sprstb column address has  1666198  Null mark (from 5000000)
hi,
This field comes from a flat file and sometimes own zero and sometimes own a empty position (after 'EUR'):
2006053000499236000005307700108287457080200408287452006052953990000000010000EUR
2006053000499236004414989200101423426004400501423422006052953990000000010000EUR0
[Column 12] == "0" ? [Column 12] : ?????????
TIA
I want to alter my all tables to change the name of all columns.
Â
I need this because all column names were created with space and I want to remove the space for future work .
For example – In Table Customer there is a column name [Cust_Id ] and I want to change it with [Cust_Id]
What is the easiest way to calculate the space (size in bytes) used by a BLOB column that is already stored in a particular row?
Thanks.
All,
I've converted datatypes in the past to something more appropriate in order to save space, and speed up the database, to great success. I'm in the middle of another such change, this one table in particular I am converting from [text] columns to varchar(max)'s, and varchar(1)'s to char(1)'s. I've made a duplicate of the table in question (which has about 25 text columns, and 20 varchar(1) columns), ported over the data, and ran 'sp_spaceused' and got the following results:
-- new table
name | rows | reserved | data | index_size | unused
Response | 39920 | 15384 KB | 15168 KB | 208 KB | 8 KB
-- old table
name | rows | reserved | data | index_size | unused
Response | 39920 | 124128 KB | 123696 KB | 200 KB | 232 KB
I didn't expect results quite this dramatic. Now to my question: Did I miss something measuring the difference? Is there something else, another method I should employ to determine the size differentials?
If this is right, it's outstanding, just unexpected. I would have been happy losing 10 or 20 MB.
I appreciate any insight anyone might be able to provide.
Best,
B.
Just fyi - here are the table schemas:
-- old table
CREATE TABLE [dbo].[Response](
[ResponseID] [varchar](30) NOT NULL,
[Term] [varchar](5) NOT NULL,
[Subject] [varchar](4) NOT NULL,
[Course] [varchar](4) NOT NULL,
[Sect] [varchar](3) NOT NULL,
[MidEndFlag] [varchar](3) NOT NULL,
[SID] [varchar](9) NOT NULL,
[TemplateID] [varchar](30) NOT NULL,
[LastModified] [datetime] NULL,
[College] [varchar](30) NULL,
[Classification] [varchar](10) NULL,
[CourseRequired] [varchar](3) NULL,
[ExpectedGrade] [varchar](10) NULL,
[Sex] [varchar](6) NULL,
[ItemAnswer1] [varchar](1) NULL,
[ItemComments1] [text] NULL,
[ItemAnswer2] [varchar](1) NULL,
[ItemComments2] [text] NULL,
[ItemAnswer3] [varchar](1) NULL,
[ItemComments3] [text] NULL,
[ItemAnswer4] [varchar](1) NULL,
[ItemComments4] [text] NULL,
[ItemAnswer5] [varchar](1) NULL,
[ItemComments5] [text] NULL,
[ItemAnswer6] [varchar](1) NULL,
[ItemComments6] [text] NULL,
[ItemAnswer7] [varchar](1) NULL,
[ItemComments7] [text] NULL,
[ItemAnswer8] [varchar](1) NULL,
[ItemComments8] [text] NULL,
[ItemAnswer9] [varchar](1) NULL,
[ItemComments9] [text] NULL,
[ItemAnswer10] [varchar](1) NULL,
[ItemComments10] [text] NULL,
[ItemAnswer11] [varchar](1) NULL,
[ItemComments11] [text] NULL,
[ItemAnswer12] [varchar](1) NULL,
[ItemComments12] [text] NULL,
[ItemAnswer13] [varchar](1) NULL,
[ItemComments13] [text] NULL,
[ItemAnswer14] [varchar](1) NULL,
[ItemComments14] [text] NULL,
[ItemAnswer15] [varchar](1) NULL,
[ItemComments15] [text] NULL,
[ItemAnswer16] [varchar](1) NULL,
[ItemComments16] [text] NULL,
[ItemAnswer17] [varchar](1) NULL,
[ItemComments17] [text] NULL,
[ItemAnswer18] [varchar](1) NULL,
[ItemComments18] [text] NULL,
[ItemAnswer19] [varchar](1) NULL,
[ItemComments19] [text] NULL,
[ItemAnswer20] [varchar](1) NULL,
[ItemComments20] [text] NULL,
[EssayQuestionAnswer1] [text] NULL,
[EssayQuestionAnswer2] [text] NULL,
[EssayQuestionAnswer3] [text] NULL,
[EssayQuestionAnswer4] [text] NULL,
[EssayQuestionAnswer5] [text] NULL,
CONSTRAINT [PK_Response] PRIMARY KEY CLUSTERED
(
[SID] ASC,
[TemplateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
-- new table
CREATE TABLE [Test].[Response](
[ResponseID] [varchar](30) NOT NULL,
[Term] [varchar](5) NOT NULL,
[Subject] [varchar](4) NOT NULL,
[Course] [varchar](4) NOT NULL,
[Sect] [varchar](3) NOT NULL,
[MidEndFlag] [varchar](3) NOT NULL,
[SID] [varchar](9) NOT NULL,
[TemplateID] [varchar](30) NOT NULL,
[LastModified] [datetime] NULL,
[College] [varchar](30) NULL,
[Classification] [varchar](10) NULL,
[CourseRequired] [varchar](3) NULL,
[ExpectedGrade] [varchar](10) NULL,
[Sex] [varchar](6) NULL,
[ItemAnswer1] [char](1) NULL,
[ItemComments1] [varchar](max) NULL,
[ItemAnswer2] [char](1) NULL,
[ItemComments2] [varchar](max) NULL,
[ItemAnswer3] [char](1) NULL,
[ItemComments3] [varchar](max) NULL,
[ItemAnswer4] [char](1) NULL,
[ItemComments4] [varchar](max) NULL,
[ItemAnswer5] [char](1) NULL,
[ItemComments5] [varchar](max) NULL,
[ItemAnswer6] [char](1) NULL,
[ItemComments6] [varchar](max) NULL,
[ItemAnswer7] [char](1) NULL,
[ItemComments7] [varchar](max) NULL,
[ItemAnswer8] [char](1) NULL,
[ItemComments8] [varchar](max) NULL,
[ItemAnswer9] [char](1) NULL,
[ItemComments9] [varchar](max) NULL,
[ItemAnswer10] [char](1) NULL,
[ItemComments10] [varchar](max) NULL,
[ItemAnswer11] [char](1) NULL,
[ItemComments11] [varchar](max) NULL,
[ItemAnswer12] [char](1) NULL,
[ItemComments12] [varchar](max) NULL,
[ItemAnswer13] [char](1) NULL,
[ItemComments13] [varchar](max) NULL,
[ItemAnswer14] [char](1) NULL,
[ItemComments14] [varchar](max) NULL,
[ItemAnswer15] [char](1) NULL,
[ItemComments15] [varchar](max) NULL,
[ItemAnswer16] [char](1) NULL,
[ItemComments16] [varchar](max) NULL,
[ItemAnswer17] [char](1) NULL,
[ItemComments17] [varchar](max) NULL,
[ItemAnswer18] [char](1) NULL,
[ItemComments18] [varchar](max) NULL,
[ItemAnswer19] [char](1) NULL,
[ItemComments19] [varchar](max) NULL,
[ItemAnswer20] [char](1) NULL,
[ItemComments20] [varchar](max) NULL,
[EssayQuestionAnswer1] [varchar](max) NULL,
[EssayQuestionAnswer2] [varchar](max) NULL,
[EssayQuestionAnswer3] [varchar](max) NULL,
[EssayQuestionAnswer4] [varchar](max) NULL,
[EssayQuestionAnswer5] [varchar](max) NULL,
CONSTRAINT [PK_Test_Response] PRIMARY KEY CLUSTERED
(
[SID] ASC,
[TemplateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
the text file format use the length of character to define the field
for example,
0001130130HAUT BAGES AVEROUS 03
9 chars <0001130130> is a field
1 char <H> is a field
20 chars <AUT BAGES AVEROUS 03> is a field
one record by one record store in db
no space, no symbol, no line break between each record
----------------------------------------------------------
I try bcp method, but some error happens. Please give me suggestions. thx
I run the following code in query analyzer.
BULK INSERT Chain.dbo.POLL59
FROM 'D:POLL59.DWN'
WITH (FORMATFILE = 'D:cp.fmt')
?????
it shows the error
Server: Msg 4839, Level 16, State 1, Line 1
Cannot perform bulk insert. Invalid collation name for source column 4 in format file 'D:cp.fmt'.
***
For your information
data file
000000011301220051222000192000000000011301320051222000030000000000019067420051222000000001<there are many space >
bcp.fmt file <I use tab to separate and use ascii>
8.0
4
1 SQLCHAR 0 4 "" 1 PLUEVT ""
2 SQLCHAR 0 9 "" 2 PLUSKU ""
3 SQLCHAR 0 8 "" 3 PLUFRD ""
4 SQLCHAR 0 9 "" 4 PLUPRC ""
!!!!
I try to edit collation name in Chinese_Taiwan_Stroke_CI_AS or others, but the error also happens.
I am dealing with what I believe is Oracle that is the source of a SQL View.
I am seeing a data type of Integer in the View, but I am not able to see what makes up that View. When I query the View, I can see that an Integer data type column is storing a blank space. I use ISNUMERIC(ColumnName) = 0 and there are a lot of rows that show as a zero length blank space, or text, or something. I just know that it is not an Integer.
I have attempted to CAST and Convert this value, but it will not. I have changed the data type on the table that is being inserted in too, and it still fails with a Conversion error. I have tried REPLACE(), but still the same conversion error.
I have another query regarding the graph/chart that i made in report builder. May i know on how to lessen the space between a column in my stacked bart chart.
View 5 Replies View RelatedHi,
I am trying to do this:
UPDATE Users SET uniqueurl = replaceAllEmptySpacesInUniqueURL('uniqueurl')
What would be the syntax.
Any help appreciated.
Thanks
I am generating a Report from Sql Data Source in Sql Server 2005 and viewing the Report in Report viewer control of Visual Studio 2005.
The data in the Data Source contains string with multiple spaces (for example €œ Test String €œ) but when they get rendered in Report viewer control, multiple spaces gets converted to single space €? Test String €œ.
I tried following solutions
1) Replacing spaces with €œ €?
2) Inserting <pre> tag before the string and </pre> tag after the string (Also tried <Pre> instead of <pre>)
But in all the cases result is same. The Report Viewer control is showing €œ €? instead of space and €œ<Pre>€? tag instead of preserving spaces.
Please provide me a solution so that spaces can be preserved in Report Viewer.
I am using the below script to get space alerts  and now i am interested in sending alerts  if for any drive space available is Less than 10% or 15%.. how to convert beelow code to find in %Â
Declare @Drives Varchar(20)
DECLARE @Spaces Varchar(50)
DECLARE @availableSpace FLOAT
DECLARE @alertMessage Varchar(4000)
DECLARE @RecipientsList  VARCHAR(4000);
CREATE TABLE #tbldiskSpace
[Code] ....
Hi.. I was doing a good maintenance on my DB and my trans log LDF keep growing until 30GB but my DB data file MDF is only 2GB. I found the two following method to reduce my log size.
Method 1: I used veritas to backup log file with truncate
Method 2: I used the shrink database option in Enterprises manager to shrink it (file chosen=log , use default option)
After doing that, I found my LDF log file is still about the same size=27GB but when I see clearly, from the shrink database windows, the log spaced used reduced to only 100MB, the allocation log space is still 27GB. Why? How to make the LDF smaller to be the around the same size as the space used 100MB?
This is driving me bananas. Can't find any info on this anywhere....SQL 2000 seems to replace double space with a single space when I seta varchar field to " " (2spaces), it only stores " " (1space). Whyon earth would microsoft do this? If I save 2 spaces - I WANT TO SEE2 SPACES!!!!Can anyone help? Is this a database setting? Is this due to usingvarchar?Any help appreciated.Colin Hale
View 2 Replies View RelatedHi
Please tell me which delimiter to use. Criterion: 1) The delimiter should appear as if it were space in the Back end 2) while running reports it should not dampen the lay out 3) The delimiter should not be an enterable character
Ex: i tried with chr(10) but while running reports, the characters after the delimiter are shifted to new line.
The delimiter that i use, should not create such undesirable effects.
Thanks in Advance
Hi!
Im trying to import some data from a Flat File Source, where a row delimiter is {LF} and column separator is SPACE.
Data looks like this:
GI$0c2 T08b 1 1 20060508 000655 6 8 8 c0a81f1f 1 1 1 00A 3 24206816 3 24206816 1 1 3 59910000 001 1 3 14730050 0 25 F10 XX 317148-131136 loop TG_MRB 0 M027 1 3 0 20060508 000655 0 3 59910000 SIP
This is the first record that generates around 41 columns and sorts data as it should, but if the second record is smaller the row delimiter {LF} is ignored and put into one of the columns untill all 41 columns from previous record are filled. It seems to me that columns separator has the priority over the row delimiter which is very wrong. :). If there is a {LF} in the file that should mean that it needs to be in a new row as a new record. I try to keep this all in a SQL 2005 package without using any additional software, i know there might be a solution with the scripting component, but would like to see if theres someone with the similar solution before i start writing any scripts. (i dont like parsing strings with scripts from bulky files:))
Thanks!
Sebastijan L.
hello!
i was on with DTS, dumping a flat file to SQL DB and i came across an error which says: "Too many columns...; found whitespaces.."
When I checked the row that caused this, I found out that entries with ""s are split into two columns possibly because the backslashes were read as tab delimiters..
I've tried other delimiters, but they do not seem to fit because most of the entries have them.
Please advise. Thanks!
Hello--
I need to Ç as my field delimiter and Æ as my row delimiter in bcp script. I use the script below but I dont get the right outcome- HELP
bcp databse.dbo.table out table.dat -S myserver -U myserid -P mypassword -c -tÇ -r"Æ"
Josephine
Josephine
Hi,
I have a problematic file. It's vertical-bar delimited, but the file conn mgr can't find ANY of the pre-defined row delimiter types.
The data looks like this:
col1|col2|col3|||||||||
With a bunch of vertical bars on the end of each line.
How should I handle this file?
Thanks!
Hello,
Somebody know how to reduce the space allocated for the transaction log space for my SQL_DB ?
3700 MB allocated but only 100 MB used and 3600 MB are free !
Transaction log properties :
Automatically grow file are filled
file growth by percent = 5%
maximum file size - restrict filegrowth = 3700 MB (we can't reduce it !)
Thank you for your precious help !
Khaix from Brussel.