Previous 1 2 Next 19 Replies Latest reply: Aug 17, 2012 2:25 PM by namsaT
VerusEx Level 1 Level 1 (0 points)

Lots of iPad app collect data as space delimited text files ( where is tab key on iPad) how can I import these files into numbers for analysis and plotting?

  • Barry Level 7 Level 7 (29,215 points)

    Provided there are no spaces in the data itself, you could:

    • import the data into a text processor or word processor application.
    • use find/replace to replace the spaces with tabs.
    • copy the data.
    • paste into a Numbers table.

     

    Regards,

    Barry

  • VerusEx Level 1 Level 1 (0 points)

    Hate to say but this was really simple in Excel, surprised

     

    Have to take a look at Pages Macros ... Life is never quite that simple

     

    Thanks

  • Level 8 Level 8 (41,790 points)

    There is no macros feature in Pages.

     

    May you give more details ?

     

    I know two kinds of files using space as delimiters.

     

    (1) there is a single space between values from two adjacent columns

    (2) cell's contents are padded with space characters so that displaying the datas with a monospaced font (like Courier) the values are correcly aligned.

     

    The first case is easy to treat : Barry gave a valuable tip for that.

     

    Second case is more complicated.

     

    Before trying to design a script for that, I wish to know which is exactly your problem.

     

    Yvan KOENIG (VALLAURIS, France) lundi 16 avril 2012

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

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

  • Wayne Contello Level 6 Level 6 (15,480 points)

    You can convert case (2) in Yvans post to a tab delimited file by replaceing groups of spaces with a tab.  Once you have the spaces removed that are common (converted to tabs) you delete the remaining spaces (so long as they are not part of the data)-- replace spaces with empty string "".

  • Level 8 Level 8 (41,790 points)

    What if two values are separated by a single space ?

    With your scheme they will be treated as a single one.

    What if a cell in, say column C is empty?

    The spaces padding value of column B and spaces padding spaces of column C will be treated as a single delimiter.

     

    Yvan KOENIG (VALLAURIS, France) lundi 16 avril 2012

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

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

  • Wayne Contello Level 6 Level 6 (15,480 points)

    All good points.  In the abscence of know what the data looks like the problem solution can be very simple to a script-- just offering the simplest solution if the data allows.

  • jaxjason Level 4 Level 4 (3,520 points)

    The wording in excel that you use to import the data will help. If you were going to import into excel using the External data command for importing a text file, please give the details for the cases below.

     

    Case 1 from Yvans response will use the "delimited" on the import box, and then you choose the delimiter on the next page, sounds like space in your case. Along with wether continuous text strings use quotes around them, and the spaces inside should be ignored for column delimiation. ("Text Qualifier" in the dialog.

     

    Case 2 from Yvans response is "Fixed Width" where the first 8 characters might be the persons ID number, the next 20 are the first name, etc... And they are "padded" with spaces.

     

    Please let us know which option you use, and the selections from with the second page if your in Case1. (Or supply dummy data in the proper format. Three or four rows would be good to start with. Headers even better.

     

    Jason

  • Barry Level 7 Level 7 (29,215 points)

    KOENIG Yvan wrote:

     

    What if two values are separated by a single space ?

    Or by no spaces at all.

     

    Sounds like the dBase format (.dbf), using fixed field lengths to keep the data in the right place. The files start with a 'map' giving the field names, types and length, The data itself is just a single string of text, with each record using the same number of characters, and each field within that block using a fixed number of characters.

     

    Excel has been around long enough that .dbf reading was likely included as a 'necessary' talent. The various OpenOffice.org applications also include the ability to read .dbf files into their spreadsheet modules.

     

    Numbers, designed for use with smaller tables, and entering the market long after the dBase format fell into disuse, does not, nor is it a suitable tool for the very large data files usually found in .dbf files.

     

    Regards,

    Barry

  • Level 8 Level 8 (41,790 points)

    Well seen Barry. I missed this case.

     

    I think that I'm on the road with a script but I have other tasks to achiev before.

     

    Yvan KOENIG (VALLAURIS, France) lundi 16 avril 2012

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

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

  • VerusEx Level 1 Level 1 (0 points)

    Guys lets not get carried away here. I'm new to iWork but high level MS Office user. I was just wondering if I had missed a simple tool for importing space delimited data.

     

    Here's an example of what I get from my iPad

     

    weight

     

    base weight.  212 preop

     

    1/20/12  release MGH

    1/21/12.      232

    1/22/12.   228

    1/23/12.  226.0

    1/24/12.  222.5

    1/25/12.  217.5  X Beta Block

    1/26/12.  213.5

    1/27/12.  212.0

    1/28/12.  209.0

    1/29/12.  206.5

    1/31/12.    203.0

    2/1/12.      203.0  X  diuretic

    2/2/12.    202.5

    2/3/12.   200.5

    2/4/12.   200.0

    2/5/12.   200.0

    2/6/12.   201.0

    2/7/12.   201.0  X 1000cc fluid

    2/9/12.  202.0

    2/10/12. 202.5

    2/11/12.   203.0

    2/12/12. 204.5

    2/13/12. 205.5

    2/14/12. 205.0

    2/15/12.  205.5

    2/16/12.  205.5

    2/17/12    206.5

    no scale travel to Tucson

    2/26/12.   205.0 diuretic

    2/27/12.    203.0e

    2/28/12.    202.5

    2/29/12.    201.6

    3/1/12.         201.6

    3/2/12.       198.6

    3/3/12.       198.6

    3/4/12.       196.4

    3/5/12.        194.8

    3/6/12.         193.6

    3/7/12.          192.4

    3/8/12.          190.6

    3/9/12.          191.8

    3/10/12.        191.0

    3/11/12.          190.0

    3/12/12.        186.4

    3/13/12.       185.8

    3/14/12.       184.4 chemo

    3/15/12.        186.2

    3/16/12.        187.0

    3/17/12.        184.4

    3/18/12.        181.6

    3/19/12.        179.8

    3/20/12.      178.2  x diuretic

    3/21/12.        176.8

    3/22/12.      177.6

    3/23/12.      179.6

    3/24/12.      179.4

    3/25/12.      180.8

    3/26/12.      181.2

    3/27/12.      181.2

    3/28/12.      184.8

    3/29/12.      186.0

    3/30/12.      187.6

    3/31/12.         187.6

    4/01/12.         191.2

    4/02/12.       189.8

    4/03/12.      189.8

    4/04/12.      190.4 chemo

    4/05/12.       192.4

    4/06/12.        193.6.  xpump

    4/07/12.        188.8 +diuretic

    4/08/12.        188.8

    4/09/12.        188.8

    4/10/12.           188.2

    4/11/12.             188.4

    4/12/12.           188.4

    4/13/12.          188.4

    4/14/12.          188.2

    4/15/12.           191.4

    4/16/12.           191.4

     

     

     

    No tragedy to fix in pages to create plots for Docs

     

    Thanks

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Sent from my iPad

  • Jerrold Green1 Level 7 Level 7 (29,935 points)

    You're right, that takes only about two minutes to fix in Pages. I'm surprised at the inconsistency of the field separators coming out of the iPad.

     

    Jerry

  • Wayne Contello Level 6 Level 6 (15,480 points)

    I don't have any iOS devices with iWork so could not compare there.  I was able to imports data by performing a repeated text replacesment of "." followed by some number of spaces (which I copied from the data you posted) into a Pages doc.  It took about 5 copy paste repetitions to get all the "."+spaces out.  If I had to do that very often I would definitely makes a script to fix it for me.:

     

    Screen Shot 2012-04-16 at 7.50.11 PM.png

  • Level 8 Level 8 (41,790 points)

    Hi Wayne

     

    In the posted datas, some dates end with a period but some don't.

    Between values there are :

    period (not always. None after the 1st date and after the one just above "no scale travel to Tucson")

    space

    Given the source which I used, I got different spaces.

    In one case they were NO BREAK spaces

    In the example which you posted, the first missing period is revealed

    The second one isn't revealed because you didn't treated the entire set of datas.

    I assume that when there are datas after the decimal values, they are supposed to be in a 3rd column.

     

    Given these features, I wrote a script deciphering automatically the set of datas.

    Adjust the property « fromClipboard » to match what you use as source.

     

    --{code}

    --[SCRIPT split-table-from-ipad]

    (*

    Treat the set of datas imported from iPad by VerusEx

    see Apple Discussions forum :

    https://discussions.apple.com/thread/3879328?tstart=0

     

    Yvan KOENIG (VALLAURIS, France)

    2012/04/17

    *)

     

    property fromClipboard : true

    (*

    true = get datas from the clipboard

    false : get the datas from a text file

    *)

    on run

              if fromClipboard then

                        set lesValeurs to the clipboard as text

              else

                        set lesValeurs to read (choose file of type {"public.plain-text"})

              end if

      (*

    Instruction removing NO BREAK spaces which I got when I copied from the mail which I received from the forum.

    *)

              set lesValeurs to my supprime(lesValeurs, character id 160)

      (*

    Replace every groups of space characters by a single one

    *)

              repeat

                        if lesValeurs does not contain "  " then exit repeat

                        set lesValeurs to my remplace(lesValeurs, "  ", space)

              end repeat

      (*

    Replace single digit + space by single digit + tab.

    Useful to take care of cases when period is missing,

    or to split strings stored after a digit + a space

    *)

              repeat with i from 0 to 9

                        set lesValeurs to my remplace(lesValeurs, (i as text) & space, (i as text) & tab)

              end repeat

      (*

    Replace period + space by a tab

    *)

              set lesValeurs to my remplace(lesValeurs, ". ", tab)

      (*

    Save the deciphered datas in a temporary text file

    *)

              set leFichier to (path to temporary items as text) & "azertyuiop.txt"

              my writeTo(leFichier, lesValeurs, text, false)

      (*

    Open the temporary file in Numbers

    *)

              tell application "Numbers" to open leFichier

    end run

     

    --=====

    (*

    replaces every occurences of d1 by d2 in the text t

    *)

    on remplace(t, d1, d2)

              local oTIDs, l

              set oTIDs to AppleScript's text item delimiters

              set AppleScript's text item delimiters to d1

              set l to text items of t

              set AppleScript's text item delimiters to d2

              set t to "" & l

              set AppleScript's text item delimiters to oTIDs

              return t

    end remplace

     

    --=====

    (*

    removes every occurences of d in text t

    *)

    on supprime(t, d)

              local oTIDs, l

              set oTIDs to AppleScript's text item delimiters

              set AppleScript's text item delimiters to d

              set l to text items of t

              set AppleScript's text item delimiters to ""

              set t to l as text

              set AppleScript's text item delimiters to oTIDs

              return t

    end supprime

     

    --=====

    (*

    Handler borrowed to Regulus6633 - http://macscripter.net/viewtopic.php?id=36861

    *)

    on writeTo(targetFile, theData, dataType, apendData)

      -- targetFile is the path to the file you want to write

      -- theData is the data you want in the file.

      -- dataType is the data type of theData and it can be text, list, record etc.

      -- apendData is true to append theData to the end of the current contents of the file or false to overwrite it

              try

                        set targetFile to targetFile as text

                        set openFile to open for access file targetFile with write permission

                        if not apendData then set eof of openFile to 0

      write theData to openFile starting at eof as dataType

      close access openFile

                        return true

              on error

                        try

      close access file targetFile

                        end try

                        return false

              end try

    end writeTo

     

    --=====

    --[/SCRIPT]

    --{code}

     

    Yvan KOENIG (VALLAURIS, France) mardi 17 avril 2012

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

    My Box account  is : http://www.box.com/s/00qnssoyeq2xvc22ra4k

  • VerusEx Level 1 Level 1 (0 points)

    Yvan

     

    Thanks ... Very impressive

     

    I plan to use your script as an AppleScript leaning example.

     

    I suspect my approach is like yours, I need to understand my tools.

     

    Any suggestions on best place to start learning AppleScript? I see quite a bit available but I always wonder if it up to date.

     

    I wonder if we'll see an iOS version in the future. Not that I plan to give up my iMac but find my Mac time is way down now except for World of Warcraft.

     

    Thanks again, Roland

Previous 1 2 Next