I need the code part for splitting the row into different columns?

Question

Post reply

I need the code part for splitting the row into different columns?

andrewalex.r

Old Hand

Points: 328
More actions
April 8, 2013 at 10:12 pm

#291929

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_IDEmployee_IDIn_TimeOut_TimeRemarksEntry_Date
17921Task Completed2013-04-08
181022Good2013-04-08
19819Not Bad2013-04-08
110718Bad2013-04-08
111921Excellent2013-04-08
112724Task Completed2013-04-08
113823In Progress2013-04-08

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

looks like the results from a csv/txt file?

Tried using SSIS and selecting "First column is headers", delimit it properly to assign columns?

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

andrewalex.r Old Hand Points: 328 More actions · Answer 2

Company_ID Employee_ID In_Time Out_Time Remarks Entry_Date

17921Task Completed 2013-04-08

181022Good 2013-04-08

19819Not Bad 2013-04-08

110718Bad 2013-04-08

111921Excellent 2013-04-08

112724Task Completed 2013-04-08

113823In Progress 2013-04-08

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

andrewalex.r (4/8/2013)
Company_ID Employee_ID In_Time Out_Time Remarks Entry_Date
17921Task Completed 2013-04-08
181022Good 2013-04-08
19819Not Bad 2013-04-08
110718Bad 2013-04-08
111921Excellent 2013-04-08
112724Task Completed 2013-04-08
113823In 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?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ronang SSC Veteran Points: 248 More actions · Answer 4

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