Specifyin The Row Delimeter As Newline (Unix Style) In Bulkinsert Format File

Sep 28, 2007

Hi,

I am bulk inserting the data from a file into a table using the following format.



9.0
1
1 SQLCHAR 0 10 "
" 1 MSISDN SQL_Latin1_General_CP1_CI_AS

This works perfectly for me.

But my problem is that the firmat may not be generated in Windows and i can't user "
" as row delimeter.
It could be generated in Unix and want to use LF as the row delimeter.

Questions :

1) How do i specify this kind of new line (LF ) in formatfile?
2) Is there anyway that i can specify both the delimeters and let it use which ever is appropriate ?

Please provide some inputs on this??

Thank u so much
~Mohan

View 3 Replies


ADVERTISEMENT

How To Create UNIX Format Files Using Flat File Connection Manager?

Dec 17, 2007

Hi All,

How to create a text file in UNIX format using Flat File connection manager. By default when we create a connection manager for flat files it is taking "CRLF" as the delimeter.

I beleive the format of the file will be decided based on the control line feed character at the end of each row. There are different control line feeds for different operating systems.

CR - Mac OS (Carraige return)
LF - UNIX (Line Feed)
CRLF - Windows. (Carriage return Line Feed)

Correct me if i am wrong.

Thanks in advance.

View 3 Replies View Related

Unix, Windows For Unix, And Direct Read Of .mdf File This Is A True Story

Sep 13, 2007

First, let me start by saying I know the answer to this, but due to politics any answer I give will be viewed with some disdain and disbelief.

A Unix/Network [rtdpmin one our sister agencies is trying to solve a problem that really does not need to be solved, but ....

The sister agency uses Information Builder's WebFocus on Unix. When they try to read my data warehouse in SQL Server 2000 it times out on them (on their end and they claim they cannot fix it) (This is using the supplied WebFocus ODBC/JDBC driver for SQL Server). This has lead to accusations of us not letting them read the data.

After much gnashing of teeth over "if you do data and data analysis for a living, why do I have to show you how to set-up an ODBC connection" we have shown them how to connect to the data, read it, and transfer it using MS-ACCESS, EXCEL, and more importantly SPSS. This is not good enough because they cannot figure out how to put it into WebFocus.

Their network person has come-up with the brilliant idea of reading the .mdf directly by installing Windows for Unix on the Unix side and then just pointing to the .mdf file and "reading it ".

Unless I have missed something somwhere (I will admit possible), I know you need a driver to read MS-SQL and that driver in every example I have seen could care less about the physical location of the .mdf file. It wants to know what database, what server (ip or name) and what security/login to use.

Could someone give me a more "technical answer" or even Microsoft's party line so I do not have this person mucking around with my production server trying to acomplish the impossible.

View 5 Replies View Related

Urgent Help!-how To Format After Getting Rows? For Newline

Oct 8, 2004

while(@@FETCH_STATUS=0)
begin
print 'inside while'

set @BodyContents = @BodyContents+@gotclientName+@gotproductName
set @Body= @Body + @BodyContents + ','

fetch next from resultcursor into @gotclientName,@gotproductName

print 'going outside of while'
end --while end

Client Name Product Name
aaa test1
bbb test4

how to format row like above. currently i am getting
aaa,test1,bbb,test4

View 1 Replies View Related

Flat File With Fixed Length Header And No Delimeter

Aug 21, 2006

Hi,

I'm trying to extract data from a Flat File which is as fixed length as they come. The file has a header, which simply contains the number of records in the file, followed by the records, with no header delimeter (No CR/LF, nothing).

For example a file would look like the following:

00000003Name1Address1Name2Address2Name3Address3

So this has 3 records (indicated by the first 8 characters), each consisting of a Name and Address.

I can't see a way to extract the data using a flat file connection, unless we add a delimeter for the header (not possible at this stage). Am I wrong?

Any suggestions on possible solution would be much appreciated - I'm thinking Ill have to write a script to parse the file manually.

Thanks in advance,

Scott

View 6 Replies View Related

Import A XML File In SQL2005 DB Using BulkInsert?

Apr 15, 2008

Hi All,

I am dealing with a lot of XML file data that I load in a SQL 2005 database using Integration Services. Let me give you a high level idea of I how I do it.

The way I do it is that a) I create a blank Integration Services Project, then b) In the "Control Flow" tab I put the "Data Flow Task" by simply dragging "Data Flow Task" on to the "Control Flow" page. c) Then in the "Data Flow" tab I drag the "XML Source" as my data flow source, and drag "OLEB Destination" as my data flow destination; then I create a connection between the "XML Source" and "OLEDB Destination" and point the "XML Source" to my XML file, and I point the "OLEDB Destination" to the Database where I want to load the XML file. I configure the mappings etc if needed and run the package. This works fine for me. But if the XML file is large it takes hours to load the data. I am dealing with huge size XML files and I want an alternative in Inegration Services probably "Bulk Insert Task" but dont know how to use it.
Can someone please explain how I can use bulk insert as a fast alternative to load the data. Please list the steps.

Thans a milliom in advance.

Zee

View 3 Replies View Related

Format Style

Feb 21, 2008

i can't find the format style for mmddyyyy..

Funnyfrog

View 3 Replies View Related

URGENT: UNIX DATE FORMAT CONVERION TO SQL SERVER

May 9, 2001

I HAVE ONE COLUMN CONTAING THE INTEGER TYPE DATA COMING in UNIX DATE FORMAT. NEED TO CONVERT IT TO THE SQL SERVER DATE TIME FORMAT.

NOTE: UNIX IS STORING THE DATE IN SECONDS STARTING FROM 1970/1/1

ANYBODY CAN HELP ME TO SOLVE THIS PROBLEM.

e.g. The UNIX date time is 973101026 need to be converted to SQL DATE TIME

View 2 Replies View Related

Loading Flat File With Embedded Column, Text Delimiters And Newline

Jun 14, 2007

I have the misfortune of converting a DTS package to SSIS that loads a flat file that has a text fields that can contain embedded text delimiters ("), column delimiters (,) and even new lines (CR+LF i.e.,hex 0D 0A) in it. A sample line from the file is posted here, remember this is just one line though it shows as three lines, since the third field has embedded new line in it:



4,"Sam","EVP; MARKETING PRODUCT MANAGER ""Level I"",
Internet Sales / HELP
8005551212",100



If you open in excel it handles it perfectly showing four fields, as below, and this is what I want ( I cannot get it aligned right in the posting, just save the above line in *.csv and open to see what it should be):



4 Sam "EVP; MARKETING PRODUCT MANAGER ""Level I"", 100
Internet Sales / HELP
8005551212"



Now, SSIS errors on the embedded text delimiters and breaks into two or three lines based on which option I chose. I have tried few options based on postings in the forum:
a). Using undouble and undoubleout: Does not work when there are embedded column delimiters (,) in the text field
b). Modified undouble script posted by lvovg at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1718225&SiteID=1. Handles the embedded column delimiters (,) perfectly, but the embedded new lines (CR+LF i.e.,hex 0D 0A) are breaking it.
Since, I am using the ragged right format to read from the file then use transform script on the line by lvovg, the line is already broken by the ragged right format at the embedded new lines, hence does not work.



Right now I am stuck. Can someone please help (anyone from MS) ? I am already baffled at the amount of coding required to convert a very basic ( and working ) flat file load DTS package to SSIS. I am willing to persist bit longer to convert this to SSIS, before I give up and stick with DTS and wait for a fix / workaround.




Thanks.

View 7 Replies View Related

File Transfer From UNIX To NT.

Jul 4, 2001

Hi Friends,

Is there any way to transfer a flat file from UNIX to Windows NT through an NT batch script without using FTP (and vice verssa)?

Thanks & Regards,

Raj

View 4 Replies View Related

Read UNIX File From SQL Server

Feb 6, 2004

Is it possibel to run a DTS job to read a file from unix server?

View 4 Replies View Related

FILE DSN On UNIX To Connect To MSSQL

Sep 19, 2007

Hello experts,

I need to create a File DSN on AIX Unix server which can connect to MS SQL server 2005 on a windows machine.Can anyone please give me step by step procedure how to do it.Its an urgent requirement. Please help me.

Regards
Arvind L

View 3 Replies View Related

Generic Way To Style Reports (CSS Style)

Jun 9, 2006

I want to make a CSS Style solution for my reports. I have few ideas related to this, changing the report properties on the fly, but I am not sure for which I should go for.

- We know that we can put expression on reports almost everywhere, and can also call custom code for setting up those expressions. i.e.

Public Shared Function GetValue(ByVal Key As String) As String

Dim myDataReader As SqlDataReader
Dim mySqlConnection As SqlConnection
Dim mySqlCommand As SqlCommand

mySqlConnection = New SqlConnection("server=localhost;Trusted_Connection=yes;database=tempMIQB")
mySqlCommand = New SqlCommand("SELECT * FROM [Properties] Where PropertyName='" & Key & "'", mySqlConnection)
mySqlConnection.Open()
myDataReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection)

If myDataReader.Read() Then
GetValue = myDataReader.Item("PropertyValue").ToString
Else
GetValue = "None"
End If


