[Solved] Opening .XLS then converting to CSV

Discuss the spreadsheet application
Post Reply
Chris302
Posts: 4
Joined: Mon Jan 31, 2011 3:31 pm

[Solved] Opening .XLS then converting to CSV

Post by Chris302 »

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)
Attachments
test5.xls
Test File
(5.85 KiB) Downloaded 349 times
Last edited by Hagar Delest on Mon Jan 31, 2011 10:07 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 3.2 Ubuntu 11.042
User avatar
Villeroy
Volunteer
Posts: 31293
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Opening .XLS then CONVERTING to CSV

Post by Villeroy »

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Chris302
Posts: 4
Joined: Mon Jan 31, 2011 3:31 pm

Re: Opening .XLS then CONVERTING to CSV

Post by Chris302 »

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!
OpenOffice 3.2 Ubuntu 11.042
User avatar
Villeroy
Volunteer
Posts: 31293
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Opening .XLS then CONVERTING to CSV

Post by Villeroy »

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Chris302
Posts: 4
Joined: Mon Jan 31, 2011 3:31 pm

Re: Opening .XLS then CONVERTING to CSV

Post by Chris302 »

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!!
OpenOffice 3.2 Ubuntu 11.042
User avatar
MrProgrammer
Moderator
Posts: 4991
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Opening .XLS then CONVERTING to CSV

Post by MrProgrammer »

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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.7, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Chris302
Posts: 4
Joined: Mon Jan 31, 2011 3:31 pm

Re: Opening .XLS then CONVERTING to CSV

Post by Chris302 »

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!
OpenOffice 3.2 Ubuntu 11.042
Post Reply