Monday 26 March 2012

CSV importation

CSV is a very common way to write a table of data. It's very easy to import it using parse, here a simple function:

csv-import: func [
    "Import a CSV file transforming it in a series."
    file [file!] "CSV file"
    /local temp temp2
    ] [
    temp: read/lines file
    temp2: copy []
    foreach item temp [append/only temp2 (parse/all item ",") ]
    return temp2    
    ]
   


And here an example, the following table:
TITLE AUTHOR Editor
Robots and Empire Isaac Asimov Mondadori
Afternoon of Earth Brian W. Aldiss Minotauro
Absolute OpenBSD "2d Edition" Michael W. Lucas No Starch Press
The space merchants Frederik Pohl, C. M. Kornbluth Mondadori

It can be represented in CSV format this way:

TITLE,AUTHOR,EDITOR
Robots and Empire,Isaac Asimov,Mondadori
Afternoon of Earth,Brian W. Aldiss,Minotauro
"Absolute OpenBSD ""2d Edition""",Michael W. Lucas,No Starch Press
The space merchants,"Frederik Pohl, C. M. Kornbluth",Mondadori


Writing it on temp.csv file and using the above functions:

>> a: csv-import %temp.csv
== [["TITLE" "AUTHOR" "EDITOR"] ["Robots and Empire" "Isaac Asimov" "Mondadori"] ["Afternoon of Earth" "Brian W. Aldiss" "Minotauro...

So it's extremely easy to retrieve data:

>> a/1/1
== "TITLE"
>> a/1/2
== "AUTHOR"
>> a/2/1
== "Robots and Empire"

Or this way:

>> for i 1 5 1 [print a/:i/1]
TITLE
Robots and Empire
Afternoon of Earth
Absolute OpenBSD
The space merchants

2 comments:

  1. If it doesn't work with your data, you may try this with Excel-compatible handling of bad data (I use it every day in production code): http://www.rebol.org/view-script.r?script=csv-tools.r
    Thank to Brian Hawley

    ReplyDelete
  2. If your CSV contains "" because some data contains commas, like:
    "Hello, how are you?","I'm fine, and you?","Me too!"
    you should use this version of parse rules:

    csv-import: func [
    "Import a CSV file transforming it in a series."
    file [file!] "CSV file"
    /local temp temp2 temp3 temp4
    ] [
    temp: read/lines file
    temp2: copy []
    foreach item temp [
    temp3: copy []
    parse item [{"} copy temp4 to {","} (append temp3 temp4) some [ thru {","} copy temp4 to {","} (append temp3 temp4)] thru {","} copy temp4 to {"} (append temp3 temp4) ]
    append/only temp2 temp3
    ]
    return temp2
    ]

    ReplyDelete