Another Excel question
Thread poster: FreeHansje

FreeHansje  Identity Verified
Local time: 10:46
Dutch to English
+ ...
Jul 1, 2012

I received an Excel sheet with text to translate. In this thread I asked how to handle such Excel sheets: http://www.proz.com/forum/sdl_trados_support/224272-excel_question.html. Basically you copy the original text and hide all other columns. In Trados you will only see this copied column, translate it and rename the new column with the translations. This works excellent, now I have a different situation. Hopefully some1 has dealt with this before and is willing to share the solution.

I have column A with text to be translated, column B is where the translations go. However, about half of the text is already translated by some1 else, so I need to filter on the blanks in column B, then copy column A, hide the rest and do the translations.
Copying a filtered column poses a problem: Excel gives me an error if I copy a complete column (´that command cannot be used on multiple selections´), copying by selecting only the fields of this column gives me problems copying back after the translation is done. Again, copying the whole column does not work, copying only the fields will copy all the fields into the target column starting from the offset-field. Example: If I have filtered and translated field 3, 4, 7 and 31 in column A and I move those fields back to column B (which is where the translations go), than in column B the fields 3, 4, 5 and 6 are overwritten with the fields to be copied.

I hope I have made the problem clear. So does any1 know a solution for this problem?

TIA,
Hans Brouwer


 

István Hirsch  Identity Verified
Local time: 10:46
English to Hungarian
This may help you Jul 1, 2012

http://www.proz.com/forum/office_applications/224347-excel_file_with_hidden_rows.html

 

FreeHansje  Identity Verified
Local time: 10:46
Dutch to English
+ ...
TOPIC STARTER
This works, but... Jul 1, 2012

Tnx for answering Istvan.
I had something similar in mind, with one exception: =IF(A1="";B1;A1) When A1 is NOT empty, then use the value of A1. Thus I have a new column with both the old and the new text/translations. But, there are about thousend row and I wondered if there is a more fullproof method to do this. Research so far indicates this is the way to do it, short of writing a macro.

I'll hope someone still has an alternative.

Greetz,
Hans Brouwer


 

SDL Community  Identity Verified
United Kingdom
Local time: 10:46
English
Maybe use CSV or text Delimited Jul 1, 2012

Hi Hans,

If converting your files to one of these formats is acceptable for you then you can use an out of the box method to do exactly what you need. I created a quick video explaining how to do this here:

http://youtu.be/WC6TZ_GP5nk

Regards

Paul


 

FreeHansje  Identity Verified
Local time: 10:46
Dutch to English
+ ...
TOPIC STARTER
Promising... Jul 1, 2012

This looks promising, Paul. I'll look into it. I like to transform back to the Excel format I received the file in, obviously. I will do a test and check if that will pose any problems.

Tnx for the effort you put into this. Appreciated.

Greetz,
Hans Brouwer


 

Chunyi Chen
United States
Local time: 01:46
English to Chinese
Let me know the results Jul 1, 2012

Hi Hans,

I just watched the video created by Paul and had the same question as you at the end. My guess is you can save csv back to xls or xlsx without losing any data or formatting. If you get a chance to test it, please let me how it went.
Thank you.

Great video and instruction, Paul. Thank you for showing this know-how in such a visually clear way.

Chun-yi

FreeHansje wrote:

This looks promising, Paul. I'll look into it. I like to transform back to the Excel format I received the file in, obviously. I will do a test and check if that will pose any problems.

Tnx for the effort you put into this. Appreciated.

Greetz,
Hans Brouwer


 

FreeHansje  Identity Verified
Local time: 10:46
Dutch to English
+ ...
TOPIC STARTER
Test succesful Jul 1, 2012

I saved the xlsx file as a cvs, did some translations with it and saved it back to xlsx. It looks fine.
I will go along with the method Paul suggested.
Tnx again Paul. Your video taught me more about the possibilities of Studio. Now I know a bit more then how to use the editor and how to handle translation memories...;-)

Greetz,
Hans Brouwer


 

Chunyi Chen
United States
Local time: 01:46
English to Chinese
This solution doesn't seem to work if you work into Chinese Jul 1, 2012

Hi Paul,

Out of curiosity I tested your solution on one of my Excel files where the client made a similar request. It turns out that when I saved the Excel file in comma delimited (csv) or tab delimited (txt) format, the Chinese in the B column would not display correctly. The only way for Chinese to display correctly is to save the file in Unicode text (.txt), but then I don't get the same options (Making existing translation confirmed & Lock existing translations) offered to me as you would with CSV file type. And then at the end I couldn't convert the .txt bilingual file back to the Excel format. If you know of any ways or any other format that would work with English>Chinese, I would be very interested to learn it from you!
Thank you.

Chun-yi

SDL Support wrote:

Hi Hans,

If converting your files to one of these formats is acceptable for you then you can use an out of the box method to do exactly what you need. I created a quick video explaining how to do this here:

http://youtu.be/WC6TZ_GP5nk

Regards

Paul


 

SDL Community  Identity Verified
United Kingdom
Local time: 10:46
English
Encoding issues Jul 2, 2012

Hi Chun-yi,

Working with Chinese you need to be in unicode. So if you look at File - Active Document Settings when the file is open in the editor is the source encoding Unicode or ascii? Mine is ascii and I have the same problem as you report. But if I change the Regional Settings so that Chinese Locale is set up correctly then the originating file is correctly encoded and the target csv retains the correct characters.

Normally I can't even save an excel as csv and retain the formatting..! But you should be able to?

Let me know if none of this works for you and we can log a case through your support contract.

Regards

Paul


 

Chunyi Chen
United States
Local time: 01:46
English to Chinese
Thank you for the additional information, Paul. Jul 2, 2012

When I open the file in Editor, the source encoding shows Western Europe (Windows) and Target Encoding shows (Auto-select). I tried a few encoding options in that Target Encoding list but not of them seemed to work. I do know other (long haul) ways in handling such partially translated Excel file, so I will just use them. I don't really want to change the Regional Settings now that everything on my system seems to work fine. It's not worth the risk to change the Settings for one file:)
Thank you again.

Chun-yi

SDL Support wrote:

Hi Chun-yi,

Working with Chinese you need to be in unicode. So if you look at File - Active Document Settings when the file is open in the editor is the source encoding Unicode or ascii? Mine is ascii and I have the same problem as you report. But if I change the Regional Settings so that Chinese Locale is set up correctly then the originating file is correctly encoded and the target csv retains the correct characters.

Normally I can't even save an excel as csv and retain the formatting..! But you should be able to?

Let me know if none of this works for you and we can log a case through your support contract.

Regards

Paul


 


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

Another Excel question

Advanced search







Déjà Vu X3
Try it, Love it

Find out why Déjà Vu is today the most flexible, customizable and user-friendly tool on the market. See the brand new features in action: *Completely redesigned user interface *Live Preview *Inline spell checking *Inline

More info »
CafeTran Espresso
You've never met a CAT tool this clever!

Translate faster & easier, using a sophisticated CAT tool built by a translator / developer. Accept jobs from clients who use SDL Trados, MemoQ, Wordfast & major CAT tools. Download and start using CafeTran Espresso -- for free

More info »



Forums
  • All of ProZ.com
  • Term search
  • Jobs
  • Forums
  • Multiple search