Display CSV file in columns

[randy_randy]

I frequently manipulate CSV files in UE. I would like to be able to display them so that each field shows in its own column. I cannot change the contents of the actual file (currently I have been doing search/replace to insert Tabs, then remove the tabs when finished, but that's a pain and occasionally something gets missed, so the file is no good). Ideas?

[Bego]

???

Better import the file to Excel and then write back as csv again.

rds Bego

[randy_randy]

I have used Excel for import/export in the past, however Excel insists on reformatting the columns as it assumes they should be so the output is not the same as the original. I just want to be able to view it that way in UE

[Mofi]

Just for clarification: Do you only want to view the CSV files or do you also want to edit it?
For viewing only there are several free tools available in the internet which shows you the content of a CSV file in columns like Excel.

In UE the functions Convert to Fixed Column and Convert to Character Delimited in the Column menu are for the purpose you need.

[randy_randy]

Thanks Mofi. That was the command I was looking for.

[KyGenealogist]

As a genealogist, I have often wanted to give each item its own column. An example is warranted first.

"Brown","James Elijah","Miami Beach","Miami-Dade County","Florida",12.345,083.6537

Notice it contains a person's name, city/town, county, state, latitude, longitude.

When I try this- all the characters Columnize, but the numbers are put in one column. That is 12.345,083.6537 is one column however it is two very different values. What would I be doing wrong? Would it be possible to put the 12.345 in one column and the 083.6537 in another column?

Please, before someone suggests using Excel, do what I want to do, save in CSV, and use Ultraedit again, there are 1,757,804 records in this file. Lastly, would I be able to sort by the second number (last column primarily)?

Thank you.

Robert

[Mofi]

Hello Robert!

Nobody can tell you what you are doing wrong if you do not tell us what you do.

What options have you selected in the Convert to Fixed Column dialog and what have you entered? Which version of UE do you use? Is the file an ASCII file or a Unicode file? Have all latitude numbers the format 2.3 (2 digits before and 3 digits after comma) and the longitude numbers 3.4?

Sorting based on a column number is possible. After converting the CSV file to fixed column and if needed after some regular expression replaces to insert leading/trailing zeros to the numbers, place the cursor to start of last column. Then open File - Sort - Advanced Sort/Options and specify for Key 1 the values 0 and -1. 0 means current cursor position.

[KyGenealogist]

-Copy-
Nobody can tell you what you are doing wrong if you do not tell us what you do.
-End-

Yep, you are right. I will have to get used to this- I thought I had the latest version and support was for the latest version, but I don't. I have ver 11.

-What options have you selected in the Convert to Fixed Column dialog and what have you entered? Which version of UE do you use? Is the file an ASCII file or a Unicode file? Have all latitude numbers the format 2.3 (2 digits before and 3 digits after comma) and the longitude numbers 3.4?
-End-

I chose the following options:
Scan first line only vs. complete line Unchecked
Keep Delimeter with fixed columns Checked
Ignore Separator in quotes (') Unchecked
Ignore Separator in doublequotes Checked
Separator Character (^t for tab) ^t
Field widths separated by commas <blank>

The file goes blank, but does return 1,756,104 blank lines.

Each and every latitude number is composed of 2 digits before the period, and 3 digits after, but each and every longitiude number is composed of 3 digits before and most have three digits after, but some have four after.

I have always used Ultraedit as a basic editor, and not anything this big before. Another question, but I can wait until later to ask: Can / will Ultraedit sort a 1,756,104 line file? I think it will.

But will it just keep one of each line? I.e., suppose I have:

Miama, Miami-Dade County, Florida, 12.345, 083.321
Miama, Miami-Dade County, Florida, 12.345, 083.321
Miama, Miami-Dade County, Florida, 12.345, 083.321

I really don't need but just one of those.

Thank you.

Robert Carneal
(KyGenealogist)

[bellafortuni]

check File/sort/Advanced options/Remove duplicates

[Mofi]

I run a simple test with UE v11.20a.

According to your example you must specify in the Convert to Fixed Column dialog the comma sign , as separator character. All other options are correct for you.

Now press the button Scan. UltraEdit will now scan the whole file to determine number of characters needed for each data column and will print it into the Field Widths field. You can modify it now, but normally this is not necessary.

Now you can press the button Convert to convert the file from character delimited to fixed column.

The different number of post comma digits at the last number is no problem. So you can now sort the file as already explained and of course with Remove Duplicates option checked.

Note: You have 4 pairs of sort keys. If you do not specify for key 1 the values 0 and -1, but the real column numbers - see status bar Col x when cursor is set to start or end of your "longitude column", you can specify for key 2 the columns of the "latitude column", for key 3 the columns of the "name columns" and for key 4 the columns for the "address columns".

This would have following effect. The lines are sorted primary by the longitude number. If the longitude number of 2 lines is identical, the sort order is determined by the latitude number. If also the latitude number is identical, the name determines the sort order. And if also the names are identical, the address is used as last criteria for the sort order. If the whole line is identical, option remove duplicates forces to delete all duplicates.

Please note: Both functions Convert to Fixed Column and Sort cannot be undone. So save the file after Convert to Fixed Column best to a new file name before running the sort. Use File - Revert to Saved if one of the functions did not work as expected because of wrong settings.