Importing CSV into existing calc sheet

Discuss the spreadsheet application

Importing CSV into existing calc sheet

Postby Kay67 » Fri Feb 05, 2016 8:57 pm

I have a calc sheet that has specific headings and certain pre-populated cells. I have a csv file that needs to be imported into this sheet however it needs to be mapped to specific cells. Is there a simple way to do this?
OpenOffice 4.1.1 / Windows 7
Kay67
 
Posts: 1
Joined: Fri Feb 05, 2016 8:38 pm

Re: Importing CSV into existing calc sheet

Postby Villeroy » Fri Feb 05, 2016 9:28 pm

[url=[Example]Loading CSV into preformatted spreadsheets ][Example] Loading CSV into preformatted spreadsheets[/url]
File>New>Database...
[X] Connect to existing db
Type: Text
Specify the directory of your csv file and the import parameters.
[X] Register this database
Save the database, close the database, forget the database.
Nothing is converted, copied nor imported. Your data are still in the text file.
######################################################################################
In Calc hit F4, double-click your database, the [Tables] icon and then drag the icon which represents your csv file into the sheet.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26853
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing CSV into existing calc sheet

Postby mauriciobaeza » Fri Feb 05, 2016 10:30 pm

You can try with EasyDev: http://easydev.readthedocs.org/en/lates ... import-csv

Best regards
______________________________________________
Everything not given is lost
AOO 4.1 / LibO 4.3 on ArchLinux with Gnome3
Please, I do not answer private questions, you use the forum
mauriciobaeza
 
Posts: 56
Joined: Thu Apr 22, 2010 5:03 am

Re: Importing CSV into existing calc sheet

Postby Villeroy » Fri Feb 05, 2016 10:34 pm

mauriciobaeza wrote:You can try with EasyDev: http://easydev.readthedocs.org/en/lates ... import-csv

Best regards

Bullshit.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26853
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing CSV into existing calc sheet

Postby mauriciobaeza » Fri Feb 05, 2016 11:58 pm

Villeroy wrote:Bullshit.

I don't understand... What's your problem?
______________________________________________
Everything not given is lost
AOO 4.1 / LibO 4.3 on ArchLinux with Gnome3
Please, I do not answer private questions, you use the forum
mauriciobaeza
 
Posts: 56
Joined: Thu Apr 22, 2010 5:03 am

Re: Importing CSV into existing calc sheet

Postby Villeroy » Sat Feb 06, 2016 12:27 pm

Instead of providing any practicable solution you deliver spam for a questionable developers tool. This is the Calc forum. We have to assume that Kay67 is not interested in writing any program.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26853
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing CSV into existing calc sheet

Postby acknak » Sat Feb 06, 2016 3:05 pm

Please remember that we ask everyone to be respectful here. The post above is disrespectful, even if it's not intended as a personal attack.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Importing CSV into existing calc sheet

Postby mauriciobaeza » Sat Feb 06, 2016 3:56 pm

Villeroy wrote:Instead of providing any practicable solution you deliver spam for a questionable developers tool. This is the Calc forum. We have to assume that Kay67 is not interested in writing any program.

If you take a few minutes to review the project, you could see ...

1. EasyDev is free software Apache OpenOffice and LibreOffice
2. It is to help developers in Basic and other macro languages.
3. It is licensed GPL3
4. All source code is available
5. And easily solves many problems as the one proposed in this topic

If you do not like the project, please, your words are better than your silence
______________________________________________
Everything not given is lost
AOO 4.1 / LibO 4.3 on ArchLinux with Gnome3
Please, I do not answer private questions, you use the forum
mauriciobaeza
 
Posts: 56
Joined: Thu Apr 22, 2010 5:03 am

Re: Importing CSV into existing calc sheet

Postby FJCC » Sat Feb 06, 2016 4:18 pm

Villeroy - You have misinterpreted Mauricio's intent and used language that just shouldn't appear on the forum. Please keep the discussion civil. What's the benefit of the response you made over "I don't think the OP is interested in writing a program"?
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7190
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Importing CSV into existing calc sheet

Postby mriosv » Sun Feb 07, 2016 1:30 am

Villeroy wrote:Instead of providing any practicable solution you deliver spam for a questionable developers tool. This is the Calc forum. We have to assume that Kay67 is not interested in writing any program.


Hopefully someday we can see you thinking a bit, before writing such things. IMMHO the best thing you can do is learn from Mauricio, big and valuable to the community both as a person like IT professional.
LibreOffice 3.5.4 AOo-3.4 on Win 7 Ultimate
mriosv
Volunteer
 
Posts: 651
Joined: Mon Mar 09, 2009 1:12 am
Location: Galiza (España)

Re: Importing CSV into existing calc sheet

Postby Villeroy » Sun Feb 07, 2016 3:57 am

Even if you get the latest "easydev" installed (I can't), the given advice on http://easydev.readthedocs.org/en/lates ... import-csv is bullshit.
The underlying Python library imports dumb text instead of spreadsheet values.
range.setDataArray(data) fails unless you selected the correct dimensions of the csv before importing it.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26853
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing CSV into existing calc sheet

Postby Villeroy » Sun Feb 07, 2016 4:50 am

Got "easydev" installed and created this Basic snippet:
Code: Select all   Expand viewCollapse view
Sub Main
util = createUnoService("org.universolibre.EasyDev")
Dim Options(0) as new com.sun.star.beans.PropertyValue
options(0).Name = "delimiter"
options(0).Value = ";"

path = "/tmp/test/07022016.csv"
data = util.importCSV(path, options)

print fileexists(path), ubound(data)

' range = ThisComponent.Sheets(0).getCellRangeByPosition(0, uBound(data(0)), 0, uBound(data))
' range.setDataArray(data)
End Sub

The print statement reports that the given path exists but the data array is empty. The file has 8 lines of semicolon separated fields.
We have enough sloppy extensions.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26853
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing CSV into existing calc sheet

Postby FJCC » Sun Feb 07, 2016 5:51 am

Flaws in EasyDev in no way excuse your previous behavior. Accept that you acted badly and try to do better in the future.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7190
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Importing CSV into existing calc sheet

Postby B Marcelly » Sun Feb 07, 2016 8:57 am

Hi,
This lengthy discussion between contributers is out of place in this thread. If you want to continue your exchanges, do it in another thread (Site Feedback for example).

Returning to the original question:
Kay67 wrote:I have a calc sheet that has specific headings and certain pre-populated cells. I have a csv file that needs to be imported into this sheet however it needs to be mapped to specific cells. Is there a simple way to do this?

  1. Open the csv file in its own Calc.
  2. Select the table you have obtained. Copy (Ctrl-C)
  3. Open the final, formatted, document
  4. Put the cursor at the upper-left corner of the position you want in the sheet.
  5. Menu Edit > Paste Special...
    • check these : Text, Numbers, Date&Time.
    • OK.
  6. Close the csv Calc. You don't need it anymore.
Much quicker to do than to explain.
Bernard

OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
B Marcelly
Volunteer
 
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: Importing CSV into existing calc sheet

Postby Villeroy » Sun Feb 07, 2016 6:13 pm

B Marcelly wrote:Much quicker to do than to explain.

This is how I do it myself because I have to deal with ever changing csv formats from various sources. But your description leaves out the import options dialog. The import options for csv are a top issue on this forum.

I think the database import is the best choice as long as your csv flavour does not change:
1) If you have to do this very often, you can repeat the same thing within seconds. The program stores all the gory import details in a config file (the "database document").
1a) Just put the new file in the same directory and if it replaces the previous one with the same file name, all you need to do is menu:Data>Refresh
1b) If the new file has another name, hit F4 and drag the new table icon onto the existing import range to replace the old data with the new data.
2) Database data are raw data. They do not overwrite any sheet formatting. But there is still an option to do so in the db-range options.
3) Database data do not even overwrite existing values outside their own area. Database import inserts new cells as the record set grows and removes cells as the record set shrinks.
4) Database data have clear dimensions. There is always one row of column labels, a distinct amount of columns (fields) and rows (records). When refreshing database data, all formula expressions are adjusted automatically. Your statistics, charts, range names, conditional formattings, validation formulas and any other formula references refer to the actual size of the database range.
5) Adjacent formula cells with row calculations (=C1+D1*Tax_Rate) expland and shrink with the record set. No need to copy down new formulas or to delete superfluous formulas.
6) When importing a large record set, you can choose to not store the imported data in the spreadsheet.
7) An fully automatic refresh can be implemented by a one-line macro: ThisComponent.DatabaseRanges("Import1").refresh() [bound to the sheet activation event]
8) Database queries offer a whole basket of most useful import options. A query can change the column labels, the order of rows, the order of columns, perform simple calculations, omit columns, filter rows by aribitrary complex criteria (way easier than a sheet) and you can even define parameter queries which prompt for filter criteria.

How many lines of macro code would it take to implement only a subset of this built-in functionality?

Drawback: The csv driver for Base is underdeveloped. If the csv data do not correspond with the application locale you may have to work around dumb text data.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26853
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 51 guests