Help fix Extra Spaces and other errors

[desibouy]

Hi,

So I've been working on like 20 different CSV files, these are all products we sell, the task was to find specifications for each product, the specifications columns could range from 3 to 20. Some CSV files contain 1500 products others 100, different CSV because of the different categories. Thinking around 7000 products if not more.

So now it's time to review these files looking for errors, spelling mistakes, and anything else.
Heres what I found below (few examples)

Incontinence Category - Full.csv
	AA
1	Size
2	x-Large
3	X-Large
4	X -Large
5	X-Large
Incontinence Category - Full

Row 4 being correct.

Incontinence Category - Full.csv
	AB
1	Waist/Hip Size
2	59"-64"
3	59" -64"
4	59" - 64"
Incontinence Category - Full

Row 3 being correct.

Incontinence Category - Full.csv
	AC
1	Weight
2	18 lbs.
3	18lbs
4	18 lb
5	18 lbs
Incontinence Category - Full

Row 4 being correct.

Incontinence Category - Full.csv
	AH
1	Backsheet
2	Cloth-Like
3	Cloth-like
4	Cloth-like
Incontinence Category - Full

Row 3 being correct.

Catagory file.xlsx
	B
14	Stethoscopes
15	Thermometers
16	Pulse Oximeters
Diagnostic Products

Leading and trailing spaces



Please can anyone help me with these errors? Not sure if you can get it to spell check them too.

Much appreciated & Thanks in advance.

 

[Radoslaw Poprawski]

1. L-ALT,A, PN, F, F
2. Select the folder with ur files.
3. Load and transform
4. if everything is in 1 column as in shown example then append it all, if not load them separetly.
5. I'm not sure what exactly do you want, so PQ can:
5.1 Trim text (fully, Left, Right side)
5.2 Split the text by number-to-digit or digit-to-number like in 18lbs example
5.3 split the text by delimiter

here it would be simply "-" sign (with no qutes) then trim both columns then merge them by either "-" or " - " delimiters or even "ThisIsMyDelimiter"
5.4 PQ can also remove all duplicates from a set of data leaving only unique values. This however should be done AFTER doing all above cleanups. cos "-text: is not equal to "- text"or " -text"

 

[desibouy]

1. L-ALT,A, PN, F, F
2. Select the folder with ur files.
3. Load and transform
4. if everything is in 1 column as in shown example then append it all, if not load them separetly.
5. I'm not sure what exactly do you want, so PQ can:
5.1 Trim text (fully, Left, Right side)
5.2 Split the text by number-to-digit or digit-to-number like in 18lbs example
5.3 split the text by delimiter View attachment 41672here it would be simply "-" sign (with no qutes) then trim both columns then merge them by either "-" or " - " delimiters or even "ThisIsMyDelimiter"
5.4 PQ can also remove all duplicates from a set of data leaving only unique values. This however should be done AFTER doing all above cleanups. cos "-text: is not equal to "- text"or " -text"

I'm sorry but I've never been so confused in my life I understood 5.3 by using the Text to Columns and then using the delimiter "-" and then clean the text but then wouldn't I have to contact/join the text again?

 

[Radoslaw Poprawski]

text to columns? wow that's as ancient as it can get... i think only 5.4 floppy diskscan be older...
I'm talking about the power query solution and the keyboard shortcut opens the filepath searcher for power query.

 

[desibouy]

Lol, well I should have mentioned that I'm not an Excel Pro, judging by your comment below I'm probably still stuck in 1999 or something lol. The Most I've done is using formula's else I usually come here to ask for help.

I've never heard of Power Query or those pesky shortcuts. Sorry.

 

[Radoslaw Poprawski]

or the "pesky shortcut" version:
ALT:

A:

PN:

F:

F:

any way, if you provide bigger data sample i can help with power query.
since u never heard of PQ then i would need a sample file for each CSV file (can be rarred or zipped.)

 

[desibouy]

Hi,

Sorry got busy here

Here is a larger data sample. There might not be any errors in this sample (maybe spelling) but I've just opened the file and just took a dump.
Thanks again.

Ostomy - Full.csv
	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO
1	Volume	Size	Color	Match	Material	Application	Type	Compatible	Opening	Flange Size	Stoma	Shape	Length	Panel	Barrier	Sterile	Absorbency	User	Clouser	Latex free
2		One Size	Clear			Low Pressure Adaptor	Barrier Ring	Colostomy		4"		Round		Disposable		No		Adult		Yes
3		One Size	Clear			Low Pressure Adaptor	Barrier Ring	Colostomy		2-1/4"		Round		Disposable		No		Adult		Yes
4		One Size	Clear			Low Pressure Adaptor	Barrier Ring	Colostomy		2-3/4"		Round		Disposable		No		Adult		Yes
5		One Size	Clear			Low Pressure Adaptor	Barrier Ring	Colostomy		1-3/4"		Round		Disposable		No		Adult		Yes
6	90 mm		Opaque			AutoLock System	Ostomy Rod	Colostomy		4"						Yes				Yes
7	90 mm		Opaque			AutoLock System	Ostomy Rod	Colostomy		4"						Yes				Yes
8	65 mm		Opaque			AutoLock System	Ostomy Rod	Colostomy		2-1/4"						Yes				Yes
9	65 mm		Opaque			AutoLock System	Ostomy Rod	Colostomy		2-1/4"						Yes				Yes
10		One Size			Soft	Pouch Clamp	Clip Clouser	Drainable Pouch				Straight				No		Adult	Tail	Yes
11		One Size			Soft	Pouch Clamp	Clip Clouser	Drainable Pouch				Straight				No		Adult	Tail	Yes
12		One Size			Hypoallergenic	Stoma Cover	Waterproof Cap	Urostomy	Style FX		1-1/8"	Round End		Disposable			Medium			Yes
13		One Size			Hypoallergenic	Stoma Cover	Waterproof Cap	Urostomy	Style FR		7/8"	Round Center		Disposable			Medium			Yes
14		One Size			Hypoallergenic	Stoma Cover	Waterproof Cap	Urostomy	Style F-4		7/8"	Round Center		Disposable			Medium			Yes
15		One Size			Hypoallergenic	Stoma Cover	Waterproof Cap	Urostomy	Style F-3		3/4" to 1-1/4"	Rectangular Center		Disposable			Medium			Yes
16		One Size			Hypoallergenic	Stoma Cover	Waterproof Cap	Urostomy	Style F-23		1-1/8"	Round End		Disposable			Medium			Yes
17		One Size			Hypoallergenic	Stoma Cover	Waterproof Cap	Urostomy	Style F-2		3/4" to 1-1/4"	Oval center		Disposable			Medium			Yes
18		One Size			Hypoallergenic	Stoma Cover	Waterproof Cap	Urostomy	Style F-1		1-1/8"	Round Center		Disposable			Medium			Yes
19		One Size			Hypoallergenic	Stoma Cover	Waterproof Cap	Urostomy	Style DM			Round Center		Disposable			Medium			Yes
20		One Size			Hypoallergenic	Stoma Cover	Waterproof Cap	Urostomy	Style DE		7/8"	Round Center		Disposable			Medium			Yes
21		One Size				Adhesive	Porous Patch		Pre-Cut		2-1/2" to 3-3/4"			Disposable			No Absorbency			Yes
22		One Size				Adhesive	Porous Patch		Pre-Cut		2-1/2" to 3-3/4"			Disposable			No Absorbency			Yes
23		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				7/8"	Round Center		Disposable			Medium			Yes
24		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				1-1/8"	Round Center		Disposable			High			Yes
25		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				7/8"	Round Center		Disposable			Medium			Yes
26		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				3/4" to 1-1/4"	Round Center		Disposable			High			Yes
27		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				1-1/8"	Round Center		Disposable			Medium			Yes
28		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				1-1/8"	Round Center		Disposable			Medium			Yes
29		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				1-1/8"	Round Center		Disposable			Medium			Yes
30		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				1-1/8"	Round Center		Disposable			Medium			Yes
31		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				1-1/4"	Round Center		Disposable			Medium			Yes
32		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				1-1/4"	Round Center		Disposable			Medium			Yes
33		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				1-1/8"	Round Center		Disposable			Medium			Yes
34		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				7/8"	Round Center		Disposable			Medium			Yes
35		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				7/8"	Round Center		Disposable			Low			Yes
36		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				1-1/8"	Round Center		Disposable			High			Yes
37		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				1-1/8"	Round Center		Disposable			High			Yes
38		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				1-1/2"	Round Center		Disposable			High			Yes
39		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				1-1/8"	Round Center		Disposable			High			Yes
40		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				3/4" to 1-1/4"	Oval Center		Disposable			High			Yes
41		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				1-1/8"	Round Center		Disposable			Medium			Yes
42		One Size	Beige		Hypoallergenic	Stoma Cover	Waterproof Cap				7/8"	Round Center		Disposable			Medium			Yes
43		One Size				Skin Barrier	Ring	Ostomy Pouch			1-1/2" to 2-3/16"	Oval			Flextend	No		Adult		Yes
44		One Size				Skin Barrier	Ring	Ostomy Pouch			1-3/16" to 1-7/8"	Oval			Flextend	No		Adult		Yes
45		One Size				Skin Barrier	Ring	Ostomy Pouch			7/8" to 1-1/2"	Oval			Flextend	No		Adult		Yes
46		One Size			Hypoallergenic	Stoma Cover	Waterproof Cap		Style G-3		1-1/2" to 1-1/2"	Round								Yes
47		One Size			Hypoallergenic	Stoma Cover	Waterproof Cap				1-1/2" to 2-3/4"	Rectangle					Low			Yes
48		One Size			Hypoallergenic	Stoma Cover	Waterproof Cap				1" to 2-1/4"	Rectangle					Low			Yes
49		One Size				Stoma Cover	Waterproof Cap				1-1/8"	Round Center								Yes
50		One Size				Stoma Cover	Waterproof Cap				1-1/2"	Round Center								Yes
51	200 mL	One Size				Stoma Cover	Waterproof Cap				2-1/4" to 2-1/4"	Round Center	7/8"							Yes
52	200 mL	One Size				Stoma Cover	Waterproof Cap				3" to 3"	Round Center	1-1/8"							Yes
53	200 mL	One Size				Stoma Cover	Waterproof Cap				3/4" to 1-1/4"	Rectangular								Yes
54		One Size				Stoma Cover	Waterproof Cap				2-1/2" to 3-3/4"	Round Center	1"							Yes
55	200 mL	One Size				Stoma Cover	Waterproof Cap				7/8" to 2-5/8"	Round								Yes
56		One Size				Stoma Cover	Waterproof Cap				3/4" to 1-1/4"	Rectangular								Yes
57	200 mL	One Size				Stoma Cover	Waterproof Cap				2-5/8"	Round Center	7/8"							Yes
58	200 mL	One Size				Stoma Cover	Waterproof Cap				3" to 3"	Round Center	1-1/8"							Yes
59		One Size			Fabric	Adhesive	Bandage				2" to 3"	Oval				Yes	High			Yes
60		One Size			Fabric	Adhesive	Bandage				1-1/4"	Oval				Yes	High			Yes
61		One Size			Fabric	Adhesive	Strip		Mold-to-Fit		1" to 3"			Two Sided Comfort	Stomahesive	Yes	High			Yes
62		One Size			Fabric	Adhesive	Strip		Mold-to-Fit		3/4" to 3"			Two Sided Comfort	Stomahesive	Yes	High			Yes
63		One Size	Clear			Low Pressure Adaptor	Barrier Ring			2-3/4"				Disposable						Yes
64			Clear		Polymer	Ostomy Vent		Pouch								No		Universal		Yes
65		Thick	Blue			Skin Barrier	Ring				1/2" to 3"									Yes
66		Standard	Yellow			Skin Barrier	Ring				1/2" to 3"									Yes
67		Standard	Yellow			Skin Barrier	Ring				3"									Yes
68		X-Large	White		Polyster	Belt	Elastic				41" to 46"		6"			No		Adult	Flat	Yes
69		Large	White		Polyster	Belt	Elastic				36" to 40"		6"			No		Adult	Flat	Yes
70		X-Large	White		Polyster	Belt	Elastic				41" to 46"		6"			No		Adult	Flat	Yes
71		Large	White		Polyster	Belt	Elastic				36" to 40"		6"			No		Adult	Flat	Yes
72		X-Large	White		Polyster	Belt	Elastic				41" to 46"		4"			No		Adult	Flat	Yes
73		Large	White		Polyster	Belt	Elastic				36" to 40"		4"			No		Adult	Flat	Yes
74		Medium	White		Polyster	Belt	Elastic				32" to 35"		4"			No		Adult	Flat	Yes
75		X-Large	White		Polyster	Belt	Elastic				41" to 46"		6"			No		Adult	Flat	Yes
76		Large	White		Polyster	Belt	Elastic				36" to 40"		6"			No		Adult	Flat	Yes
77		X-Large	White		Polyster	Belt	Elastic				41" to 46"		6"			No		Adult	Flat	Yes
78		Large	White		Polyster	Belt	Elastic				36" to 40"		6"			No		Adult	Flat	Yes
79		X-Large	White		Polyster	Belt	Elastic				41" to 46"		4"			No		Adult	Flat	Yes
80		Large	White		Polyster	Belt	Elastic				36" to 40"		4"			No		Adult	Flat	Yes
81			White			Belt	Adjustable	Two Piece Appliance			26" to 49"					No		Adult	Flat	Yes
82						Skin Barrier	Ring		Cut-to-Fit		1-9/16"	Round			Flextend	No		Adult		Yes
83						Skin Barrier	Ring		Cut-to-Fit		1-3/16"	Oval			Flextend	No		Adult		Yes
84						Skin Barrier	Ring		Cut-to-Fit		1-3/16"	Round			Flextend	No		Adult		Yes
85	60 g					Skin Barrier	Strips					Traingular	5-1/8"		Flextend	No		Adult		Yes
Ostomy - Full

 

[Radoslaw Poprawski]

and where do those misspelings appear?

 

[desibouy]

It could appear in any column sir.

 

[Radoslaw Poprawski]

Then I am sorry, but this is not doable,
the table has too many of different options for incorrect spelling.
this is like trying to find microscopic droplet of evaporating water on Jupiter using basic magnifying glass.

The only way I can think of is to prepare 1 column table with properly spelled descriptions and then compare it with this dataset.