Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

I need the code part for splitting the row into different columns? Expand / Collapse
Author
Message
Posted Monday, April 8, 2013 10:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 5, 2013 11:56 PM
Points: 12, Visits: 29
1 : 7 ; 9 : 21:Task Completed:2013-04-08, 1 : 8 ; 10 : 22:Good:2013-04-08, 1 : 9 ; 8 : 19:Not Bad:2013-04-08, 1 : 10 ; 7 : 18:Bad:2013-04-08, 1 : 11 ; 9 : 21:Excellent:2013-04-08, 1 : 12 ; 7 : 24:Task Completed:2013-04-08, 1 : 13 ; 8 : 23:In Progress:2013-04-08

--------------------------------------------------------------------
I need the above values as the below table format,pls help me

------------------------------------------------------------------
Company_ID Employee_ID In_Time Out_Time Remarks Entry_Date
1 7 9 21 Task Completed 2013-04-08
1 8 10 22 Good 2013-04-08
1 9 8 19 Not Bad 2013-04-08
1 10 7 18 Bad 2013-04-08
1 11 9 21 Excellent 2013-04-08
1 12 7 24 Task Completed 2013-04-08
1 13 8 23 In Progress 2013-04-08


Post #1440132
Posted Monday, April 8, 2013 11:43 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 2:39 AM
Points: 1,880, Visits: 2,846
looks like the results from a csv/txt file?
Tried using SSIS and selecting "First column is headers", delimit it properly to assign columns?


----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #1440149
Posted Monday, April 8, 2013 11:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, May 5, 2013 11:56 PM
Points: 12, Visits: 29
Company_ID Employee_ID In_Time Out_Time Remarks Entry_Date
1 7 9 21 Task Completed 2013-04-08
1 8 10 22 Good 2013-04-08
1 9 8 19 Not Bad 2013-04-08
1 10 7 18 Bad 2013-04-08
1 11 9 21 Excellent 2013-04-08
1 12 7 24 Task Completed 2013-04-08
1 13 8 23 In Progress 2013-04-08
Post #1440153
Posted Tuesday, April 9, 2013 12:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:16 AM
Points: 5,078, Visits: 11,856
andrewalex.r (4/8/2013)
Company_ID Employee_ID In_Time Out_Time Remarks Entry_Date
1 7 9 21 Task Completed 2013-04-08
1 8 10 22 Good 2013-04-08
1 9 8 19 Not Bad 2013-04-08
1 10 7 18 Bad 2013-04-08
1 11 9 21 Excellent 2013-04-08
1 12 7 24 Task Completed 2013-04-08
1 13 8 23 In Progress 2013-04-08


Are you saying that the first post shows a single row (in a text file?) and that you want to create a text file that looks like this?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1440169
Posted Wednesday, April 10, 2013 3:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 15, 2014 4:04 AM
Points: 22, Visits: 123
Hi

It seems like you need to clean up your delimiters and there are generally two:
1. Row delimiter
2. Column delimiters

Your row delimiter seems to be a comma (,) but your column delimiters seem to be mixed between a colon (:) and a semi colon (;) which I suspect is why you're having an issue attempting to split this into a proper columns and records. If this is your issue then:
1. Standardise all column delimiters by using a text editing programme (Notepad etc). I usually use the Replace function to perform this quickly. My suggestion is to use pipe (|) delimiters.
2. Standardise your row delimiters.
3. Attempt to import data using the SQL Import/Export wizard to import to target table
4. If 3. fails then use BCP to perform the Import into a new SQL table (This rarely fails)
5. Using the Insert Into...Select from move the data from the new SQL table to the original target table.
6. Step 5 might require some data transformation.

I generally find it easier to work with data once the data is in SQL Server.

I hope this helped.
Regards,
Ronan
Post #1440711
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse