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

  • 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

  • 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

  • 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

  • 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?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

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

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