Importing data from tsv file into existing or template without losing spreadsheet formatting?

English
#1

i’ve managed so far to get my tsv files set up such that they import well into calc. having gotten that to work, i took one such import and set about customizing it, mostly for appearance but also things like formulas, hidden columns, colors. i saved this customized calc document as a template. i find that if i open one of my tsv files as a calc document, then, say, copy a column and paste it into the template, i lose all of my formatting. things like word wrap and border buffers seem to get overwritten by the incoming data.

is there a guide someplace that shows how to import things like this, columns or whole documents, into an existing template without losing my customization? everything i’ve seen so far seems to assume the template will be for hand input rather than bulk importing.

thanks,
babag

#3

1.: Use Cell Styles instead of the direct (manual) formatting. Then you will able to restore the overwritten formatting properties easily.
2.: Use the “Paste special - Unformatted text” instead of the simple “Paste”. The Paste will overwite the format properties with the properties of the data source. The Paste Special will not.

1 Like
#4

interesting and thanks for this. if i’m getting this right, and please correct me if i’m not, you’re suggesting using menu choices and such for achieving my formatting aims as opposed to something manual like dragging row height and width to get things to look as i want. that’s pretty much what i did to get my document formatted.

i did find something interesting in exploring your suggestions. i copied a column from an imported tsv file and tried pasting it into its corresponding column in my template. i used ‘paste special - unformatted text’ as suggested. when i did so, i lost my formatting. it was most obvious in the wrapping of text in the cells. there was none but there should have been.

what’s interesting is that i undid the paste with ‘ctl + z’ and found that, if i immediately hit ‘ctl + shift + z’ to undo the undo, so to speak, the correct formatting came through this time. odd. any idea as to what that’s all about?

thanks,
babag

#5

Not. I suggested you to use the STYLES. The column width and column height are not a part of the Cell Styles.

#6

Please upload your ODF type sample files here. (source and target file)

#7

https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=77069

This macro driven “solution” assumes that you are maintaining a database table on a spreadsheet, growing with every import.

  1. You can import all supported spreadsheet formats plus text tables (csv, tsv, txt).
  2. All your customizations remain untouched. The macro requires no more than a set of named configuration cells.
  3. With every new import, new cells are inserted for the new records, expanding all references of your formulas, charts, names, conditional formats and formattings too.
  4. The name of the imported file is written into a separate column, so the import context remains intact.
  5. Files that have been imported already, can be moved to a separate directory, so they won’t be imported twice.

All this is not fit to hold a candle to a database solution. A spreadsheet is not a database, not even close.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.