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: Yesterday @ 2:15 AM
Points: 5,317, Visits: 12,354
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
Post #1440169
Posted Wednesday, April 10, 2013 3:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, December 8, 2014 11:49 AM
Points: 22, Visits: 129
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