Save CSV file automatcally and then import it

English
,
#1

Hello there!
I’m trying to save a Sheets data in CSV file, but i wanna do it with a macro. I need the macro to be able to save the info as CSV automatcally in the same location where the document is, i wanna do it without openning any extra tab or screen, just save it. I want to use it as a data base, where the CSV file will always have the same name and will always be overwrited by the new one (that will have new data probably)…
In addition, i will only have the native functions of the program, can not use any outside adds to make it work…

Im very new in this language and do not know a lot.

Hope someone can help me.

Thankyou allready!

I found the cod bellow in a other topic around… the code does exactly what i need, but it requires the user to choose a local to save the file and a name to the file…
I need it to save with a defined name in a defined local…

Sub DDICSVsave
dim aUrl(), s$
dim oDlg as variant
dim fileProps(1) as new com.sun.star.beans.PropertyValue
fileProps(0).Name = "FilterName"
fileProps(0).Value = "Text - txt - csv (StarCalc)"
fileProps(1).Name = "FilterOptions"
fileProps(1).Value = "44,34,76,1,,0,false,true,true,false"
oDlg = createUnoService("com.sun.star.ui.dialogs.FilePicker")
dim listAny(0) as variant
listAny(0) = com.sun.star.ui.dialogs.TemplateDescription.FILESAVE_SIMPLE
oDlg.initialize(listAny()) ' Speichern unter
oDlg.setMultiSelectionMode(false)
oDlg.execute
aUrl = oDlg.getFiles()
thisComponent.storeAsURL(aURL(0), fileProps())
End Sub
#2

Thea Basic language of the LibreOffice IDE is a very simple BASIC dialect. (It is named StarBasic, OpenOffice Basic, LibreOffice Basic).
.
The API is a huge and difficult thing in the Open/
LibreOffice suite. (API: Application Programming Interface.)
There are thousands of functions and procedures, and programming objects in the API. You MUST study them, because you need call these functions and procedures from the Basic macro program.
.
I suggest you to install one of the excellent Object Inspection Tool: XrayTool or MRI. Then you will able to list and examine (in situ) the existing properties and methods of the existing programming objects.

1 Like
#3

You definitively want a database. csv data are database data.

1 Like
#4

I got it to work in the saving. the code is the following:

Sub DDICSVsave()
Dim sDirectory As String
Dim sFilePath As String
Dim sFileName As String
Dim sURL As String
Dim fileProps(1) As New com.sun.star.beans.PropertyValue

' Definir o diretório e o nome do arquivo
sDirectory = "local to store"
sFileName = "base.csv"

' Obter o caminho completo do arquivo
sURL = ConvertToURL(sDirectory & sFileName)

' Configurar as propriedades do arquivo CSV
fileProps(0).Name = "FilterName"
fileProps(0).Value = "Text - txt - csv (StarCalc)"
fileProps(1).Name = "FilterOptions"
fileProps(1).Value = "44,34,76,1,,0,false,true,true,false"

' Salvar o conteúdo da planilha como um arquivo CSV
ThisComponent.storeToURL(sURL, fileProps())

MsgBox "Arquivo CSV salvo com sucesso em " & sURL

End Sub

The thing now is, i am not getting how to import the data from this CSV file directly in my actual doc Sheet; i wanna do it the same way, without setting any screen. the preferences might be in the code, i just want the data in my actual Sheet…

#5

Just use the macro-recorder to record once opening your file, to have parameters right…

PS: Alternatives would be to register the folder with your csv as datasource of type text. And you could also directly open the csv-file with double-click. On my systen it opens in Calc and can be saved back…

#6

Which csv? There are thousands of different flavors of csv. Csv is not standardized.

#7

Clif4 may not be aware of this “decision”, but by using the macro he found he defined the rules for import:

#8

There are several methods for this, for example, insertAtPosition. Since the structure of the csv file does not contain information about the types of fields, additional parameters for import may be required. It would be best if you upload an example of an .ods file with a real data structure (sheet cells) for export.

#9

I have seen people exporting csv files from a database, import the csv into an arithmetic calculator (spreadsheet), export from the calculator back to csv in order to update the same database from that csv. This is pure nonsense since you may access, filter and edit the database directly with Base.

If there is a reason for csv (source database not accessible), and you want to collect data in your own database, you should never ever do that with any spreadsheet program.

As always, everything depends on the gory details. I can create the naked “data collector” with some HSQL in Base within an hour if I only knew the exact structure of the incoming csv. With a proper database, it makes no difference if you collect thousands or many millions rows over the years. Millions of rows are not a big deal. 100,000 rows in a spreadsheet can be a nightmare, no matter how much Basic code you throw at it.

#10

As u might have noticed i am searshing for ways to create a “program” from scratch almost; of course there are many options out there, thought where this “program” will run have almost nothing to work with, and i do not have the knowledge of all the tools available from LibreOffice, what i am doing is going to my objective with what i can by now… i know its very primitive the way i am doing this but i do not find any tutorial that show how to use Libreoffice Base with Libreoffice Calc to make a “interface” with forms and all thats needed, i don’t even know if there is a better option for a program like using this tools. I would prefer using a database for access the data. Do u have any idea where i can get the whole information of how building program like forms? thats all i need :slightly_smiling_face:

#11

menu:File>New>Database…
Connect to existing database
Only you know the details

#12

Documentation is linked at Get Help on th website for LibreOffice. Try the guide for “Base”: English documentation | LibreOffice Documentation - LibreOffice User Guides

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.