April 8, 2008 at 3:56 am
Dear Experts,
I have one text file.I have to split this file in the below format.
Content of the File:
50,Karthik,1982,+91-044-994012345,5000.00
60,Vijay,1974,+91-044-998012345,8000.00
70,Arun,1992,+91-044-992012345,6500.00
Expected Output:
50 Karthik 1982 +91-044-994012345 5000.00
60 Vijay 1974 +91-044-998012345 8000.00
70 Arun 1992 +91-044-992012345 6500.00
What i did, just imported the text file into a table called 'Imp_Text'.
Thanks in advance!
karthik
April 8, 2008 at 4:01 am
I am using Sql2000.
karthik
April 8, 2008 at 4:40 am
You can create a table in which you want to load the data into, e.g.:
CREATE TABLE mytable
(
id INT,
name VARCHAR(100),
year INT,
phone VARCHAR(100),
somenumber DECIMAL
)
and then use the bcp utility (http://msdn2.microsoft.com/en-us/library/aa174646(SQL.80).aspx) to load the data
e.g. fromt eh command line where the data file is stored:
bcp tempdb.dbo.mytable in mydata.dat -T -c -t,
Regards,
Andras
April 8, 2008 at 4:50 am
Andras,
I already did what you have mentioned.
TableName: Imp_Text
Remarks
50,Karthik,1982,+91-044-994012345,5000.00
60,Vijay,1974,+91-044-998012345,8000.00
70,Arun,1992,+91-044-992012345,6500.00
Now i want to split it in the below format.
50 Karthik 1982 +91-044-994012345 5000.00
60 Vijay 1974 +91-044-998012345 8000.00
70 Arun 1992 +91-044-992012345 6500.00
Thanks for your help !
karthik
April 8, 2008 at 5:17 am
karthikeyan (4/8/2008)
Andras,I already did what you have mentioned.
TableName: Imp_Text
Remarks
50,Karthik,1982,+91-044-994012345,5000.00
60,Vijay,1974,+91-044-998012345,8000.00
70,Arun,1992,+91-044-992012345,6500.00
Now i want to split it in the below format.
50 Karthik 1982 +91-044-994012345 5000.00
60 Vijay 1974 +91-044-998012345 8000.00
70 Arun 1992 +91-044-992012345 6500.00
Thanks for your help !
Sorry, I must have misunderstood you. Just to make sure I understand you: you have bcpd in the data into a table, this table has one column, and it contains all the values for an entire row. You would like to separate these into the individual columns? Assuming that this is the case, I'd still suggest to use the bcp command to import the rows and use "," as the separator (bcp will put the individual parts separated by comma to the relevant columns, so you would not need to split the text row.
Regards,
Andras
April 8, 2008 at 5:23 am
Can you give me the syntax of BCP ?
karthik
April 8, 2008 at 5:24 am
Also can you help me to achieve the same task with Sql query ?
karthik
April 8, 2008 at 5:33 am
[font="Verdana"]
karthikeyan (4/8/2008)
Andras,I already did what you have mentioned.
TableName: Imp_Text
Remarks
50,Karthik,1982,+91-044-994012345,5000.00
60,Vijay,1974,+91-044-998012345,8000.00
70,Arun,1992,+91-044-992012345,6500.00
Now i want to split it in the below format.
50 Karthik 1982 +91-044-994012345 5000.00
60 Vijay 1974 +91-044-998012345 8000.00
70 Arun 1992 +91-044-992012345 6500.00
Thanks for your help !
Using which tool or command did you imported the text file? If not through the DTS, try to import the file through it. Its a step by step process. i.e. Data Source, Data Destination, Destination object etc.
Thanks,
Mahesh
[/font]
MH-09-AM-8694
April 8, 2008 at 5:55 am
Yes,I used DTS import/export wizard.
karthik
April 8, 2008 at 6:11 am
[font="Verdana"]
karthikeyan (4/8/2008)
Yes,I used DTS import/export wizard.
Then whats the problem dude? Tell the DTS comma is the delimeter in the file, so it will separate the fields and will treat them as columns. So did you get your desired o/p?
Now i want to split it in the below format.
50 Karthik 1982 +91-044-994012345 5000.00
60 Vijay 1974 +91-044-998012345 8000.00
70 Arun 1992 +91-044-992012345 6500.00
I didn't get this. Where do you wants to split this data? Give some more details on this.
Mahesh[/font]
MH-09-AM-8694
April 8, 2008 at 6:57 am
yes you are correct,we can seperate it in the DTS itself.
But i want to do this with sql query.
karthik
April 8, 2008 at 7:33 am
karthikeyan (4/8/2008)
yes you are correct,we can seperate it in the DTS itself.But i want to do this with sql query.
Well, in this case you will need to parse the contents of each individual row. You could do something like the following (for each row)
DECLARE @inputList VARCHAR(1000)
SET @inputList = '50,Karthik,1982,+91-044-994012345,5000.00'
DECLARE @part VARCHAR(50),
@pos INT
SET @inputList = LTRIM(RTRIM(@inputList)) + ','
SET @pos = CHARINDEX(',', @inputList, 1)
IF REPLACE(@inputList, ',', '') <> ''
BEGIN
WHILE @pos > 0
BEGIN
SET @part = LTRIM(RTRIM(LEFT(@inputList, @pos - 1)))
IF @part <> ''
BEGIN
PRINT @part -- do something with the individual part
END
SET @inputList = RIGHT(@inputList, LEN(@inputList) - @pos)
SET @pos = CHARINDEX(',', @inputList, 1)
END
END
where I print the result you can cast to a relevant data type, keep track of the current column number, insert it into a different table, etc. The basic idea is to use CHARINDEX, and breakup your string.
This is much more painful to do than just specify a "," separator to DTS, bcp, SSIS, or whatever 🙂
Regards,
Andras
April 8, 2008 at 10:06 pm
[font="Verdana"]
karthikeyan (4/8/2008)
yes you are correct,we can seperate it in the DTS itself.But i want to do this with sql query.
If this is so, I would suggest do it at front end. In ASP .Net / VB .Net you can do it with StreamReader object. In a loop read each line of the file, until EOF, with StreamReader object. Declare variable for each column so that you can store values of each comma separated value. When you read the complete line, insert the values of these variable in table. After finishing the all stuff, close the StreamReader object.
And at the end, in SQL Query Analizer, you will get your desired output through Select * From {Table Name}
Let us know.
Mahesh
[/font]
MH-09-AM-8694
April 9, 2008 at 7:04 am
karthikeyan (4/8/2008)
yes you are correct,we can seperate it in the DTS itself.But i want to do this with sql query.
Karthik... all of the columns in your file are comma separated. Absolutely the fastest way to get this data into a table that looks like the file is to (obviously) create the table first, then use BULK INSERT to import the data. BULK INSERT will import more than 5 million 20 column rows in 60 seconds.
Now, here somes the hard part for you... Lookup BULK INSERT in Books Online... you really need to read about this.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 9, 2008 at 2:26 pm
karthikeyan (4/8/2008)
yes you are correct,we can seperate it in the DTS itself.But i want to do this with sql query.
Why?
The best way to split apart imported data is as you import it. The import tools are designed to do this kind of processing. The SQL engine is not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply