[Solved] Automatic Import of CSV into Macro

Discuss the spreadsheet application

[Solved] Automatic Import of CSV into Macro

Postby coreydpeterson » Tue Nov 12, 2019 9:47 pm

Hi,

Longtime lurker, first time poster. Appreciate any help on this. Been scouring the forums for this and can't seems to find a concrete solution. Many of the posts say solved but no complete solution was given. Should be simple to just import a csv file to a sheet.

I need to automatically import a .csv file into a sheet on an existing file. The imported sheet from insertfunction will work but I need it to run automatically with no input from the user so it can be driven by automation software. I have tried using the record function to walk through this but it still pops up user dialogue boxes.

Ideally the macro would just open a given .csv file and then import into either a predefined sheet name or a new one. Then an existing sheet would react to that data and perform some calculations. I see this same question pop up quite a bit in the forum but no answer given.

I have solved this issue using Excel but cannot recreate it in Open office which is needed to run on Linux where Excel is unavailable.

My recorded code is:
dispatcher.executeDispatch(document, ".uno:InsertSheetFromFile","",0 Array())

Thanks for any help
Last edited by coreydpeterson on Wed Nov 13, 2019 7:26 pm, edited 3 times in total.
OpenOffice 4.1.7 on Cent OS6
coreydpeterson
 
Posts: 4
Joined: Tue Nov 12, 2019 9:24 pm

Re: Automatic Import of CSV into Sheet

Postby Villeroy » Tue Nov 12, 2019 11:31 pm

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

Re: Automatic Import of CSV into Sheet

Postby coreydpeterson » Wed Nov 13, 2019 3:11 pm

Hi,

Thank you for your quick reply, I am currently reviewing the code that you sent over. It probably has what I need in it but maybe you know a much simpler way.

Say I have a .csv which is the following:
Input Data, Values
Sheet to export, Sheet1

How do I have the macro read in the .csv and access element 1,1 ("Sheet1")? I have the rest of my macro working which will take that name in and then export that whole sheet as a .csv called output.csv. Thanks for your help in this matter and I must thank you for all your support on this forum. I have found many many invaluable pieces of info from your posts.
OpenOffice 4.1.7 on Cent OS6
coreydpeterson
 
Posts: 4
Joined: Tue Nov 12, 2019 9:24 pm

Re: [Solved] Automatic Import of CSV into Sheet

Postby coreydpeterson » Wed Nov 13, 2019 3:30 pm

Hi I got this working now. Will post code when complete.
OpenOffice 4.1.7 on Cent OS6
coreydpeterson
 
Posts: 4
Joined: Tue Nov 12, 2019 9:24 pm

Re: [Solved] Automatic Import of CSV into Sheet

Postby Villeroy » Wed Nov 13, 2019 3:55 pm

What is so complicated? All data go to the area below named cell "Target".
You specify the path if it differs from the document path. There is even a file picker button.
You specify a file name or a file name pattern.
You specify the import parameters because there are thousands of different csv flavours out in the wild. There is even a helper macro which reads the correct import parameters from your specific csv.
Finally you save the document and click a button.

And this is the clue:
Everything needs to be set up only once. When new files arrived, you only have to open the document and click the button.
In order to prevent duplicate imports, you can also specify the path where already processed files should be moved.
You do not have to fiddle around with my macro code. Just call the Main routine by pushing the button or any other way you like.

For different spreadsheet types or different flavours of csv you just open a new document from template, fill out the specifics and save it as a different import file.
You are totally free to format and re-arrange your document with your own charts, aggregations, formattings, pivot tables as long as you keep the "Target" cell and the other named cells. You can move those cells anywhere across the document without breaking the macro.
The macro takes care that all references are updated properly because it inserts new cells before dumping data, so all your references expand accordingly. Your charts, pivot tables, names, all formulas, conditional formatting, validation formulas and form controls will refer to the updated range after new data have arrived.
The macro keeps record of origins by using a first column with file names.
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.4
User avatar
Villeroy
Volunteer
 
Posts: 28540
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Automatic Import of CSV into Sheet

Postby Villeroy » Wed Nov 13, 2019 5:47 pm

Possibly the easiest solution (if the csv file is always stored in the same place) would be menu:Insert>Sheet From File with "Link" option. If you point to a csv file, the import parameters will be stored. Next time you replace the csv file with another, open the document and refresh links.
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.4
User avatar
Villeroy
Volunteer
 
Posts: 28540
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Automatic Import of CSV into Sheet

Postby coreydpeterson » Wed Nov 13, 2019 5:54 pm

Hi,

Since I am driving by automation I cannot use any GUI interaction. The following code works for me:
Code: Select all   Expand viewCollapse view
  Dim oSheets
  Dim oSheet
  Dim oCell
  Dim SheetToExport
  Dim Apath
  Dim OutFileName
'get sheetname to export
   FileName = "/home/tester/HEEDS/openoffice/Input.csv"
   FileNum = FreeFile
   Open FileName For INPUT As FileNum
   Line Input #FileNum, sLine
   Line Input #FileNum, sLine
   sMsg1 = sMsg2 & sLine
   Line Input #FileNum, sLine
   sMsg2 = sMsg2 & sLine
   'wend
   Close #FileNum
   'Msgbox sMsg
   sSeparator=","
   sourcearray=split(sMsg1,sSeparator)
   SheetToExport = sourcearray(1)
   sourcearray=split(sMsg2,sSeparator)
   Apath = sourcearray(1)
   'Print Apath
OpenOffice 4.1.7 on Cent OS6
coreydpeterson
 
Posts: 4
Joined: Tue Nov 12, 2019 9:24 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 9 guests