Re: importing spreadsheet rows and columns
A (late) follow-up on this: I have got some good experience with an open-source Excel-Plugin named Excel2LaTeX. This plugin provides a toolbar button to convert the selected parts of an Excel table into LaTeX source code that can then either be saved into a file or into the clipboard. I use this to "copy" tables from Excel into an empty LaTeX document and then import this document into a new LyX document. From there I use again copy and paste to copy the LyX table into the final document. Works like a charm and also preserves much of the formattings (e.g. bold font headers). Daniel
Re: importing spreadsheet rows and columns
The simplest method (if you have a table of the right size set up in LyX) is to paste with shift held down as well i.e. shift+ctrl+v - that puts the data in to the cells, rather than all in one cell. I don't know why it works but it does and will save you having to export all of your spreadsheets. Hope that helps! jf7 wrote: > > > Hi, I'm using LyX 1.5.3 and have a situation where I need to put > spreadsheet data into tables in LyX -- several of them actually. I'm > able to paste the spreadsheet data into LyX. But so far I haven't > figured out a way other than the tedious method of cutting and pasting > every cell's contents one-by-one into place in the table. > > Is there any way I can at least do columns or rows at a time if not a > the whole block of columns and rows at once? > > thanks, > > jamie faunt > > > > -- View this message in context: http://www.nabble.com/importing-spreadsheet-rows-and-columns-tp17288380p17517948.html Sent from the LyX - Users mailing list archive at Nabble.com.
Re: importing spreadsheet rows and columns
Steve Litt wrote:
On Wednesday 28 May 2008 09:54, rgheck wrote:
Helge Hafting wrote:
[EMAIL PROTECTED] wrote:
Hi, I'm using LyX 1.5.3 and have a situation where I need to put
spreadsheet data into tables in LyX -- several of them actually. I'm
able to paste the spreadsheet data into LyX. But so far I haven't
figured out a way other than the tedious method of cutting and
pasting every cell's contents one-by-one into place in the table.
Is there any way I can at least do columns or rows at a time if not a
the whole block of columns and rows at once?
There is another option too.
Gnumeric can export a spreadsheet as latex code for a longtable.
There's also a CSV importer now, and almost any spreadsheet will export
CSV.
In what version did the CSV importer first make its appearance?
I think it's new in 1.5.5. But it's just a python script, so it could be
run independently. I've attached it in case you want to play with it.
rh
#! /usr/bin/env python
# -*- coding: utf-8 -*-
# file csv2lyx.py
# This file is part of LyX, the document processor.
# Licence details can be found in the file COPYING.
# author Hartmut Haase
# author José Matos
# Full author contact details are available in file CREDITS
# This script reads a csv-table (file name.csv) and converts it into
# a LyX-table for versions 1.5.0 and higher (LyX table format 276).
# It uses Python's csv module for parsing.
# The original csv2lyx was witten by Antonio Gulino <[EMAIL PROTECTED]>
# in Perl for LyX 1.x and modified for LyX table format 276 by the author.
#
import csv, unicodedata
import os, sys
import optparse
def error(message):
sys.stderr.write(message + '\n')
sys.exit(1)
header = """#csv2lyx created this file
\lyxformat 276
\\begin_document
\\begin_header
\\textclass article
\\inputencoding auto
\\font_roman default
\\font_sans default
\\font_typewriter default
\\font_default_family default
\\font_sc false
\\font_osf false
\\font_sf_scale 100
\\font_tt_scale 100
\\graphics default
\\paperfontsize default
\\papersize default
\\use_geometry false
\\use_amsmath 1
\\use_esint 0
\\cite_engine basic
\\use_bibtopic false
\\paperorientation portrait
\\secnumdepth 3
\\tocdepth 3
\\paragraph_separation indent
\\defskip medskip
\\papercolumns 1
\\papersides 1
\\paperpagestyle default
\\tracking_changes false
\\output_changes false
\\end_header
\\begin_body
\\begin_layout Standard
\\align left
\\begin_inset Tabular
"""
cell = """
\\begin_inset Text
\\begin_layout Standard
%s
\\end_layout
\\end_inset
"""
footer = """
\\end_inset
\\end_layout
\\end_body
\\end_document
"""
# processing command line options
# delegate this to standard module optparse
args = {}
args["usage"] = "Usage: csv2lyx [options] csvfile [file.lyx]"
args["description"] = """This script creates a LyX document containing a table created from a
comma-separated-value (CSV) file. The resulting LyX file can be opened
with LyX 1.5.0 or any later version.
If no options are given csv2lyx will try to infer the CSV type of the csvfile,
"""
parser = optparse.OptionParser(**args)
parser.set_defaults(excel='', column_sep='')
parser.add_option("-e", "--excel", metavar="CHAR",
help="""CHAR corresponds to a CSV type:
'e': Excel-generated CSV file
't': Excel-generated TAB-delimited CSV file""")
parser.add_option("-s", "--separator", dest="column_sep",
help= """column separator
't' means Tab""")
group = optparse.OptionGroup(parser, "Remarks", """If your CSV file contains special characters (e. g. umlauts,
accented letters, etc.) make sure it is coded in UTF-8 (unicode).
Else LyX will loose some cell contents. If your CSV file was not written according to the "Common Format and MIME Type for Comma-Separated Values (CSV) Files" (http://tools.ietf.org/html/rfc4180) there may be unexpected results.""")
parser.add_option_group(group)
(options, args) = parser.parse_args()
# validate input
if len(args) == 1:
infile = args[0]
fout = sys.stdout
elif len(args) ==2:
infile = args[0]
fout = open(args[1], 'w')
else:
parser.print_help()
sys.exit(1)
if not os.path.exists(infile):
error('File "%s" not found.' % infile)
dialects = {'' : None, 'e' : 'excel', 't' : 'excel-tab'}
if options.excel not in dialects:
parser.print_help()
sys.exit(1)
dialect= dialects[options.excel]
# Set Tab, if necessary
if options.column_sep == 't':
options.column_sep = "\t"
# when no special column separator is given, try to detect it:
if options.column_sep or dialect :
reader = csv.reader(open(infile, "rb"), dialect= dialect, delimiter=options.column_sep)
else:
guesser = csv.Sniffer()
input_file = "".join(open(infile,'rb').readlines())
try:
dialect = guesser.sniff(input_file)
reader = csv.reader(open(infile, "rb"), dialect= dialect)
except:
reader = csv.reader(open(infile, "rb"), dialect= dialect, delimiter
Re: importing spreadsheet rows and columns
On Wednesday 28 May 2008 09:54, rgheck wrote: > Helge Hafting wrote: > > [EMAIL PROTECTED] wrote: > >> Hi, I'm using LyX 1.5.3 and have a situation where I need to put > >> spreadsheet data into tables in LyX -- several of them actually. I'm > >> able to paste the spreadsheet data into LyX. But so far I haven't > >> figured out a way other than the tedious method of cutting and > >> pasting every cell's contents one-by-one into place in the table. > >> > >> Is there any way I can at least do columns or rows at a time if not a > >> the whole block of columns and rows at once? > > > > There is another option too. > > Gnumeric can export a spreadsheet as latex code for a longtable. > > There's also a CSV importer now, and almost any spreadsheet will export > CSV. In what version did the CSV importer first make its appearance? SteveT Steve Litt Books written in LyX: Troubleshooting Techniques of the Successful Technologist Twenty Eight Tales of Troubleshooting Troubleshooting: Just the Facts
Re: importing spreadsheet rows and columns
Helge Hafting wrote: [EMAIL PROTECTED] wrote: Hi, I'm using LyX 1.5.3 and have a situation where I need to put spreadsheet data into tables in LyX -- several of them actually. I'm able to paste the spreadsheet data into LyX. But so far I haven't figured out a way other than the tedious method of cutting and pasting every cell's contents one-by-one into place in the table. Is there any way I can at least do columns or rows at a time if not a the whole block of columns and rows at once? There is another option too. Gnumeric can export a spreadsheet as latex code for a longtable. There's also a CSV importer now, and almost any spreadsheet will export CSV. rh
Re: importing spreadsheet rows and columns
[EMAIL PROTECTED] wrote: Hi, I'm using LyX 1.5.3 and have a situation where I need to put spreadsheet data into tables in LyX -- several of them actually. I'm able to paste the spreadsheet data into LyX. But so far I haven't figured out a way other than the tedious method of cutting and pasting every cell's contents one-by-one into place in the table. Is there any way I can at least do columns or rows at a time if not a the whole block of columns and rows at once? There is another option too. Gnumeric can export a spreadsheet as latex code for a longtable. This works for spreadsheets made with gnumeric, openoffice and excel. The entire thing can then be imported by importing the latex file. This works fine for text and numbers, I don't know about bar graphs and such. I made an "external inset" that automates this process - there has been no interest so far though. Helge Hafting
Re: importing spreadsheet rows and columns
Quoting Uwe Stöhr <[EMAIL PROTECTED]>: Bug report is filed. New version of csv2lyx.py works as expected. thanks very much! jamie faunt
Re: importing spreadsheet rows and columns
Importing a spreadsheet is something I very often do in LyX but I use quite a different way! Usually my finished product is a pdf file, harder to edit and respecting exactly the formatting I want. Here is what I do in a few words: printing the spreadsheet table in a Postscript file, convert it in encapsulated Postscript (eps or epsi) and insert it as an image in LyX. When exported in pdf, the fonts are embedded, the table is exactly as it was in the spreadsheet and, as it is vectorial, il can fit any space I want in the page. So here is what I do: 1. print the spreadsheet table in a file, be sure to avoid header or footer unless you really need them; 2. convert the printed ps file in eps format in a terminal: ps2epsi the_printed_file.ps 3. the table is cropped precisely in the epsi file with ps2epsi, if you leave a header then the header will be part of the cropped table, don't forget it! 4. In LyX, simply insert the eps file as an image. Don't forget to keep the ratio while giving the wanted width or height of the table. 5. Export the resulting LyX file in pdf and voilà ! Try to be uniform while creating the table, respect the look and feel of the document you want to insert the table into, especially use the same font. This way of doing things can be done also directly by inserting a pdf file in the LyX document (using ERT), but I prefer the eps way as it gives me more control easyly. Raymond -- Édité et transmis avec des logiciels libres sous Linux !
Re: importing spreadsheet rows and columns
Okay -- I've got 1.5.5 installed. When I import a csv file it separates the rows but not the columns. In looking at the converter I see that the python script is csv2lyx. I can't find that script on my system. Would it have been part of my lyx installation? (I installed the Mac Universal binary and I'm using MacTex but I haven't upgraded it since I had LyX 1.5.3.) Also it's putting the imported data in a new LyX file. Just wondering what I need to get this to work. thanks, jamie faunt Quoting [EMAIL PROTECTED]: Fantastic! Will do! Thanks! jamie faunt Quoting Uwe Stöhr <[EMAIL PROTECTED]>: [EMAIL PROTECTED] schrieb: Hi, I'm using LyX 1.5.3 and have a situation where I need to put spreadsheet data into tables in LyX Upgrdade to LyX 1.5.5 and use there the menu File -> Import -> Comma separated values. This requires that you format your spreadsheet to a CSV file with Tabs as column separator (OpenOffice and Excel can do this). regards Uwe
Re: importing spreadsheet rows and columns
[EMAIL PROTECTED] schrieb:
Okay -- I've got 1.5.5 installed. When I import a csv file it separates
the rows but not the columns. In looking at the converter I see that the
python script is csv2lyx. I can't find that script on my system.
I attached the latest version of the script. It does now also recognize the used column separator
automatically. To use it, copy it to LyX's scripts folder and then reconfigure LyX.
Would it have been part of my lyx installation?
It should be part of LyX 1.5.5, as this doesn't seem to be the case, please
open a bug report at
bugzilla.lyx.org
so that our Mac maintainer can fix this.
(I installed the Mac Universal
binary and I'm using MacTex but I haven't upgraded it since I had LyX
1.5.3.)
The script is part of LyX and independent of TeX.
Also it's putting the imported data in a new LyX file.
Yes that's intended. You can then copy the table to other LyX files if you like.
regards Uwe
#! /usr/bin/env python
# -*- coding: utf-8 -*-
# file csv2lyx.py
# This file is part of LyX, the document processor.
# Licence details can be found in the file COPYING.
# author Hartmut Haase
# author José Matos
# Full author contact details are available in file CREDITS
# This script reads a csv-table (file name.csv) and converts it into
# a LyX-table for versions 1.5.0 and higher (LyX table format 276).
# It uses Python's csv module for parsing.
# The original csv2lyx was witten by Antonio Gulino <[EMAIL PROTECTED]>
# in Perl for LyX 1.x and modified for LyX table format 276 by the author.
#
import csv, unicodedata
import os, sys
import optparse
def error(message):
sys.stderr.write(message + '\n')
sys.exit(1)
# processing command line options
# delegate this to standard module optparse
args = {}
args["usage"] = "Usage: csv2lyx [options] mycsvfile mytmptable.lyx"
args["description"] = """This script creates a LyX document containing a table
from a comma-separated-value file. The LyX file has format 276
and can be opened with LyX 1.5.0 and newer.
"""
parser = optparse.OptionParser(**args)
parser.set_defaults(excel = 'n', column_sep = 'n', guess_sep = False)
parser.add_option("-e", "--excel",
help="""'character' Excel type, default is 'n'
'e': Excel-generated CSV file
't': Excel-generated TAB-delimited CSV file""")
parser.add_option("-s", "--separator", dest="column_sep",
help= "column separator, default is ','")
parser.add_option("-g", "--guess-sep", action="store_true",
help = "guess the columns separator")
group = optparse.OptionGroup(parser, "Remarks", """If your .csv file contains
special characters (e. g. umlauts,
accented letters, etc.) make sure it is coded in UTF-8 (unicode).
Else LyX will loose some cell contents. If your .csv file was not written
according to the "Common Format and MIME Type for Comma-Separated Values (CSV)
Files" (http://tools.ietf.org/html/rfc4180) there may be unexpected results.""")
parser.add_option_group(group)
(options, args) = parser.parse_args()
# validate input
if len(args) == 1:
infile = args[0]
fout = sys.stdout
elif len(args) ==2:
infile = args[0]
fout = open(args[1], 'w')
else:
parser.print_help()
sys.exit(1)
if not os.path.exists(infile):
error('File "%s" not found.' % infile)
dialects = {'n' : None, 'e' : 'excel', 't' : 'excel-tab'}
if options.excel not in dialects:
parser.print_help()
sys.exit(1)
dialect= dialects[options.excel]
# when no special column separator is given, try to detect it:
if options.column_sep == 'n':
options.guess_sep = 'True'
print options.column_sep, options.guess_sep
if options.guess_sep:
guesser = csv.Sniffer()
input_file = "".join(open(infile,'rb').readlines())
try:
dialect = guesser.sniff(input_file)
print 'found:', dialect.delimiter
reader = csv.reader(open(infile, "rb"), dialect= dialect)
except:
print 'error, using ,'
reader = csv.reader(open(infile, "rb"), dialect= dialect, delimiter=',')
else:
reader = csv.reader(open(infile, "rb"), dialect= dialect,
delimiter=options.column_sep)
# read input
num_cols = 1 # max columns
rows = []
for row in reader:
num_cols = max(num_cols, len(row))
rows.append(row)
num_rows = reader.line_num # number of lines
# create a LyX file
#
# write first part
fout.write("""#csv2lyx created this file
\lyxformat 276
\\begin_document
\\begin_header
\\textclass article
\\inputencoding auto
\\font_roman default
\\font_sans default
\\font_typewriter default
\\font_default_family default
\\font_sc false
\\font_osf false
\\font_sf_scale 100
\\font_tt_scale 100
\\graphics default
\\paperfontsize default
\\papersize default
\\use_geometry false
\\use_amsmath 1
\\use_esint 0
\\cite_engine basic
\\use_bibtopic false
\\paperorientation portrait
\\secnumdepth 3
\\tocdepth 3
\\paragraph_separation indent
Re: importing spreadsheet rows and columns
Fantastic! Will do! Thanks! jamie faunt Quoting Uwe Stöhr <[EMAIL PROTECTED]>: [EMAIL PROTECTED] schrieb: Hi, I'm using LyX 1.5.3 and have a situation where I need to put spreadsheet data into tables in LyX Upgrdade to LyX 1.5.5 and use there the menu File -> Import -> Comma separated values. This requires that you format your spreadsheet to a CSV file with Tabs as column separator (OpenOffice and Excel can do this). regards Uwe
Re: importing spreadsheet rows and columns
[EMAIL PROTECTED] schrieb: Hi, I'm using LyX 1.5.3 and have a situation where I need to put spreadsheet data into tables in LyX Upgrdade to LyX 1.5.5 and use there the menu File -> Import -> Comma separated values. This requires that you format your spreadsheet to a CSV file with Tabs as column separator (OpenOffice and Excel can do this). regards Uwe
importing spreadsheet rows and columns
Hi, I'm using LyX 1.5.3 and have a situation where I need to put spreadsheet data into tables in LyX -- several of them actually. I'm able to paste the spreadsheet data into LyX. But so far I haven't figured out a way other than the tedious method of cutting and pasting every cell's contents one-by-one into place in the table. Is there any way I can at least do columns or rows at a time if not a the whole block of columns and rows at once? thanks, jamie faunt
