Most efficient way to revise multiple bilingual Excel files
Thread poster: Brent Sørensen
Brent Sørensen
Brent Sørensen  Identity Verified
Germany
Local time: 01:24
Member (2016)
German to English
+ ...
Sep 3, 2020

I have a new direct client. There is some software that has been translated from German to English.

My client has around 60 Excel files. There is a column for an ID number. There is also a column for the German source text and the English target text. Apparently this was done by a German native speaker and some of the translations are questionable.

The client wants me to read through all the translations and insert any corrections in a new column.

Unfortuna
... See more
I have a new direct client. There is some software that has been translated from German to English.

My client has around 60 Excel files. There is a column for an ID number. There is also a column for the German source text and the English target text. Apparently this was done by a German native speaker and some of the translations are questionable.

The client wants me to read through all the translations and insert any corrections in a new column.

Unfortunately, the client was just given these files and has no idea how they were generated. The client had some sort of glossary on some sort of proprietary CAT tool that I’ve never seen before. The client has zero idea about CAT tools. I’ve asked the client to try and find some additional information. I have a feeling there are some .xliff tools somewhere.

I’m trying to think of a more efficient way of handling this than opening up all the individual Excel files. Does anybody have any suggestions?
Collapse


 
Samuel Murray
Samuel Murray  Identity Verified
Netherlands
Local time: 01:24
Member (2006)
English to Afrikaans
+ ...
@Brent Sep 3, 2020

Brent Sørensen wrote:
My client has around 60 Excel files[, all with an identical number of columns and all with the same type of data in those columns].


One option is to merge all Excel files into a single Excel file, with all content on a single worksheet, and optionally later split it again into separate Excel files.

Perhaps your client would be happy with a merged file as long as the original files' names are in a separate column. Or, if you can't figure out how to split it into separate Excel file, perhaps this saves enough time for you that you would not mind copy/pasting your revisions column into the original files.

To merge multiple Excel files into a single file:

If your files have only one worksheet each (with the default name), then this may work: Put all your Excel files in a single folder. Then, in Excel, create a new empty file, and then click Data > Get Data > From File > From Folder. Specify the folder, then click OK, and then click the "Transform Data" button. Click the double down arrow in the Content column's header (which means "Combine files"). It may then show a preview (click OK) and then it will combine the files. The first column of the combined file will contain the file name of each line's originating file. Then click "Close and load" on the ribbon.

To split the above merged Excel file into separate files:

I found a method of splitting such a merged file:
https://www.pk-anexcelexpert.com/vba-split-data-into-separate-workbooks/
Unfortunately it retains the file name column. You have to enable the Developer tab in Excel, and name the sheet "Data" and create a second sheet called "Settings". This macro (adjusted slightly from that video) worked for me (remember to edit "YOURNAME"):

Option Explicit
Sub Split_Data_in_workbooks()
Application.ScreenUpdating = False
Dim data_sh As Worksheet
Set data_sh = ThisWorkbook.Sheets("Data")
Dim setting_Sh As Worksheet
Set setting_Sh = ThisWorkbook.Sheets("Settings")
Dim nwb As Workbook
Dim nsh As Worksheet
setting_Sh.Range("A:A").Clear
data_sh.AutoFilterMode = False
data_sh.Range("A:A").Copy setting_Sh.Range("A1")
setting_Sh.Range("A:A").RemoveDuplicates 1, xlYes
Dim i As Integer
For i = 2 To Application.CountA(setting_Sh.Range("A:A"))
data_sh.UsedRange.AutoFilter 1, setting_Sh.Range("A" & i).Value
Set nwb = Workbooks.Add
Set nsh = nwb.Sheets(1)
data_sh.UsedRange.SpecialCells(xlCellTypeVisible).Copy nsh.Range("A1")
nsh.UsedRange.EntireColumn.ColumnWidth = 15
nwb.SaveAs "C:\Users\YOURNAME\Desktop\Output Folder" & "/" & setting_Sh.Range("A" & i).Value & ".xlsx"
nwb.Close False
data_sh.AutoFilterMode = False
Next i
setting_Sh.Range("A:A").Clear
MsgBox "Done"
End Sub


[Edited at 2020-09-03 08:53 GMT]


 
Heinrich Pesch
Heinrich Pesch  Identity Verified
Finland
Local time: 02:24
Member (2003)
Finnish to German
+ ...
Charge by the hour Sep 3, 2020

That sounds as a good source of income for you, if the client is ready to pay your time. Most important of course you are well familiar with the subject of the translation. If there are wrong translations you easily can fix them with search and replace, when the target is English. Why not open each file separately and fix it?

 
Noel McCourt
Noel McCourt  Identity Verified
United Kingdom
Local time: 00:24
English
Import bilingual Excel files into a CAT tool Sep 3, 2020

Why not import the files into a CAT tool? I think most mainstream tools have this feature. In some cases you may need to remove the ID column and just have the source and target. I know this feature is available in Trados Studio:
http://producthelp.sdl.com/SDL_Trados_Studio_2015/client_en/Bilingual_Excel_Settings.htm

Smartcat (fre
... See more
Why not import the files into a CAT tool? I think most mainstream tools have this feature. In some cases you may need to remove the ID column and just have the source and target. I know this feature is available in Trados Studio:
http://producthelp.sdl.com/SDL_Trados_Studio_2015/client_en/Bilingual_Excel_Settings.htm

Smartcat (free and online) also allows import of bilingual Excel files and you can define the columns and import existing translation (in the target column) ready for editing. You will then automatically be able to create a TM when done and you will have a proper editing environment instead of Excel.
Collapse


Peter Motte
 
Brent Sørensen
Brent Sørensen  Identity Verified
Germany
Local time: 01:24
Member (2016)
German to English
+ ...
TOPIC STARTER
I’d like to use Trados - but I’m not allowed to change the original translation Sep 3, 2020

Ideally I would like to do this in Trados. But as far as I know, you can’t have three columns.

There are three approaches I can think of:

1.
I could have one column for the uncorrected English and one for the corrected English. The problem is that I would keep having to refer to the Excel file to see what the original German was. But this would allow me to have the altered text in a new column like the client wants.

2.
I could have one colum
... See more
Ideally I would like to do this in Trados. But as far as I know, you can’t have three columns.

There are three approaches I can think of:

1.
I could have one column for the uncorrected English and one for the corrected English. The problem is that I would keep having to refer to the Excel file to see what the original German was. But this would allow me to have the altered text in a new column like the client wants.

2.
I could have one column for the German text and one for the English text. I could correct the English if needed. I know you can compare two Word files to see what the differences are. Can this also be done in Excel. And would there be a way to to have the changes in a separate column

3.
I could have one column for the German text and one for the English text. I could add a comment if the English term needs correcting. But is there a way to export the comment into a new column?
Collapse


 
James Plastow
James Plastow  Identity Verified
United Kingdom
Local time: 00:24
Member (2020)
Japanese to English
Trados Sep 3, 2020

I would add a new column in each of the excel files and insert "English OK" into all the rows.

Then create a Trados project with the files (regular excel files not bilingual)

Then copy all source to target

Then replace "English OK" with your correction in the relevant segments.

[Edited at 2020-09-03 12:11 GMT]


 
Noel McCourt
Noel McCourt  Identity Verified
United Kingdom
Local time: 00:24
English
Edit the English column and merge after translation Sep 3, 2020

I see the issue with the 3 columns. You could assign the German or English column as the Context column which should display in Studio. I'm not sure how tricky this is to get right. I'm done in other tools but not Studio. According to the documentation it is possible.

But a workaround would be to load the two columns and edit in Studio, then save the target Excel files. You then copy the updated English column into each of the original spreadsheets into the corrected column. It's 1
... See more
I see the issue with the 3 columns. You could assign the German or English column as the Context column which should display in Studio. I'm not sure how tricky this is to get right. I'm done in other tools but not Studio. According to the documentation it is possible.

But a workaround would be to load the two columns and edit in Studio, then save the target Excel files. You then copy the updated English column into each of the original spreadsheets into the corrected column. It's 1 copy and paste per file but if this is too cumbersome you could combine this with merging files as suggested by Samuel.
Collapse


 
Stepan Konev
Stepan Konev  Identity Verified
Russian Federation
Local time: 03:24
English to Russian
Source – Target – Target Copy Sep 3, 2020

If you have column A for German source and clomun B for English target, you can copy column B and paste it as column C. Then you import a bilingual Excel with column A as source and column C as target. This is how you can see the original translation and amend it as needed. Column B with original translation will remain out of Trados but you will be able to see its content as column C that you have imported under the bilngual Excel workflow. Once you are done with your edits, you will have 3 col... See more
If you have column A for German source and clomun B for English target, you can copy column B and paste it as column C. Then you import a bilingual Excel with column A as source and column C as target. This is how you can see the original translation and amend it as needed. Column B with original translation will remain out of Trados but you will be able to see its content as column C that you have imported under the bilngual Excel workflow. Once you are done with your edits, you will have 3 columns with source, translation, and edits.

[Edited at 2020-09-03 14:33 GMT]
Collapse


 


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

Moderator(s) of this forum
Laureana Pavon[Call to this topic]

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

Most efficient way to revise multiple bilingual Excel files






Wordfast Pro
Translation Memory Software for Any Platform

Exclusive discount for ProZ.com users! Save over 13% when purchasing Wordfast Pro through ProZ.com. Wordfast is the world's #1 provider of platform-independent Translation Memory software. Consistently ranked the most user-friendly and highest value

Buy now! »
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 Trados, MemoQ, Wordfast & major CAT tools. Download and start using CafeTran Espresso -- for free

Buy now! »