End Function

and in textbox or any where I can say =Code.GetValue("BGColor") or from .NET dll.

For going further on with this idea I can create an .NET dll and get the list from database or XML file. (Please give suggestions for performance/scalability issue.)

- second idea is to write a custom application (script) which can go through all the reports and change the color and fonts and every thing.

- third is to use parameters and use Array.IndexOf method to search for the value, but in this case i need to add dataset to all reports and, I think we cannot access report properties i.e. Parameters from custom code, just to make a generic function to access parameter value.

View 1 Replies View Related

Convert Data File Type From Unix To Pc Using Stored Procedure?

Jul 14, 2006

Hi, I have a script written in ASP to load data file (.csv) to ms sql. In the script, I have a portion of script looks like taht :
.....
Do While NOT oInFile.AtEndOfStream  oOutFile.WriteLine Replace(oInFile.Readline, chr(13), vbcrlf)Loop
.....
After that, I will use a BULK INSERT to input data to ms sql.
I am wondering how do I convert each row (data) to vbcrlf in Stored Procedure? Coz' I did not compose the convertion part, and I got no error when running BULK INSERT, but no rows are inserted :( HELP!!!!
I guess it's because the file is not being converted into a correct format??
 

View 1 Replies View Related

Column Row Delimeter Problem

Feb 17, 2006

I'm trying to import a comma delimited text file into a SQL table-

The row delimiters I am assuming are {CR}{LF}

When I try to open up my file in Excel, the data file parses perfectly.

When I try to port it over in SSIS, I get an error:

"The column delimeter for column <my column> was not found."

"An error occurred while processing the file <my file> on data row 2076"

I've tried looking at that data row, and i am having a hard time finding anything wrong with the row.

Anybody know of any good ways to debug that?

View 1 Replies View Related

BULKINSERT

Feb 26, 2008

hi all,

can any one give me syntax example of how to use BULKINSERT

Thanx for any help

sql9

View 3 Replies View Related

BULKINSERT Problem

Jan 24, 2005

Dear All,

I am importing all the files from a particular folder to a table on my database KB. It is working perfectly if i use it on the same system where the DB exists and not working from
the network. Also this scritp is working perfectly on sqlserver7 and not working on sqlserver2000.

SQL2000 returns error near comma

set @query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'"
WITH ( FIELDTERMINATOR = ","(returns error here) ,ROWTERMINATOR = "")'



USE TESTDB

--Table Creation Starts here

Create table Account([ID] int IDENTITY PRIMARY KEY, Name Varchar(100),
AccountNo varchar(100), Balance money)

Create table logtable (id int identity(1,1),
Query varchar(1000),
Importeddate datetime default getdate())

--Table Creation ends here

---Stored Procedure Starts here

Create procedure usp_ImportMultipleFiles @filepath varchar(500),
@pattern varchar(100), @TableName varchar(128)
as
set quoted_identifier off
declare @query varchar(1000)
declare @max1 int
declare @count1 int
Declare @filename varchar(100)
set @count1 =0
create table #x (name varchar(200))
set @query ='master.dbo.xp_cmdshell "dir '+@filepath+@pattern +' /b"'
insert #x exec (@query)
delete from #x where name is NULL
select identity(int,1,1) as ID, name into #y from #x
drop table #x
set @max1 = (select max(ID) from #y)
--print @max1
--print @count1
While @count1 <= @max1
begin
set @count1=@count1+1
set @filename = (select name from #y where [id] = @count1)
set @query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "")'
--print @query
exec (@query)
insert into logtable (query) select @query
end

drop table #y

--sp ends here

Exec usp_ImportMultipleFiles 'c:myimport', '*.csv', 'Account'

If i use the above Exec like

Exec usp_ImportMultipleFiles '\kb-02C$MyImport', '*.csv', 'Account'

I am getting the following error:

Could not bulk insert because file '\kb-02C$MyImportAccess is denied.' could not be

opened.
Operating system error code 5(Access is denied.).

C Drive and MyImport folder is shared on system kb-02


Also If if execute this script it should insert the following line to logtable for all the available files on myimport folder.

How do i modify the following line in the above script to get the syntax given below:


set @query ='BULK INSERT '+ @Tablename + ' FROM "'+ @Filepath+@Filename+'"
WITH ( FIELDTERMINATOR = ",",ROWTERMINATOR = "")'

Required Syntax

BULK INSERT kbAccount FROM 'c:myimporta.csv' WITH ( FIELDTERMINATOR = ',' ,ROWTERMINATOR = '')

Would appreciate your valuable HELP.

thanking your valuable help in advance.
K006B

View 1 Replies View Related

BULKINSERT - Any GURU ?

Jan 2, 2008



Hi,

I am trying to import comma delimitted text files which contain data for any particular country.

I have created SSIS package to import data into sql server table. table structure is exactly same as text file except one additional column which is to identify the CountryID. Text files we recieved does not contain CountryID column.

I am using BULK INSERT Task in SSIS to import the data as text files are quite big (up to 1 GB). during processing I do determine the country id for that text file as file name contain countrycode which can be use for lookup and get the countryid.

cyn_bills_20071208_032242.txt

CYN is the country code and country table has got country id for it.

I am using Format file with BULK INSERT task as table has additional column which does not present in source file.
currently after inserting all rows into table i have to run an UPDATE statement to update the value of CountryID column which is very expensive some time taking more then 30 min for (30 million rows)

Can anyone tell me how to insert an expression when using BULK INSERT command / Task in SSIS. there must be a way to insert litterals using Bulk Insert.

Thanks in Advance.
Furrukh Baig

View 6 Replies View Related

Adapter To Convert CSV Format File To SAP IDOC Format

Nov 16, 2006

Hi All,

I am stuck at one place, where I have to convert CSV format file data into SAP IDOC format file. In SSIS we don't have any such SAP adapter (though we have .NET Data Provider for mySAP suite [SSIS SAP Adapter] but this is still not fully supported by Microsoft, plus it doesn't have feature to convert data into IDOC format) that can do this. Can someone here please provide me some pointers on any third party adapters available in market to do this job or if anyone has already developed some custom approach to achieve this task?


