Export document text to CSV

Scrivener Wish List
1

I LOVE the Export > Outliner Contents as CSV… function. And I would love it (and you all) even more if it would also (optionally) export the document text as well.

I have created a work-around, but it requires a pulling in a compile of the text to the CSV using a VBA script, so it’s a bit cumbersome and inelegant. It would be great to get all the document text data straight to the CSV file.

The goal is to get the document text into one cell - after importing the CSV to Excel. I realize there’a a potential glitch if there are tabs in the text, so the writer would have to just not use tabs, or the export code would need to strip those out.

Thanks for considering - and BTW, I’m in awe of how beautifully Scrivener has been designed and programmed,

– M

2

Hi M –

If you were on a PC, I’d recommend creating a script in AutoHotKey to walk through the binder and copy and paste each document into Custom Meta Data, so that its text would reside in the outliner for export. Don’t know if there’s a similar capability on the Mac, but it would not be a complicated user script, relying on fixed cursor location and keystroke playback.

Whereas even if L&L are thrilled with this idea for Scrivener, its implementation would not be timely.

Cheers – Jerome

3

^This.

I used to do exactly that with AutoHotKey on Windows. (Well, I copy the text into the Synopsis field rather than a custom meta-data field, but same difference).

Since I’ve switched to Mac I now use KeyboardMaestro to do the same thing. You could maybe use AppleScript and/or Automator instead, but I never put the time in to learn those so I can’t say for certain.

4

Hey - thanks for the replies and the idea of using a macro to transfer data to a (custom) meta-data field. I hadn’t thought of that.

But I’m not sure that would be better than my current solution:

  1. Export the document text to separate text files (they’re Markdown formatted): File –> Export –> Files…
  2. Export the outline to CVS: File –> Export –> Outliner Contents As CSV
  3. Open the CSV in Excel and run a VBA a script that matches the Titles to the text documents a copies the text into the appropriate row in the spreadsheet.

It takes a few steps, but it works. Happy to share the VBA script if anyone would find it useful.

5

mclemens, I’d be interested in your VBA script. I’ve been looking to export the CSV data along with the text as well. Thanks.

6

I just spent the day coming up with a better way to do this. The VBA script is below.

Instead of using File –> Export –> Outliner Contents As CSV, I’ve switched to using using compiling – which much more flexible.

I compile only the ‘Text’, and then output anything else I want by putting place holder tags in the Formatting -> Section Layout… Prefix’s, e.g. tab separated <$title>, <$parenttitle>, <$keywords> etc., You can output more things this way AND use Enumerated Outline to get outline numbering into the output.

For the script to work correctly:

  • Be sure that everything in the Prefix or Suffix is tab separated
  • you must put a % + Tab character as the first thing in the Section Layout Prefix.
  • The text cannot contain tabs
  • Compile to plan text format - with the file name ‘scriv.txt’

Open a new Excel worksheet, run the script, your compiled data should appear in the spreadsheet all nicely lined up, with all the document Text in a single cell (yeah!).

Sub importScriv()

Dim recFields As Variant
Dim rec, rec2 As String
Dim index, row, col, numcols
Dim FileName

FileName = ThisWorkbook.Path & "/scriv.txt"

Open FileName For Input As #1
Line Input #1, rec
' Get next line
Line Input #1, rec2


' Start at row 2, so we can have a header row
row = 2


While Not EOF(1)
    ' The Scrivner compile needs to put a % + Tab character as the first thing in the Section Layout Prefix
    ' The % is used to demark Scrivener documents
    Do While Left(rec2, 1) <> "%"
        rec = rec & Chr(13) & rec2
        ' Get next line
        Line Input #1, rec2
        If EOF(1) Then GoTo Out
    Loop

Out:

    ' Split the lines at the tab seprators
    recFields = Split(rec, vbTab)
    numcols = UBound(recFields) - LBound(recFields) + 1

    ' put the data in the row
    For col = 1 To numcols
        ' Remove any leading carriage returns
        Do While Left(recFields(col - 1), 1) = Chr(13)
          recFields(col - 1) = Right(recFields(col - 1), Len(recFields(col - 1)) - 1)
        Loop

        Cells(row, col) = recFields(col - 1)

    Next col

    ' We got a % in rec2 so set rec to rec2
    rec = rec2

    ' increment the row
    row = row + 1

    ' Get next line
    If Not EOF(1) Then
        Line Input #1, rec2
    End If
Wend

Close #1

' Finally, delete the first column that contains the % document separator characters
Columns(1).EntireColumn.Delete

End Sub
7

Wow, thank you so much, mclemens. I used it, mostly successfully–had to tweak things a bit because I had tabs in the text. Thank you.

8

Cool - glad to hear it worked for you.

My text is all written in markdown, so I didn’t have to worry about tabs or other formatting. But if you want to maintain the use of tabs you should be able change the field-separator from tabs to something else. Just separate the things in the Prefix or Suffix with something like ‘$$$’ instead of tabs, and then change the line in the VB script from:
recFields = Split(rec, vbTab)
to
recFields = Split(rec, “$$$”)

9

Thanks so much. I’m learning a lot.