LinuxQuestions.org
Visit Jeremy's Blog.
Go Back   LinuxQuestions.org > Forums > Non-*NIX Forums > General
User Name
Password
General This forum is for non-technical general discussion which can include both Linux and non-Linux topics. Have fun!

Notices


Reply
  Search this Thread
Old 03-18-2019, 04:22 PM   #1
mjolnir
Member
 
Registered: Apr 2003
Posts: 760

Rep: Reputation: 84
Import .csv file into Windows LibreOffice Calc


I created a small .pdf about 10 years ago to keep track of some expensive tools on a job site. I pulled it out of the dust bin this morning to re-purpose for a family member to keep a small pantry inventory. It consists of 20 pairs of editable text boxes that dump all their data to a single text box at the bottom of the form. A snippet of the output, saved to a file, looks as such:

0,70770000326;0,16300151144;0,25500003856;0,024126011344;0,21100025113


The desired output after the file is imported into Calc is:

Cell A1=0 Cell B1=70770000326
Cell A2=0 Cell B2=16300151144
etc.


She has Calc on her Windows computer and I thought it would be a simple matter to open the file and use the comma and semi-colon to format the desired output but I seem to have forgotten how. I can open the file in Vim and run:
Code:
s/;/\r/g
save the file and the import goes smoothly but I need for her to do it on her machine. Ideas appreciated.

Last edited by mjolnir; 03-19-2019 at 07:52 AM. Reason: Edited for clarity
 
Old 03-18-2019, 04:54 PM   #2
scasey
Senior Member
 
Registered: Feb 2013
Location: Tucson, AZ, USA
Distribution: CentOS 7.6
Posts: 2,760

Rep: Reputation: 950Reputation: 950Reputation: 950Reputation: 950Reputation: 950Reputation: 950Reputation: 950Reputation: 950
open the data in a spreadsheet
Data -> Text to Columns -- select the semicolon delimiter

If it's always as short as the example, she can drag and drop the cells from the row to the column
Or use cut/paste

I think the function to do it automagically is a pivot table, but I'm not figuring out/remembering how to do that at the moment. I'll keep playing with it.

Nope: It's the TRANSPOSE function. Copies a row of columns to a column of rows. Help is your friend.

My main point is that what you want to do can be done within Calc with no pre-processing.

Last edited by scasey; 03-18-2019 at 05:07 PM.
 
Old 03-18-2019, 06:22 PM   #3
mjolnir
Member
 
Registered: Apr 2003
Posts: 760

Original Poster
Rep: Reputation: 84
Thanks for the quick reply. I gave it a try or two and it's trying to work but not quite. My old eyes are getting tired on me so I'll take it back up in the morning. Thanks again.
 
Old 03-18-2019, 08:36 PM   #4
syg00
LQ Veteran
 
Registered: Aug 2003
Location: Australia
Distribution: Lots ...
Posts: 17,691

Rep: Reputation: 2796Reputation: 2796Reputation: 2796Reputation: 2796Reputation: 2796Reputation: 2796Reputation: 2796Reputation: 2796Reputation: 2796Reputation: 2796Reputation: 2796
Caveat - not a user of office unless I absolutely have to.

Use of carriage return is a little odd (to me) - does that imply all that data is in a single cell in calc ?. Rather than each being in a new cell ?.
If you can do the substitution in vim, you can do it in sed - in a script for your friend if you have to. And with Windows Subsystem for Linux (WSL) you can run bash scripts on Windows files. That's basically all I've used WSL for in fact.
Free download from the store these days apparently.
You could probably do likewise in PowerShell too.

Or you could mess around in calc - maybe make up a macro or somesuch.
 
Old 03-19-2019, 07:42 AM   #5
mjolnir
Member
 
Registered: Apr 2003
Posts: 760

Original Poster
Rep: Reputation: 84
Quote:
Originally Posted by scasey View Post
If it's always as short as the example, she can drag and drop the cells from the row to the column
Or use cut/paste
The snippet I posted above is only 5 'pairs' or 10 cells of data from one page of a pdf. There are 20 items or a potential 40 cells of data on this single page pdf so if I add extra pages to the pdf, cut/paste would get old pretty quickly.
It's no problem of course for me to do a one off for the family member using vim or a sed one-liner as @syg00 suggested but I belong to some other groups, primarily Windows users, that may find some use for the method I use if I extend it's functionality.
'Transpose' orders the data into individual vertical cells:
A1=0
A2=70770000326
when I want:
A1=0 B1=70770000326
A2=0 B2=16300151144
etc.

