How to Import csv file in calc when downloading is completed
-
- Posts: 19
- Joined: Fri Jun 17, 2022 9:45 am
How to Import csv file in calc when downloading is completed
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
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
Re: How to Import csv file in calc when downloading is completed
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"
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
-
- Posts: 19
- Joined: Fri Jun 17, 2022 9:45 am
Re: How to Import csv file in calc when downloading is completed
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.
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
Re: How to Import csv file in calc when downloading is completed
Your CSV is not located on the web
Code: Select all
fname = "file:////home/max/mytab.csv"
Download your file before run your import.
Co-admin french forum branch
-
- Posts: 19
- Joined: Fri Jun 17, 2022 9:45 am
Re: How to Import csv file in calc when downloading is completed
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
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
- 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
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...
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 94 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 .
Re: How to Import csv file in calc when downloading is completed
OpenOffice 4.1.12 - Windows 10
-
- Posts: 19
- Joined: Fri Jun 17, 2022 9:45 am
Re: How to Import csv file in calc when downloading is completed
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
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
- 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
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.
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 .
-
- Posts: 19
- Joined: Fri Jun 17, 2022 9:45 am
Re: How to Import csv file in calc when downloading is completed
Please try with link "https:://www.nseindia.com/api/allIndices?csv=true"
Main website is https://www.nseindia.com/market-data/li ... et-indices
Main website is https://www.nseindia.com/market-data/li ... et-indices
OpenOffice 4.0 on Windows 10
- 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
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 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!
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:
- Install CURL on your machine.
- use CURL and save cookies beforehand by reading the first page.
- Call the URL
https://www.nseindia.com/api/allIndices?csv=true
while giving CURL the previously downloaded cookie file at the same time.
- -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"
Enjoy!
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 .
- 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
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
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 82 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 .