Was curious if you had considered or looked into a fixed width export tool and not just CSV? I have seen so many cases where fixed width can be nice as it gets around quotes in text fields and other things.
Also realized the CSV export tool is not described on the main page. It is nice enough that should be mentioned.
I was thinking about one of the text editors I use. It had a Convert to Fixed Columns option. I attached a screen shot of the dialog box. I was thinking of a similar option for XL Toolbox. I was hoping much of the CSV export code could be used for the other.
That’s a very interesting suggestion. Will need to think about the details: How to determine the column with other than going through each cell first before exporting (but this may be the way to go).
Thanks for pointing out the missing documentation!
I think that in many cases there there may already be a predefined set of limits. The dialog screen shot I posted was from UltraEdit (forgot to mention that). I think in many cases people may want to set or adjust the predefined field lengths and even be willing to truncate items like an extra long description. As the fixed with may be predefined in some cases. I do like how UltraEdit will scan a text file, and suggest some lengths then you can adjust or just start with your own lengths.
If curious how UltraEdit handles it you could play with it for their trial period.
My only curiosity is how you would handle an Excel file with a newline int the cell? I have never tested that in XL Toolbox, but have seen CSV files that do have a new line in a cell as long as is surrounded by quotes. In the case of fixed width files it would be best to strip out any CR/LF or just a CR or just an LF.
The other day i helped work though a problem with a CSV file that had CR/LF for end of records, but inside some of the quoted fields were LF (char 10) to create a multi line field. It is still causing us pains.
Hope you will consider this feature
Oh, if you update the webpage to info about the CSV Export options be sure and also a a link on the front page to the change long. Noticed it can be hard to find.
One feature of your CSV export is that some may not realize is it seems to support full Unicode Export (not sure if there is a BOM). I learned a long time ago that Excel’s default would mess up some characters Chinese characters.
Keep up the good work
@asjones123, first of all I hope you are being notified of this new reply of mine, because I have moved the forum to a new software in the meantime. In case you cannot log in with your old user name, please attempt to have your password reset or try the alternative login methods.
I’ve just released version 7.2.0 which offers ‘tabular’ CSV export. It’s not as full-featured as the export function of UltraEdit, but 1) I don’t want to clone UltraEdit and 2) I do not fully agree with their approach. There are also a couple of unresolved questions such as what to do with numbers – right align them? Limit the number of decimal places?
Anyway, let me know what you think. The release is available at www.xltoolbox.net, as always.
[same as asjones12]
thanks for the reply… first I did get an email from the forum about this post. I was not able to log in with my username so I tired to reset my password. However i never got a reset email (tried a few times). So just used my Gmail.
I can’t test just yet, but will do so…
Glad to hear you are looking at expanding the export options. I am curious what you don’t fully agree with on UltraEdit’s approach (there are times when i wanted “more”).
When you say ‘tabular’ CSV do you mean “tabs” instead of commas? Or fixed with with truncating as i mentioned so that field A started at position X and field B starts at position Y?
I agreee there are several questions that can come up.
- long items in a field… just truncate
- Excel has new line in a cell … not sure what you do with that now, I would just strip the new line
- For numbers what about right justification
- leave decimals alone …
- As I said I have not had a chance to to review your implementation, but if it is space delimited and one specifies a starting position number what about 1 ,10, 15R, 30 would mean that the 3rd field starts in at position 15, but will be right justified so number would end up at position 29 for the right most digit?
not sure what went wrong with the e-mails.
With ‘tabular’ I mean to say ‘aligned columns’. Couldn’t find a better, concise word for it. It uses spaces, not tabs. However, since you can also specify a field delimiter, it is possible to get something like this with a pipe symbol
first column | second column
some data in it | more data
and so on... | ...
I personally disagree with the notion of pre-defining column widths and truncating data because I believe the XL Toolbox’ import/export function should be about data interchange, not so much about document formatting. I’m an ardent user of text-based documentation myself (love Markdown, it was one of the many reasons to switch the forum software; the table above was formatted with Markdown). But truncating data, well… truncates the data
By the way, yes it is UTF-8 without BOM.
I did some initial testing and like it. However, I did notice a few things.
I did not specify any field separator and selected Tabularized and it seemed to put a space between fields. In my mind there would not be a space. I could see how that would be intentional on your part, but not sure a space should be there. I would otherwise expect the fields to just run together so the column marker is the delimiter. I did notice there is no extra space if I put a delimiter in i.e. a |
I next put a space in the filed separator field (just pressed space bar. and there was no “extra” space. HOWEVER then my first row that i was using for a column header with headers like
Col a Col b , Col c …
all had quotes around them
"col a" “col b” “col c” “col d” along with other fields that had spaces in them.
If one is doing a space delimited not sure if you would quote fields with spaces. As the spaces say when a new column starts/ends
I may have talked to much about truncating…In past experiences the space delimited (column numbers) interchanges I have done had widths that were predefined sometimes by the receiving party. So saying start at column 1, 15, 30 36 were to match up to something else.
I might be neat, but not sure how to implement it … if the spacing is automatically calculated as it is now to give a list of the starting position for each filed. Maybe a display on the screen that can be copied an pasted 1, 15, 30, 36 or an extra output to a file or something.