Issue with updating .CSV file

Topic

New Reply

Hi All,

I need to modify .CSV file that is imported from XDOC application.

Earlier I had made the modifications using Ultraedit software. This time, I want to use MS Excel instead of Ultraedit, as this is more convenient. However, when I edit and re-import the updated file it is not accepted by the application.

To find if any thing is incorrect, I tried opening the file in Ultraedit and found that few extra comma’s are included with the usage of MS Excel.

The following is the code snippet with extra comma symbols:

Structure Report (only the toplevel) ,,,

ParentElement,,,
Name:,fad_rnp,,
Id:,0900d80580761f40,,
Title:,Radio network performance,,
External ID:,0900d805805a59e4,,

Children,,,
Position,Name,Id,Binding

1,DN9814062,0900d80580589d50,fixed
2,DN9814132,0900d80580589d6e,fixed
3,DN9814168,0900d8058075a2a6,fixed
4,DN9814129,0900d805807d5741,fixed
5,DN9814214,0900d8058058d726,fixed
6,DN9814202,0900d8058058f01a,fixed

Whereas, the correct format of the file is:
sep=;
Structure Report (only the toplevel) ;
;
;
ParentElement;
Name:;fad_rnp;
Id:;0900d80580761f40;
Title:;Radio network performance;
External ID:;0900d805805a59e4;

;
Children;
Position;Name;Id;Binding;

1 ;DN9814062;0900d8058076373f;CURRENT
2 ;DN9814132;0900d805807349c6;CURRENT
3 ;DN9814168;0900d8058075a2a6;CURRENT
4 ;DN9814129;0900d805806a75b3;CURRENT
5 ;DN9814214;0900d8058058d726;CURRENT
6 ;DN9814202;0900d8058058f01a;CURRENT

Reply | Quote

Replies

XL does columns as well as rows. It will save as many columns as you have in the max columns and fill them with blanks which leads to the extra commas.

A relatively simple ways around this problem and converting to semi-colons:
1.Save as CSV
2.Open CSV in Notepad
3. Find replace commas with semi colons
4.find 2 semicolons and replace with 1

Repeat 4 until there are no double semicolons

Steve

Reply | Quote

Just a note that although Steve’s solution should work with the example data given, be aware that it will not work if the file contains a row with a blank column with data in the following column. In this case the solution will move all columns in that row following the blank column one column to the left. This is caused by step 4. in the instructions.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote