[Solved] Import CSV into existing Calc sheet
[Solved] Import CSV into existing Calc sheet
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
Reason: Tagged ✓ [Solved] Multiple solutions were provided -- MrProgrammer, forum moderator
OpenOffice 4.1.1 / Windows 7
Re: Importing CSV into existing calc sheet
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 56
- Joined: Thu Apr 22, 2010 5:03 am
Re: Importing CSV into existing calc sheet
______________________________________________
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
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
Re: Importing CSV into existing calc sheet
Bullshit.mauriciobaeza wrote:You can try with EasyDev: http://easydev.readthedocs.org/en/lates ... import-csv
Best regards
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 56
- Joined: Thu Apr 22, 2010 5:03 am
Re: Importing CSV into existing calc sheet
I don't understand... What's your problem?Villeroy wrote: Bullshit.
______________________________________________
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
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
Re: Importing CSV into existing calc sheet
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Importing CSV into existing calc sheet
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
-
- Posts: 56
- Joined: Thu Apr 22, 2010 5:03 am
Re: Importing CSV into existing calc sheet
If you take a few minutes to review the project, you could see ...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.
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
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
Re: Importing CSV into existing calc sheet
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Importing CSV into existing calc sheet
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.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.
LibreOffice 3.5.4 AOo-3.4 on Win 7 Ultimate
Re: Importing CSV into existing calc sheet
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.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Importing CSV into existing calc sheet
Got "easydev" installed and created this Basic snippet:
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.
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
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Importing CSV into existing calc sheet
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
-
- Volunteer
- Posts: 1160
- Joined: Mon Oct 08, 2007 1:26 am
- Location: France, Paris area
Re: Importing CSV into existing calc sheet
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:
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?
- Open the csv file in its own Calc.
- Select the table you have obtained. Copy (Ctrl-C)
- Open the final, formatted, document
- Put the cursor at the upper-left corner of the position you want in the sheet.
- Menu Edit > Paste Special...
- check these : Text, Numbers, Date&Time.
- OK.
- Close the csv Calc. You don't need it anymore.
Bernard
OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
OpenOffice.org 1.1.5 / Apache OpenOffice 4.1.1 / LibreOffice 5.0.5
MS-Windows 7 Home SP1
Re: Importing CSV into existing calc sheet
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.B Marcelly wrote:Much quicker to do than to explain.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- Jurassic Pork
- Posts: 24
- Joined: Wed Oct 25, 2017 7:55 am
- Location: France
Re: Importing CSV into existing calc sheet
Hello,
Villeroy, your code is wrong.
Here is a code using easydev to import csv file in a calc sheet ( sheet CSV)
fruits.csv :
old post but when you say that something is a bullshit without knowing how to use it, it is not fair.Villeroy wrote: ↑Sun Feb 07, 2016 4:50 am Got "easydev" installed and created this Basic snippet:The print statement reports that the given path exists but the data array is empty. The file has 8 lines of semicolon separated fields.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
We have enough sloppy extensions.
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
Friendly, J.P"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"
OpenOffice 4.1.14 , LibreOffice 7.6.2.1 on Windows 11/ LibreOffice 7.3.7 on Lubuntu 22.04
Re: Importing CSV into existing calc sheet
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- Jurassic Pork
- Posts: 24
- Joined: Wed Oct 25, 2017 7:55 am
- Location: France
Re: Importing CSV into existing calc sheet
Hello,
You can also convert the data of the columns with string numbers to number with conversion functions.
Example with this in the fruis.csv :
Friendly, J.P
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 :
Code :"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: 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
OpenOffice 4.1.14 , LibreOffice 7.6.2.1 on Windows 11/ LibreOffice 7.3.7 on Lubuntu 22.04
Re: Import CSV into existing Calc sheet
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- Jurassic Pork
- Posts: 24
- Joined: Wed Oct 25, 2017 7:55 am
- Location: France
Re: Import CSV into existing Calc sheet
May be but then show me a way as easy as Easydev to import csv in calc with a macro
OpenOffice 4.1.14 , LibreOffice 7.6.2.1 on Windows 11/ LibreOffice 7.3.7 on Lubuntu 22.04
Re: Import CSV into existing Calc sheet
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:
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.
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
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 288 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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- Jurassic Pork
- Posts: 24
- Joined: Wed Oct 25, 2017 7:55 am
- Location: France
Re: Import CSV into existing Calc sheet
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
Re: Import CSV into existing Calc sheet
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
- Jurassic Pork
- Posts: 24
- Joined: Wed Oct 25, 2017 7:55 am
- Location: France
Re: Import CSV into existing Calc sheet
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