Your quick response on this is highly appreciated.

Regards,

Kuldeep Chauhan

View 2 Replies View Related

SSIS Undouble - Inline Double Quote And Column Delimeter Problem

Apr 24, 2007

We have problems with SSIS flat file adapter.

We try to import file which contains next sort of data:

"1";"ABC";5
"2";"A""BC""";5
"3";"A""B;C""";5

The resulting table must be:
f1 f2 f3
1 ABC 5
2 A"BC" 5
3 A"B;C" 5

How can we get this result?

p.s.
We tried to use "SQL Server SSIS Sample Component: UnDouble" but the result was unsuccesfull.

View 11 Replies View Related

Load Data With BulkInsert In DTS, How To Skip The First Line?

Dec 13, 2007

I can skip the first line with Transform Data Task, it look like can not skip the first line in BulkInsert.
But bulkinsert is faster, anybody can help?

Thanks

View 1 Replies View Related

XML Format File For Bulk Insert Of Text File With Fixed Length Columns

Jan 2, 2008

Hey All,

Similar to a previous post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=244646&SiteID=1), I am trying to import data into a SQL Table.

I am trying to program a small application that will import product data obtained through suppliers via CD-ROM. One supplier in particular uses Fixed width colums, and data looks like this:




Example of Data

0124015Apple Crate 32.12

0124016Bananna Box 12.56

0124017Mango Carton 15.98

0124018Seedless Watermelon 42.98
My Table would then have:
ProductID as int
Name as text
Cost as money

How would I go about extracting the data with an XML Format file? I am stumbling over how to tell it where to start picking up data for a specific column.
Is there any way that I could trim the Name column (i.e.: "Mango Carton " --> "Mango Carton")?

I don't know if it makes any difference, but I've been calling SQL from my code by doing this:




Code in C# Form

SqlConnection SqlConnection = new SqlConnection(global::SQLClients.Properties.Settings.Default.ClientPhonebookConnectionString);
SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.Text;
cmd.CommandText = "INSERT INTO PhonebookTable(Name, PhoneNumber) VALUES('" + txtName.Text.ToString() + "', '" + txtPhoneNumber.Text.ToString() + "')";
cmd.Connection = SqlConnection;

SqlConnection.Open();
cmd.ExecuteNonQuery();
SqlConnection.Close();
RefreshData();
I am running Visual Studio C# Express 2005 and SQL Server Express 2005.



Thanks for your time,


Hayden.

View 1 Replies View Related

Is It Possible To Write A Format File That Skips A Few Bytes Of Header In Data File?

Jul 23, 2005

Hi,I am trying to use BULK INSERT with format file. All of our data hasfew bytes of header in the data file which I would like to skip beforedoing BULK INSERT.Is it possible to write format file to skip these few bytes ofheader before doing BULK INSERT? For example, I have a 1 GB data filewith 1000 byte header. Except for first 1000 bytes, rest of the data isgood for BULK INSERT.Thanks in advance. Sorry if it is really a dumb question as I am newto BULK INSERT and practicing still.Bob

