SOLVED

Issues with Text to Column

%3CLINGO-SUB%20id%3D%22lingo-sub-2440911%22%20slang%3D%22en-US%22%3EIssues%20with%20Text%20to%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2440911%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20I%20am%20having%20is%20figuring%20out%20which%20setting%20I%20need%20to%20change%20in%20order%20to%20prevent%20the%20following%20scenario%20from%20happening.%20Please%20keep%20in%20mind%20this%20report%20has%20approx%2018K%20rows%20and%2065%20Columns%2C%20and%20I%20never%20encountered%20this%20issue%20with%20my%20previous%20laptop%20which%20was%20a%20Dell%20vs%20a%20Lenovo(current).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20what%20happens%20is%20that%20I%20pull%20the%20raw%20file%20that%20is%20produced%20in%20a%20CSV%20format%2C%20select%20column%20A%2C%20click%20text%20to%20column%2C%20Choose%20%22Delimited%22%2C%20select%20other%20and%20use%20%7C%20as%20the%20delimiter%2C%20and%20then%20complete%20it%20with%20finish.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20error%20that%20is%20then%20prompted%20is%20%22There's%20already%20data%20here.%20Do%20you%20want%20to%20replace%20it%3F%22%3C%2FP%3E%3CP%3E-%20This%20error%20never%20populates%20when%20using%20the%20Dell%20(same%20office%20suite%20as%20the%20lenovo)%3C%2FP%3E%3CP%3EIf%20I%20select%20%22OK%22%20then%20the%20report%20then%20moves%20it%20to%20column%20format%20however%20the%20data%20gets%20changed%20as%20it%20appears%20that%20a%20few%20of%20the%20rows%20were%20moved%20over%20by%202%20columns%20and%20other%20data%20has%20been%20plugged%20in%20the%20first%202%20columns%20from%20a%20different%20row.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHas%20anyone%20else%20ever%20experienced%20this%2C%20How%20do%20I%20fix%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*I%20cannot%20share%20images%20as%20the%20data%20holds%20sensitive%20data%20*%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2440911%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2440967%22%20slang%3D%22en-US%22%3ERe%3A%20Issues%20with%20Text%20to%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2440967%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1077055%22%20target%3D%22_blank%22%3E%40Vaterraz%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDoes%20it%20make%20a%20difference%20if%20you%20tick%20the%20check%20box%20'Treat%20consecutive%20delimiters%20as%20one'%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0504.png%22%20style%3D%22width%3A%20507px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F288156iBB629FD5D8E165DE%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0504.png%22%20alt%3D%22S0504.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2441001%22%20slang%3D%22en-US%22%3ERe%3A%20Issues%20with%20Text%20to%20Column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2441001%22%20slang%3D%22en-US%22%3EYou%20are%20awesome%2C%20that%20worked!!%20I%20can't%20believe%20I%20kept%20overlooking%20this%20box.%3CBR%20%2F%3EThank%20You%3C%2FLINGO-BODY%3E
New Contributor

Hello All,

 

The issue I am having is figuring out which setting I need to change in order to prevent the following scenario from happening. Please keep in mind this report has approx 18K rows and 65 Columns, and I never encountered this issue with my previous laptop which was a Dell vs a Lenovo(current). 

 

So what happens is that I pull the raw file that is produced in a CSV format, select column A, click text to column, Choose "Delimited", select other and use | as the delimiter, and then complete it with finish.

 

The error that is then prompted is "There's already data here. Do you want to replace it?"

- This error never populates when using the Dell (same office suite as the lenovo)

If I select "OK" then the report then moves it to column format however the data gets changed as it appears that a few of the rows were moved over by 2 columns and other data has been plugged in the first 2 columns from a different row. 

 

Has anyone else ever experienced this, How do I fix this?

 

*I cannot share images as the data holds sensitive data *

 

4 Replies
best response confirmed by Vaterraz (New Contributor)
Solution

@Vaterraz 

Does it make a difference if you tick the check box 'Treat consecutive delimiters as one'?

 

S0504.png

You are awesome, that worked!! I can't believe I kept overlooking this box.
Thank You

@Hans Vogelaar Actually upon further testing of the file it appears that the text to column deletes the header when selecting that box and shifts the data into the incorrect columns, such as cash now reporting in the date field and dates reporting in the unique identifier column

@Vaterraz 

In that case, the data file now has a different structure.

Insert enough columns to the right of the data column.

Then untick the 'Treat consecutive delimiters as one' check box in the Text to Columns wizard.