Quote:
Originally Posted by syg00 View Post
If you can do the substitution in vim, you can do it in sed - in a script for your friend if you have to. And with Windows Subsystem for Linux (WSL) you can run bash scripts on Windows files. That's basically all I've used WSL for in fact.
Agreed. I in fact used Vim running in a Bash shell on WSL to format the original file. Works great. I haven't fired up my Ubuntu vm in several weeks. Thanks for the reply.
 
Old 03-19-2019, 08:57 AM   #6
scasey
Senior Member
 
Registered: Feb 2013
Location: Tucson, AZ, USA
Distribution: CentOS 7.6
Posts: 2,760

Rep: Reputation: 950Reputation: 950Reputation: 950Reputation: 950Reputation: 950Reputation: 950Reputation: 950Reputation: 950
Quote:
Originally Posted by mjolnir View Post
The snippet I posted above is only 5 'pairs' or 10 cells of data from one page of a pdf. There are 20 items or a potential 40 cells of data on this single page pdf so if I add extra pages to the pdf, cut/paste would get old pretty quickly.
It's no problem of course for me to do a one off for the family member using vim or a sed one-liner as @syg00 suggested but I belong to some other groups, primarily Windows users, that may find some use for the method I use if I extend it's functionality.
'Transpose' orders the data into individual vertical cells:
A1=0
A2=70770000326
when I want:
A1=0 B1=70770000326
A2=0 B2=16300151144
etc.
I'm pretty sure you've edited your OP, as I had exactly what you originally posted you wanted which was:
a1=0,70770000326
a2=0,16300151144
...but I didn't save that and can't prove it.

I'll leave you to figure it out. I'm confident it can be done within Calc without having to pre-process the data, which, yes, would be challenging in Windows.
 
Old 03-19-2019, 09:24 AM   #7
mjolnir
Member
 
Registered: Apr 2003
Posts: 760

Original Poster
Rep: Reputation: 84
Quote:
Originally Posted by scasey View Post
I'm pretty sure you've edited your OP, as I had exactly what you originally posted you wanted which was:
a1=0,70770000326
a2=0,16300151144
...but I didn't save that and can't prove it.

...
Why would you have to prove something? I edited it this morning and gave a reason as 'clarity' so why the hostility?
 
Old 03-19-2019, 09:33 AM   #8
scasey
Senior Member
 
Registered: Feb 2013
Location: Tucson, AZ, USA
Distribution: CentOS 7.6
Posts: 2,760

Rep: Reputation: 950Reputation: 950Reputation: 950Reputation: 950Reputation: 950Reputation: 950Reputation: 950Reputation: 950
Quote:
Originally Posted by mjolnir View Post
Why would you have to prove something? I edited it this morning and gave a reason as 'clarity' so why the hostility?
No hostility intended. Sorry.
I had figured out how to do what you asked for originally, then you changed what you wanted, which, of course, you have every right to do. I'm sure it can still be done within Calc instead of preprocessing the data, but I'll leave that to an exercise for you.
 
Old 03-19-2019, 09:43 AM   #9
mjolnir
Member
 
Registered: Apr 2003
Posts: 760

Original Poster
Rep: Reputation: 84
Quote:
Originally Posted by scasey View Post
No hostility intended. Sorry. ...
No problem, have a good day.
 
Old 03-19-2019, 10:45 AM   #10
AnanthaP
Member
 
Registered: Jul 2004
Location: Chennai, India
Distribution: UBUNTU 5.10 since Jul-18,2006 on Intel 820 DC
Posts: 885

Rep: Reputation: 208Reputation: 208Reputation: 208
With importing many pairs into the spreadsheet as a single row you would br out of space after 128 pairs (256 columns).

So I suggest that you
Ask for the pdf file,
Convert the data into rows thus:
Quote:
0,70770000326
0,16300151144
0,25500003856
0,024126011344
0,21100025113
Send it back to her and
Get her to do a simple import with comma delimiter.

