Importing CSV into existing calc sheet

[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?

[Villeroy]

[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.

[mauriciobaeza]

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

Best regards

[Villeroy]

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

Best regards

Bullshit.

[mauriciobaeza]

Bullshit.

I don't understand... What's your problem?

[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.

[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.

[mauriciobaeza]

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

[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"?

[mriosv]

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.

[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.

[Villeroy]

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.

[FJCC]

Flaws in EasyDev in no way excuse your previous behavior. Accept that you acted badly and try to do better in the future.

[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:

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.

[Villeroy]

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.