How to Import csv file in calc when downloading is completed

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Lovepreet323
Posts: 19
Joined: Fri Jun 17, 2022 9:45 am

How to Import csv file in calc when downloading is completed

Post by Lovepreet323 »

Please advise how to import csv file when downloading from web is completed.if i run below code then its shows error that file not exists.while file is still downloading

sub load_csv

fname = "file:////home/max/mytab.csv"
if len(fname)>0 then
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 = "All"
document = StarDesktop.loadComponentFromURL(fname, "_blank", 0, fileProps())
end if
msgbox "Done"
end sub
OpenOffice 4.0 on Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: How to Import csv file in calc when downloading is completed

Post by FJCC »

Your code works for me if I change the URL to one that exists on my system. I notice that there are four slashes after file:. Try changing that to three slashes. I am not on a Linux system at the moment, so I cannot test that.
fname = "file:///home/max/mytab.csv"
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.
Lovepreet323
Posts: 19
Joined: Fri Jun 17, 2022 9:45 am

Re: How to Import csv file in calc when downloading is completed

Post by Lovepreet323 »

Sir code works great when file exists on path.
My concern is that when csv file is still downloading from web than how to loop this load_csv macro to run after file completely downloaded in path.
OpenOffice 4.0 on Windows 10
Bidouille
Volunteer
Posts: 574
Joined: Mon Nov 19, 2007 10:58 am
Location: France

Re: How to Import csv file in calc when downloading is completed

Post by Bidouille »

Lovepreet323 wrote: Wed Jul 20, 2022 7:19 am when csv file is still downloading from web
Your CSV is not located on the web

Code: Select all

   fname =  "file:////home/max/mytab.csv"
Home directory is a local disk.

Download your file before run your import.
Lovepreet323
Posts: 19
Joined: Fri Jun 17, 2022 9:45 am

Re: How to Import csv file in calc when downloading is completed

Post by Lovepreet323 »

Still not getting my actual point. Csv file located on web is downloading by below RunChrome macro 1st. And then load_csv macro run which import csv file into ods.
My concern is that when csv file is still downloading from web and no file exists than how to loop this load_csv macro to run after file completely downloaded in path.

Sub RunChrome(param As String)

Dim SysShell As Object

SysShell = createUNOService("com.sun.star.system.SystemShellExecute")
SysShell.execute("C:\Program Files\Google\Chrome\Application\chrome.exe",param,0)
End Sub
OpenOffice 4.0 on Windows 10
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: How to Import csv file in calc when downloading is completed

Post by Sébastien C »

Hi there,

Dear Lovepreet323, I've been reading you since this morning and I still don't understand why you store the CSV file first on your hard disk to read it later... Why not, for example, import the CSV directly into a sheet from Calc? Why launch a browser on, I assume, a CSV file, when the “fileCopy” instruction might do the job? For you, I have uploaded an exercise “mytab.csv” file. The address is
http://roubo.art/documents/smcj/mytab.csv
And I attach to this post a Calc file with two procedures: “load_csv1()” and “myGoodCopy()”. Needless to say that I did the test under W$; and it worked. Tell me a little more for what you understand of my questions...

Code: Select all

Sub load_csv1()
 Dim      mySheet As Object
 Dim    myCSVFile As String

   mySheet = thisComponent.currentController.activeSheet
 myCSVFile = "http://roubo.art/documents/smcj/mytab.csv"

 ' Include the CSV file as linked in the sheet.
 mySheet.link(myCSVFile, "", "Text - txt - csv (StarCalc)", "44,34,76,1,1/2", com.sun.star.sheet.SheetLinkMode.NORMAL)
 '  44 =          Field separator : comma.
 '  34 =     Text field delimiter : quote character ".
 '  76 =                 Encoding : Unicode (UTF-8).
 '   1 = First line to be treated : line 1.
 ' 1/2 =            Column format : column 1 is formating in TEXT (2).

 ' Break the link
 mySheet.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)
End Sub


Sub myGoodCopy()
 fileCopy "http://roubo.art/documents/smcj/mytab.csv", "C:\Users\YOUR_NAME\Documents\mytab.csv"
End Sub
Attachments
testPerso.ods
(9.02 KiB) Downloaded 74 times
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
Mr.Dandy
Posts: 427
Joined: Tue Dec 11, 2012 4:22 pm

Re: How to Import csv file in calc when downloading is completed

Post by Mr.Dandy »

OpenOffice 4.1.12 - Windows 10
Lovepreet323
Posts: 19
Joined: Fri Jun 17, 2022 9:45 am

Re: How to Import csv file in calc when downloading is completed

Post by Lovepreet323 »

My web link had not ending with .csv . Like below
myCSVFile = "http://roubo.art/documents/smcj"
My link is "https://__________?__=__&FromDate=15/07/2022&UptoDate=22/07/2022"
This link not working in your code.pls help
OpenOffice 4.0 on Windows 10
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: How to Import csv file in calc when downloading is completed

Post by Sébastien C »

Give us a correct address (without underscore), possibly in private message if it is private for you; so that we can do real tests. There, you may be able to hope that we make an effort of which you prove very little the reciprocal. Otherwise, I suggest that you do not ask questions whose answers are simply impossible to give you. :roll:
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
Lovepreet323
Posts: 19
Joined: Fri Jun 17, 2022 9:45 am

Re: How to Import csv file in calc when downloading is completed

Post by Lovepreet323 »

Please try with link "https:://www.nseindia.com/api/allIndices?csv=true"
Main website is https://www.nseindia.com/market-data/li ... et-indices
OpenOffice 4.0 on Windows 10
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: How to Import csv file in calc when downloading is completed

Post by Sébastien C »

It's a cookie issue. The page
https://www.nseindia.com/market-data/li ... et-indices
gives you, via javaScript, access to the CSV file (Download (.csv)) ONLY if it has written a cookie in your browser.

Solution: You note the options of the curl command:
  • -A is a User agent (to be discreet...)
  • -c: Write cookies to <filename> after operation
  • -b: Send cookies from string/file
  • -o: Write to file instead of stdout

Code: Select all

curl -A "Mozilla/5.0 (X11; Linux x86_64; rv:101.0) Gecko/20100101 Firefox/101.0" -c myCookiesJar.txt "https://www.nseindia.com/market-data/live-market-indices"
curl -A "Mozilla/5.0 (X11; Linux x86_64; rv:101.0) Gecko/20100101 Firefox/101.0" -b myCookiesJar.txt -o "myGoodCSV.csv" "https://www.nseindia.com/api/allIndices?csv=true"
You can do this with the service "com.sun.star.system.SystemShellExecute"; and read a CSV file, huh, you know how to do it, right???
Enjoy! :geek:

 Edit: I said something stupid: rather than the service “com.sun.star.system.SystemShellExecute”, use the Shell instruction which is much better at waiting for the end of a procedure (which is important here between the two loads). 
Last edited by Sébastien C on Fri Jul 22, 2022 8:17 pm, edited 1 time in total.
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
User avatar
Sébastien C
Posts: 111
Joined: Mon Jan 04, 2010 5:06 pm
Location: Meymac, France

Re: How to Import csv file in calc when downloading is completed

Post by Sébastien C »

An example to obviously work according to your needs.

Your system work with M$-Window$ and I assume you will have installed CURL in
C:\curl-7.84.0_6-win64-mingw\bin\

If not, you will know how to correct (or change your PATH system).

I notice (without having looked for a solution) that OpenOffice asks for the refresh of the sheet in a dialog box; what LibreOffice does not do. But maybe you won't include the retrieved file this way.

Enjoy
;)

Code: Select all

Option Explicit

Private Const  curlCommand As String = "curl"                                      ' Linux.
Private Const  curlCommand As String = "C:\curl-7.84.0_6-win64-mingw\bin\curl.exe" ' M$-Window$.
Private Const  myUserAgent As String = "Mozilla/5.0 (X11; Linux x86_64; rv:101.0) Gecko/20100101 Firefox/101.0"
Private Const myCookiesJar As String = "myCookiesJar.txt"
Private Const    myCSVFile As String = "myGoodCSV.csv"

Private Const    myUrlHTML As String = "https://www.nseindia.com/market-data/live-market-indices"
Private Const     myUrlCSV As String = "https://www.nseindia.com/api/allIndices?csv=true"

Sub loadCSV()
 Dim          mySheet As Object
 Dim myTempoDirectory As String,   myParams As String

 If Not globalScope.basicLibraries.isLibraryLoaded("Tools") Then globalScope.basicLibraries.loadLibrary("Tools")

          mySheet = thisComponent.currentController.activeSheet
 myTempoDirectory = convertFromURL(getPathSettings("Temp")) & getPathSeparator

 ' First load of HTML page (just for savec the cookies).
         myParams = "-A """ & myUserAgent & """ -c """ & myTempoDirectory & myCookiesJar & """ """ & myUrlHTML & """"
 shell(curlCommand, 2, myParams, True)

         myParams = "-A """ & myUserAgent & """ -b """ & myTempoDirectory & myCookiesJar & """ -o """ & myTempoDirectory & myCSVFile & """ """ & myUrlCSV & """"
 shell(curlCommand, 2, myParams, True)

 ' Include the CSV file as linked in the sheet.
 mySheet.link(myTempoDirectory & myCSVFile, "", "Text - txt - csv (StarCalc)", "44,34,76,1,1/2", com.sun.star.sheet.SheetLinkMode.NORMAL)
 '  44 =          Field separator : comma.
 '  34 =     Text field delimiter : quote character ".
 '  76 =                 Encoding : Unicode (UTF-8).
 '   1 = First line to be treated : line 1.
 ' 1/2 =            Column format : column 1 is formating in TEXT (2).

 ' Break the link
 mySheet.setLinkMode(com.sun.star.sheet.SheetLinkMode.NONE)
End Sub
Attachments
loadCSVwithCURL.ods
(9.76 KiB) Downloaded 59 times
LibreOffice v. 7.3.2.2, under GNU-Linux Mint and, in virtualization and just for tests, LibreOffice v. 7.3.2.2 an OpenOffice v. 4.1.12 under M$-W 10 :ouch: .
Post Reply