View 7 Replies View Related

Newline In C# + SQL?

Feb 26, 2008

Hi everyone!I'm be stuck by a simple issue about newline in C# and has relative with SQL. I wrote a SQL sentence like that:                        IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME =  'TG1' AND TYPE = 'TR')" +                          " DROP TRIGGER TG1" +                          " GO " +                          " CREATE TRIGGER TG1 ON FILES FOR DELETE AS DELETE table1 FROM" +                          " table1 INNER JOIN DELETED ON DELETED.ID = table1.ID" +                          " GO " +                          " DELETE FROM table2 WHERE ID = 'ID00001'It's executing ok in SQL but when execute programming then " GO " have to break newline (
or System.Environment.NewLine), However seem have not mean for SQL Execute becoz will be appear
in SQL sentence.  I'm not  have experience about GO and TRIGGER so have any idea for this problem! Thanks so much. 

View 2 Replies View Related

Help With Newline

Jun 25, 2007

Hey guys, how do I return a string from a stored proc that displays some of its text on a newline?

E.g.

"some text goes here
some more text goes here
etc etc etc"

View 4 Replies View Related

Analyse File Format By File Flat Connection

Jan 16, 2008

I have a txt file with format as following

MailAddress:Kienpt@ifi.local
DomainName:ETH2K

[Date]2007/12/27 15:02:50 [Operation]
[Date]2007/12/27 15:02:50 [Operation]
[Date]2007/12/27 15:02:50 [Operation]


I want to use File Flat Connection to analyse format of this file. And i want each record after analysing include 4 fields as folowing:

- MailAddress, DomainName, Date, Operation
(Mail Address and DomainName is same in each record)

Can you help me?

View 1 Replies View Related

How To Add A Newline Sequence

Apr 9, 2007

I have a SQL 2005 stored procedure to generate an email when passed parameters such as receipient, subject etc
 One of the paramteres passed to it is @body which is the body text of the message.  I want to be able to add a couple of blank lines and then some footer information.  This is working right now except I can't find the right way to add newlines into the string within the store procedure, so my footer information just tags right on after the bodytext.
I have tried but that literally adds the two characters  and n
Can anyone advise how to generate newlien sequences in T-SQL.
Regards
Clive

View 1 Replies View Related

Newline In Data

May 10, 2005

Hello,
I have data in a database table that has newline data in it.  It has a newline character in front of the text of the data, and for some reason, I can't figure out how I can replace it. I've tried entering in a return character between '' and I've tried trimming the text...  How do I represent a newline character to remove it from the text?
Thanks.

View 1 Replies View Related

Newline In String

Sep 21, 2006

hi all..

i have a text field which has to be shown in the result of a query execution as several lines..

eg: the result should be like this

8STK :: L/2
: M/2
: S/1

Original string is stored as "8STK :: L/2 : M/2 : S/1"

expect a positive reply soon..
thanx..

View 6 Replies View Related

Newline Character In Sqlserver

Jun 1, 2004

what is the newline character in sqlserver.
My problem is ,I have to display text (say "Hi abcd hhh") where is the newline.
But when i print this in sqlserver storedproc,Its displaying as it is instead of newlines.

please help

thx

View 2 Replies View Related

Newline Character In Query

Feb 28, 2002

Can I use a newline character in my query? I want to dynamically generate a statement, split on several lines. Simple example:

declare @cmd varchar(100)
select @cmd = 'select user_name(), getdate()'
select @cmd

The output will be: select user_name(), getdate()

Can I use something like this:
declare @cmd varchar(100)
select @cmd = 'select user_name(), getdate()'
select @cmd

so as to get an output like this:
select user_name(),
getdate()

Thanks.

View 1 Replies View Related

Redirect URI Cannot Contain Newline Characters

Apr 25, 2005

Hi i get the above error when moving from one page to another. the following code is used. Can u help

Response.Redirect("Applicationform2.aspx?Id="+StudentID.Text+"&nam="+Name.Text+"&surNa="+ SrName.Text +"&DOB="+Ddate+ "&addr="+address.Text+"&pCode="+postal.Text+"&Cntry="+country.Text+"&email="+email.Text+"&ph="+phone.Text);

i dont understand the problem, can u help resolve the problem

View 1 Replies View Related

How Can I Put A Newline Within A String Column?

Apr 20, 2006

I am using Derived Column Transformation Editor. I have 3 string values which I would like to combine them into one string and have a newline character inbetween the 3 strings. I cannot see a Char() function similar to TSQL to use for this purpose. I thought about creating a Variable but even to that I don't know how I can assign a newline character.

Any ideas?

View 5 Replies View Related







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