Skip navigation

Numbers '09 2.1 - setting import delimiter for .txt or .rtf

311 Views 7 Replies Latest reply: Oct 24, 2011 3:04 PM by KOENIG Yvan RSS
Rasuul Level 1 Level 1 (0 points)
Currently Being Moderated
Oct 23, 2011 2:44 PM

Hi there -

 

I have a series of text files with leading numbers that I'd like to remove. The files are lists, where each line is enumerated sequentially & these line-numbers have to be removed for further processing.

 

This is too painful to do manually (takes forever), but the content is not in English and so Excel, which allows me to set the delimited & correctly parses the information, doesn't read the content properly (even after copy paste into a word processor that usually does) whereas Pages will handle the content, but for the life of me I can't get it to parse the information - so it's effectively useless.

 

The other threads I've read basically suggest that one has to manually insert tabs between the fields - is this actually the case? Is there really no way to control how Pages '09 parses imported data?

 

As a final add-on: I know this would be relatively easy to do with a script, but I havne't a clue where to get started with scripting for the Mac, so if you read this & have a suggestion as to where I might get started (generally, or w.r.t. this particular project), I would very much appreciate the enlightenment.

 

Thanks in advance and all the best,

 

Rax

  • Jerrold Green1 Level 7 Level 7 (28,175 points)

    Rax,

     

    In Pages or another text editor, replace whatever now separates the fields with a Tab character. Then Paste into Numbers.

     

    Jerry

  • Jerrold Green1 Level 7 Level 7 (28,175 points)

    Most files that are intended to be imported into either a database or spreadsheet will have proper delimiters already placed appropriately. It's only if the files are not properly formatted that you will need to insert either commas or tabs, and Find and Replace will do that nicely in most situations.

     

    I will not judge or comment further on Apple's design decision in this case.

     

    Jerry

  • Jerrold Green1 Level 7 Level 7 (28,175 points)

    Rax,

     

    Numbers is a much leaner application than Excel. It costs less, it does less, and in the process of doing less, has a rather cleaner and easier interface, for the things that it does do.

     

    Regarding parsing the raw data file with automation, I believe that Yvan has developed script to parse some types of data and if it doesn't serve your needs as-is, it could probably be modified. He will probably chime in here, but if not, you can search the discussions for his posts on the subject.

     

    Using Find and Replace in Pages is a basic skill that you can study in the Pages User Guide, downloadable from the Help menu. In short, you "Find" the current delimiters, perhaps space characters, and you "Replace" with the character(s) of your choice, either one at a time, or "Replace All" globally. The only trick in using Find and Replace is to make sure that you are using the long form of the tool. If the disclosure triangle is pointing down, click it to expand the pane.

     

    If more logic is required to parse the document, it can be done with formulas in Numbers, or with a script.

     

    Jerry

  • Badunit Level 6 Level 6 (10,760 points)

    Find and Replace is a feature of TextEdit, Numbers, Pages, etc.  It does not require Automator.  Open your document then do Command F to bring up the Find/Replace window.  Type the incorrect delimiter in the "find" field and type the correct one in the "replace" field.  If you want to replace with a tab, use Option Tab to type the tab.

  • Level 8 Level 8 (41,760 points)

    If the leading numbers are separated from the rest of lines by space character, you file is not a tab delimited one.

    If a file whose name ends with .txt is a tab delimited one, it may be opened by Numbers with no problem.

    If its name ends with .csv, thingsa re more complicated.

    If the system use the period as decimal separator, the values MUST be separated by commas

    but

    if the system use the comma as decimal separator, the values MUST be separated by semi-colons.

     

    I have a possible understanding of your problem.

    Maybe values are separated by single or several spaces which is the awful format used in the typewriter era.

    I guess that with this structure, Excel use its knowledge of English language to split the lines.

     

    As I don't like to work in the Terminal, I wrote a short script achieving what you described.

     

    --{code}

    set fichier_source to choose file with prompt "Select a text file" of type {"public.plain-text"}

    set le_fichier to POSIX path of (fichier_source as text)

    do shell script "sed -i.bak -e 's/^[0-9]* *//' " & quoted form of le_fichier

    tell application "Pages"

      open le_fichier

    end tell

    --{code}

     

    Yvan KOENIG (VALLAURIS, France) mardi 25 octobre 2011 00:03:57

    iMac 21”5, i7, 2.8 GHz, 4 Gbytes, 1 Tbytes, mac OS X 10.6.8 and 10.7.2

    My iDisk is : <http://public.me.com/koenigyvan>


    Please : Search for questions similar to your own before submitting them to the community

     

     

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.