Apache OpenOffice Community Forum

Hi, I'm having an issue where the following is happening...I download an .xls file from an internal database.

The file is Tab delimited. I then go to convert it to a CSV file for importing into a CRM system. When it converts everything is put into one column. The character set said Unicode UTF-8. I then re-try the conversion, this time changing the . This time I tried randomly changing the Character set to Western Europe (ASCII-US), this time it has multiple column but, the information is not held in the previous columns.

The data in the columns prior to the saving does have multiple data with commas in it. So my main issue is the following. How do I convert a file (see attached) into a standard CSV file that any CRM program reads as a CSV file. Any help would be greatly appreciated!

I attached a sample file (.xls prior to any conversion)

Why Calc? Your CRM can not handle tab-separated text files? Rename that xls to csv (it has nothing to do with Excel anyway) and try to import as is.
Apart from that, Base provides better ways to import database data without any text files.

Villeroy, thanks for the reply but it doesn't help solving the specific issue. Renaming the file does not convert Tab delimited to CSV format. We need to take a tab delimited file like the attached, and convert it to a CSV where the formatting is kept intact for the spreadsheet and the cells. With Excel it is simply a matter of saving the file as a different type and all works fine. With OpenOffice we haven't found a solution for what we thought would be a simple conversion. Not to bore you with details, but we are unable to use Excel and must find a solution. Any specific help or direction as to how to do this with a file in the specific format (that is attached) would very much be appreciated. Thanks again!

CSV is not a file format. It is plain text that follows certain conventions. When I save your "xls" as csv, OOo keeps the column delimiter. It remains a tab-separated text file which is what 99% of users would expect. You've got to change the export settings. There is an option in the save-as dialog for file type text(csv).
Since you are using Linux, there are by far more efficient ways to exchange the separators of file types by means of a script.
Most of the slightly advanced text editors for Linux can also do the trick in a more interactive way. Mind the commas in your text values. You need to add text delimters.

Thanks, Villeroy. I think I'm out of my element on this one. Saving simply as a CSV improved the output a little bit, but fields etc. are still merging. We have tried to modify delimiters (without having a knowledge base to do so), but can't seem to get this right. I know it is irrelevant, but we never had this issue and thought it would be a simple (non-programmer simple) solution. I really really really did not want to have to convert out of Ubuntu back to Microcrap...ah well. We need a standardized solution because we do this on a daily basis. Thanks again for your help, it was much appreciated!!

Hi, and welcome to the forum.

To change tabs to commas with Calc:

• Use File, Open, File type = Text CSV (scroll way down to find that and do not use Text or Text Encoded)
• The import dialog will probably default to "Separated by Tab" but choose that if not
• Use File, Save As, File type = Text CSV, Check Edit Filter Settings, and set Field delimiter to a comma.

To change tabs to commas with a command (I tested with Unix but I presume this also works in Linux):

• cd Directory-containing-your-file
• tr '\t' ',' <test5.xls >test5.csv

Edit: If fields are still "merging", it's probably because the file has been built incorrectly with the tab delimiters in the wrong positions. The output looked OK to me. I see that some fields (like CRD NO) contain multiple comma-separated values, and have to assume that this is correct. You could merge or unmerge fields with Calc, but you'd need to explain in a lot of detail what needs to be done.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

First off,..THANK YOU! Secondly,...we did the following to get it into the necessary format...We opened the file as Character: UTF-8, Language: English-USA, Separated by: Tab, Text Delimiter: ", Other Options: Quoted Field as Text,...after opening the file we then saved it with a File Type: Text CSV, then Edit Filter Settings, then Character Set: Unicode UTF-8, Field Delimiter: (comma , ) , then Text Delimiter: ", then Check the box labeled "Save Cell Content as Shown",...and all seems to work well. We noticed that when opening files, Calc would seem to switch Characters/Language randomly which may have been impacting our ability to isolate the issue. However, all seems fine now. Thanks again for all your help everyone! It truly was greatly appreciated!