OK
 
Old 03-19-2019, 12:31 PM   #11
mjolnir
Member
 
Registered: Apr 2003
Posts: 760

Original Poster
Rep: Reputation: 84
Quote:
Originally Posted by AnanthaP View Post
With importing many pairs into the spreadsheet as a single row you would br out of space after 128 pairs (256 columns).

So I suggest that you
Ask for the pdf file,
Convert the data into rows thus:

Send it back to her and
Get her to do a simple import with comma delimiter.

OK
Thanks for the reply. As, per the post #5, I can do that now but the family member is reasonably computer literate with Windows and very independent so she, and I, had rather she could do the transition on her on. If I don't find a solution inherent to 'Calc' I'll probably try to modify the javascript contained in the pdf itself.
Yesterday was the first time I've opened up a javascript debugger in ten years so I've got some research to do.

Last edited by mjolnir; 03-19-2019 at 12:33 PM.
 
Old 03-20-2019, 12:44 AM   #12
John VV
LQ Muse
 
Registered: Aug 2005
Location: A2 area Mi.
Posts: 17,426

Rep: Reputation: 2590Reputation: 2590Reputation: 2590Reputation: 2590Reputation: 2590Reputation: 2590Reputation: 2590Reputation: 2590Reputation: 2590Reputation: 2590Reputation: 2590
as i recall it is the same as it was on MS Excel

open a new blank sheet


then go to
sheet / insert sheet from file
-- use the GUI to navigate to the CSV file

and select the proper delimiter ( , "blank space" or tab )
 
Old 03-20-2019, 04:03 AM   #13
mjolnir
Member
 
Registered: Apr 2003
Posts: 760

Original Poster
Rep: Reputation: 84
Quote:
Originally Posted by John VV View Post
as i recall it is the same as it was on MS Excel

open a new blank sheet


then go to
sheet / insert sheet from file
-- use the GUI to navigate to the CSV file

and select the proper delimiter ( , "blank space" or tab )
Correct, the procedure is the same. Thanks for the reply. My problem is that I can't get Calc to use both delimiters, comma and semi-colon to give me the desired result:
Quote:
Originally Posted by mjolnir View Post
...A snippet of the output, saved to a file, looks as such:

0,70770000326;0,16300151144;0,25500003856;0,024126011344;0,21100025113


The desired output after the file is imported into Calc is:

Cell A1=0 Cell B1=70770000326
Cell A2=0 Cell B2=16300151144
etc. ...
 
Old 03-20-2019, 07:42 AM   #14
mjolnir
Member
 
Registered: Apr 2003
Posts: 760

Original Poster
Rep: Reputation: 84
Ok, I did find a work around that doesn't involve 'sed' or vim for my relative's windows computer. I opened up the pdf and changed the javascript so that the captured text string is now:
0,70770000326`r`n0,16300151144`r`n0,25500003856`r`n0,024126011344 `r`n0,21100025113
instead of:
0,70770000326;0,16300151144;0,25500003856;0,024126011344;0,21100025113
Following @syg00's suggestion concerning Powershell I found that I could plug the string into:
Code:
#Rem Copy/paste data from .pdf textbox into quotation marks: $text="", enter.
#Rem Enter $text | Set-Content 'file.txt' with file name of choice.   
$text=""
$text | Set-Content 'file.txt'
and the resultant 'file' imports into Calc with the desired output.
Still requires an extra step but one my relative can handle on her windows comp.
Thanks to all who participated.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] converting .ods to .csv file in LibreOffice Calc question Gregg Bell Linux - Newbie 14 04-22-2017 11:59 PM
How to print lines in csv file if 1 csv column field = "text". There are 10 column (;) in csv file nexuslinux Linux - Newbie 9 04-22-2016 11:35 PM
PHP script to import CSV file into MySQL database JoseCuervo Linux - Server 3 08-01-2014 06:18 AM
[SOLVED] A challenging script - Replace field of CSV file based on another CSV file arbex5 Programming 11 06-12-2013 06:56 AM
data import into openoffice calc general4172 Linux - Software 0 09-24-2003 12:45 PM

LinuxQuestions.org > Forums > Non-*NIX Forums > General

All times are GMT -5. The time now is 05:02 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration