[Solved] Import CSV into existing Calc sheet

Discuss the spreadsheet application
Post Reply
Kay67
Posts: 1
Joined: Fri Feb 05, 2016 8:38 pm

[Solved] Import CSV into existing Calc sheet

Post by Kay67 »

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?
Last edited by MrProgrammer on Sat Jan 20, 2024 5:30 pm, edited 1 time in total.
Reason: Tagged ✓ [Solved] Multiple solutions were provided -- MrProgrammer, forum moderator
OpenOffice 4.1.1 / Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing CSV into existing calc sheet

Post by Villeroy »

Loading CSV into preformatted spreadsheets ][Example] Loading CSV into preformatted spreadsheets
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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mauriciobaeza
Posts: 56
Joined: Thu Apr 22, 2010 5:03 am

Re: Importing CSV into existing calc sheet

Post by mauriciobaeza »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing CSV into existing calc sheet

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
mauriciobaeza
Posts: 56
Joined: Thu Apr 22, 2010 5:03 am

Re: Importing CSV into existing calc sheet

Post by mauriciobaeza »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing CSV into existing calc sheet

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
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

Post by acknak »

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
mauriciobaeza
Posts: 56
Joined: Thu Apr 22, 2010 5:03 am

Re: Importing CSV into existing calc sheet

Post by mauriciobaeza »

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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Importing CSV into existing calc sheet

Post by FJCC »

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"?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
mriosv
Volunteer
Posts: 651
Joined: Mon Mar 09, 2009 1:12 am
Location: Galiza (España)

Re: Importing CSV into existing calc sheet

Post by mriosv »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing CSV into existing calc sheet

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing CSV into existing calc sheet

Post by Villeroy »

Got "easydev" installed and created this Basic snippet:

Code: Select all

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Importing CSV into existing calc sheet

Post by FJCC »

Flaws in EasyDev in no way excuse your previous behavior. Accept that you acted badly and try to do better in the future.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
B Marcelly
Volunteer
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: Importing CSV into existing calc sheet

Post by B Marcelly »

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
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing CSV into existing calc sheet

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Jurassic Pork
Posts: 23
Joined: Wed Oct 25, 2017 7:55 am
Location: France

Re: Importing CSV into existing calc sheet

Post by Jurassic Pork »

Hello,
Villeroy wrote: Sun Feb 07, 2016 4:50 am Got "easydev" installed and created this Basic snippet:

Code: Select all

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.
old post but when you say that something is a bullshit without knowing how to use it, it is not fair.
Villeroy, your code is wrong.
Here is a code using easydev to import csv file in a calc sheet ( sheet CSV)

Code: Select all

Sub ImportCsv()
Dim doc As Object, address As Object, cell As Object
Dim util As Object, path As String, options, data As Object
util = createUnoService("org.universolibre.EasyDev")
address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")
address.Doc = ThisComponent
address.Sheet = "CSV"
address.Name = "B2"
cell = util.getCell(address)
path = "d:/tmp/fruits.csv"
options = Array(Array("delimiter",","))
data = util.importCSV(path, options)
util.setData(cell,data)
End Sub
fruits.csv :
"1000","Apple","4","133"
"1001","Apricot","5","175"
"1002","Avocado","5","182"
"1003","Banana","5","187"
"1004","Bilberry","5","160"
"1005","Blackberry","4","178"
"1006","Blackcurrant","5","102"
"1007","Blueberry","6","156"
"1008","Currant","5","194"
"1009","Cherry","5","182"
"1010","Cherimoya","3","169"
"1011","Clementine","3","165"
CsvImport.gif
CsvImport.gif (49.82 KiB) Viewed 2206 times
Friendly, J.P
OpenOffice 4.1.14 , LibreOffice 7.6.2.1 on Windows 11/ LibreOffice 7.3.7 on Lubuntu 22.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Importing CSV into existing calc sheet

Post by Villeroy »

And all those values are text. Just try to sum them up.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Jurassic Pork
Posts: 23
Joined: Wed Oct 25, 2017 7:55 am
Location: France

Re: Importing CSV into existing calc sheet

Post by Jurassic Pork »

Hello,
Villeroy wrote: Sun Dec 31, 2023 4:30 pm And all those values are text. Just try to sum them up.
If your csv files have quoted string and if the numbers are not quoted your can use the option quoting = CSV.QUOTED_NONNUMERIC (2) to import CSV. Number data will be Number in calc.
You can also convert the data of the columns with string numbers to number with conversion functions.
Example with this in the fruis.csv :
"1000","Apple","4",133
"1001","Apricot","5",175
"1002","Avocado","5",182
"1003","Banana","5",187
"1004","Bilberry","5",160
"1005","Blackberry","4",178
"1006","Blackcurrant","5",102
"1007","Blueberry","6",156
"1008","Currant","5",194
"1009","Cherry","5",182
"1010","Cherimoya","3",169
"1011","Clementine","3",165
Code :

Code: Select all

Sub CsvImport()
Dim doc As Object, address As Object, cell As Object
Dim util As Object, path As String, options, data As Variant, x As integer
util = createUnoService("org.universolibre.EasyDev")
address = createUnoStruct("org.universolibre.EasyDev.CellRangeAddress")
address.Doc = ThisComponent
address.Sheet = "CSV"
address.Name = "B2"
cell = util.getCell(address)
path = "d:/tmp/fruits.csv"
options = Array(Array("delimiter",","),Array("quoting",2))
data = util.importCSV(path, options)
for x = 0 to  Ubound(data)
   data(x)(0) =  Cint(data(x)(0)) ' convert data of column 0 string -> int
next x
util.setData(cell,data)
End Sub
CsvNumbers.png
CsvNumbers.png (26.21 KiB) Viewed 2033 times
Friendly, J.P
OpenOffice 4.1.14 , LibreOffice 7.6.2.1 on Windows 11/ LibreOffice 7.3.7 on Lubuntu 22.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Import CSV into existing Calc sheet

Post by Villeroy »

As a matter of fact, EasyDev imports wrong data. You need to add a post-processing routine.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Jurassic Pork
Posts: 23
Joined: Wed Oct 25, 2017 7:55 am
Location: France

Re: Import CSV into existing Calc sheet

Post by Jurassic Pork »

Villeroy wrote: Mon Jan 01, 2024 1:08 pm As a matter of fact, EasyDev imports wrong data. You need to add a post-processing routine.
May be but then show me a way as easy as Easydev to import csv in calc with a macro :mrgreen:
OpenOffice 4.1.14 , LibreOffice 7.6.2.1 on Windows 11/ LibreOffice 7.3.7 on Lubuntu 22.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Import CSV into existing Calc sheet

Post by Villeroy »

EasyDev is insufficient because the coders never used any spreadsheet in a professional manner (as far as sheets are professional anyway).

The code in library CSV_Dump has following advantages:
- Not overwriting any existing data. Inserts cells at active cell before dumping data, expanding the formatting from the row above down.
- Insertion of data updates cell references. =SUM(A1:A50) becomes SUM(A1:A75) after importing 25 rows.
- Configurable as easily as possible: Works with any valid csv file. Can skip header rows.
Open one of your csv files with all necessary import options, run macro showFilterOptions and copy the file's filter options from the shown input box.
- Comes with a simplified file picker macro.

A most simple version of your personal import macro looks like this then:

Code: Select all

Sub dumpMyCsv
sOptions = "<your specific filter options>"
sURL = convertToURL("C:\path\blah.csv")
insert_csv_at_active_cell sURL, sOptions
End Sub
Installation:
menu:Tools>Macros>Organize... button [Organizer...], tab "Libraries", Cotainer "My Macros", [Import...], select my Document and the library therein.

Module1 contains the macro which gets the correct import options for your specific files and 2 samples.
The first sample imports your fully quoted, comma separated data in with UTF8 encoding using the US English locale.
The second sample imports a file of mine with ANSI encoding, Swiss language settings, skipping the first row of column headers.
Attachments
csv_Dump.ods
(23.22 KiB) Downloaded 58 times
Last edited by Villeroy on Mon Jan 01, 2024 5:54 pm, edited 1 time in total.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Jurassic Pork
Posts: 23
Joined: Wed Oct 25, 2017 7:55 am
Location: France

Re: Import CSV into existing Calc sheet

Post by Jurassic Pork »

Villeroy in your file in the library Csv_Dump i only see the function getActiveCell (_helpers module) ???
OpenOffice 4.1.14 , LibreOffice 7.6.2.1 on Windows 11/ LibreOffice 7.3.7 on Lubuntu 22.04
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Import CSV into existing Calc sheet

Post by Villeroy »

Sorry, wrong document. I replaced the file with the actual version. Please, test carefully. I didn't.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Jurassic Pork
Posts: 23
Joined: Wed Oct 25, 2017 7:55 am
Location: France

Re: Import CSV into existing Calc sheet

Post by Jurassic Pork »

ok, it seems to be good now, thanks
OpenOffice 4.1.14 , LibreOffice 7.6.2.1 on Windows 11/ LibreOffice 7.3.7 on Lubuntu 22.04
Post Reply