Forgot password? | Forgot username? | Register

Converting Word to Excel - carriage returns within a field

Converting Word to Excel - carriage returns within a field

Hi folks

I'm going nuts, need some help.

A curator wanted to take home a word doc to do some edits to Notes fields for seeral thousand Catalogue records. I created this doucment, pulling just the IRN and the Notres fields. The Curator has now returned the Word Doc, with edits, ready for me to import back into EMu using the Import Tool.

The problem is, some of the Notes fields contain carriage returns/paragraphs and these are to be retained at the Curator's request. When I try to convert the word document over to Excel to make a CSV file the carriage returns are interpretted as an end-of-record marker, with the new para of the Note appearing in the IRN column of the next row.

I have the document currently in the format of
IRN "[Notes]"

I put the notes within quotes, hoping this would parse the carriage retuens between as simple line breaks, but no joy.

Can anyone advise me on how to convert this Word doc into a format that I can import to EMu, with full acceptance of the paragraph/carriage returns in the Notes Field?


Thanks

Mark - NGA

Edited by: - 01-Jan-70 09:00:00

Mark Bradley – Assistant Registrar, Documentation (EMu)
National Gallery of Australia

Mark Bradley
Assistant Registrar (EMu Guy)
useravatar
Offline
147 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Converting Word to Excel - carriage returns within a field

Ok - that didn't quite work. The format of the documents is

IRN (in Bold) ->Tab Notes entry (including para marks), then a para to end the record.

Hope someone can help me out.

Mark Bradley – Assistant Registrar, Documentation (EMu)
National Gallery of Australia

Mark Bradley
Assistant Registrar (EMu Guy)
useravatar
Offline
147 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Converting Word to Excel - carriage returns within a field

First a disclaimer, I developed this solution on one of the test clients provided by KE as my organization has not completed our conversion.

You might consider using notepad to work on the formatting of the CSV instead of Word. It will make eliminating extraneous carriage returns and formatting. This is the test data I was able to successfully add to the Association notes field

irn, AssAssociationComments0
1, "These are some notes with a
carriage return"
3, notes notes notes
4, notes notes notes
5, "notes,
notes notes"

If it would be helpful I'd be glad to look at specific example from your data.

Good luck.

Scott Williams

Scott Williams
Database Administrator
useravatar
Offline
15 Posts
Male 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Converting Word to Excel - carriage returns within a field

Hi Mark,

You might give this a try: Save the Word file as a text document, rename the text document's extension from .txt to .csv, then open the csv file in Excel and see if the data is in the correct fields.

If that doesn't work, you could try using a hex editor to search the text file for the appropriate "carriage return/line feed" combinations (hex 0D 0A) and replace them with just a line feed (0A).

I'll be happy to take a look at your Word document if you upload it.

Best,
Tom

Thomas J. Trombone
American Museum of Natural History

Thomas Trombone
Data Manager, Ornithology
useravatar
Offline
54 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Converting Word to Excel - carriage returns within a field

Thanks guys.

I tried this morning to follow Tom's advice, of saving from Word to Text and then renaming to CSV and opening in Excel. Not sure of the options to choose on saving as text though. Options are:

Text Encoding: Windows (default / MS Dos / Other (choose from list, default is Western European Windows)
options: Insert Line Breaks y/n
End Lines with: CR-LF / CR only / LF only / LF-CR

If I accept the defaults, then open the text file in Excel (either doing Open With directly or opeingin into Excel Import Wizrd) I am seeing the same results - the IRN is fine, the tab is correctly considered to be a column break, but each carriage return nested in the Notes data is interpretted as an end-of-record break.

So my excel sheets looks like

IRN1 Notes_Para_1a
Notes_Para_1b
Notes_Para_1c
IRN2 Notes_Para_2a
Notes_Para_2b
IRN3 Notes_Para_3a
IRN4 ""

Hope you can visualise that.

I can post a sample of data - just not sure of the best format to post it in!

Mark - NGA

Mark Bradley – Assistant Registrar, Documentation (EMu)
National Gallery of Australia

Mark Bradley
Assistant Registrar (EMu Guy)
useravatar
Offline
147 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Converting Word to Excel - carriage returns within a field

ok - forum stripped out the white space, so that last post didn't work the way I wanted. Please imagine a column break bewteen the IRNx and Notes_Para_Xa fields.

Mark Bradley – Assistant Registrar, Documentation (EMu)
National Gallery of Australia

Mark Bradley
Assistant Registrar (EMu Guy)
useravatar
Offline
147 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Converting Word to Excel - carriage returns within a field

And further information - the original text file was created by building a Crystal report and exporting the results to Editable RTF.

The Crystal report had the IRN (bolded) in Details A section, and then the Notes in Details B section below it, with a line drawn (at either the top of Details A or bottom of Details B) to separate the records.

Mark Bradley – Assistant Registrar, Documentation (EMu)
National Gallery of Australia

Mark Bradley
Assistant Registrar (EMu Guy)
useravatar
Offline
147 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Converting Word to Excel - carriage returns within a field

Try opening the CSV in notepad. Your file should look something like this. Be sure to include the quotes.

irn, notes
1, "note with a carriage return
right here"
2, "here is another set of notes
about an object
with a carriage return"

Scott Williams
Database Administrator
useravatar
Offline
15 Posts
Male 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Converting Word to Excel - carriage returns within a field

Ta Scott.

Looking at the Text doc in Notepad, I see this:

170172 “Inkahoots notes:
The trial of Sir Joh Bjelke Petersen in 1991 was a source of much mirth amongst the politically active left in Queensland. When an advertisement appeared in the Courier Mail placed by the Friends of Sir Joh for the sale of old election posters to raise money for his upcoming court costs, it was too much for the Corporate Art Terrorists to resist. Very little of the original artwork for the advertisement needed to be altered in order to change its meaning so completely. However, the identities of the Terrorists have been protected to avoid the possibility of copyright prosecution. [Inkahoots to ANG, 16 February 1993 RB files].”
162187 “Another impression held by the Newcastle Region Art Gallery was acquired in 1961 (1961.18) and is impression ‘10/16’.”
162470 “Title confirmed by the artist [email to NGA, November 2007].
40793 “Same print as Acc. No. 92.1246”
40773 “”
40775 “”
40778 “”
40795 “The exhibition was held at Peter Bray Gallery, 435 Bourke Street, Melbourne, 27 April - 6 May 1954”
40779 “”

The only one of these that is in paras is the top one, 170172.

Mark

Mark Bradley – Assistant Registrar, Documentation (EMu)
National Gallery of Australia

Mark Bradley
Assistant Registrar (EMu Guy)
useravatar
Offline
147 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Converting Word to Excel - carriage returns within a field

Hi Mark,

I saved the above excerpt as a csv file. Playing around with it I found three things that look like problems:

1. There is no comma between the IRN and the Notes field.
2. The Notes are delimited by open-double-quote “ and close-double-quote ” characters instead of just the double-quote character ".
3. There are carriage-return/line-feed character combinations in the middle of a cell's data (for example following Inkahoots notes:) instead of just line-feeds. These combinations cause Excel to start new rows.

I fixed all three by using a hex editor (I used HxD Hex Editor, a free editor available at http://tinyurl.com/clu3uu.)

First I replaced the space/open-double-quote combination with a comma/double-quote combination (using the text data type search option):
Replace:

with:
,"

Then I replaced all close-double-quote characters with the double-quote character (again using the text data type search option):
Replace:

with:
"

Then I replaced all carriage-return/line-feed combinations with just line-feeds (using the hexadecimal data type search option):
Replace:
0D0A
with:
0A

When I then opened the csv file in Excel, I got the result I think you're looking for. It's a bit circuitous but it seems to work; let me know if you need help doing it. (By the way, it looks like the note for IRN 162470 is missing its end quote; it'll be best if all such problems are cleared up before doing the search-and-replace operations.)

Best,
Tom

Attachment: mark-2.zip

Thomas J. Trombone
American Museum of Natural History

Thomas Trombone
Data Manager, Ornithology
useravatar
Offline
54 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.

Re: Converting Word to Excel - carriage returns within a field

Thanks Thomas, and Scott.

Simon King from the National Museum of Australia actually followed similar steps to you, Thomas, to help resolve this.

For any other readers, the issue was as Thomas pointed out - the para breaks in the Notes data were both Carriage Return AND Line Feed, and the quote marks were open and close quotes, inserted by Microsoft Word mind-reading function.

It can be fixed using just Word and notepad. Here's how:

1. Saved the RTF to Text format, and a file conversion options box pops up.
2. Change the End Lines With option to "End with LF only" - i.e. end with line feed, not line feed + Carriage return.
3. Tick the "allow character substitution" which replaces the open and close quotes with regular quote mark character.
4. (possibly unnecessary) reopen the text file in word, do a find replace, substituting commas for tabs, then save.
5. Change the filetype extension from .txt to .csv

The CSV file can be opened in Excel and checked.

No solution to the missing end quotes issue - that was just the result of manually inserting quote marks around 4000+ records! and was easily picked up when running a test import of the CSV.

Thanks again all for your help.

Mark - NGA

Mark Bradley – Assistant Registrar, Documentation (EMu)
National Gallery of Australia

Mark Bradley
Assistant Registrar (EMu Guy)
useravatar
Offline
147 Posts
Male  Website 
Administrator has disabled public posting. Please login or register in order to proceed.
There are 0 guests and 0 other users also viewing this topic