By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

Importing zipped csv file to Excel using VBA

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi guys,

I'm looking for some advice. I have to import a csv file which is on a website page in zip format. I have a piece of code thats supposed to work with winzip but I don't think the client has winzip available. Before I insist that they must get it I was wondering if anyone has any other method that would work with windows compressed files option.

This process has to be automated.

Any advice welcome.

Mary
Jan 4 '08 #1
Share this Question
Share on Google+
13 Replies


Rabbit
Expert Mod 10K+
P: 12,357
Is it legacy compression? Windows has it's own zip with command line functionality.
Expand|Select|Wrap|Line Numbers
  1. Copyright (C) 1990-1999 Info-ZIP
  2. Type 'zip "-L"' for software license.
  3. Zip 2.3 (November 29th 1999). Usage:
  4. zip [-options] [-b path] [-t mmddyyyy] [-n suffixes] [zipfile list] [-xi list]
  5.   The default action is to add or replace zipfile entries from list, which
  6.   can include the special name - to compress standard input.
  7.   If zipfile and list are omitted, zip compresses stdin to stdout.
  8.   -f   freshen: only changed files  -u   update: only changed or new files
  9.   -d   delete entries in zipfile    -m   move into zipfile (delete files)
  10.   -r   recurse into directories     -j   junk (don't record) directory names
  11.   -0   store only                   -l   convert LF to CR LF (-ll CR LF to LF)
  12.   -1   compress faster              -9   compress better
  13.   -q   quiet operation              -v   verbose operation/print version info
  14.   -c   add one-line comments        -z   add zipfile comment
  15.   -@   read names from stdin        -o   make zipfile as old as latest entry
  16.   -x   exclude the following names  -i   include only the following names
  17.   -F   fix zipfile (-FF try harder) -D   do not add directory entries
  18.   -A   adjust self-extracting exe   -J   junk zipfile prefix (unzipsfx)
  19.   -T   test zipfile integrity       -X   eXclude eXtra file attributes
  20.   -!   use privileges (if granted) to obtain all aspects of WinNT security
  21.   -R   PKZIP recursion (see manual)
  22.   -$   include volume label         -S   include system and hidden files
  23.   -h   show this help               -n   don't compress these suffixes
  24.  
Jan 4 '08 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Rabbit

Thanks for the suggestion.

Not sure how this will help me. Assuming I use it with the shell command, how do I direct the command to the website path and what command do I use to unzip and save contents as?

Mary
Jan 4 '08 #3

Rabbit
Expert Mod 10K+
P: 12,357
Hi Rabbit

Thanks for the suggestion.

Not sure how this will help me. Assuming I use it with the shell command, how do I direct the command to the website path and what command do I use to unzip and save contents as?

Mary
How did you do it with winzip? Does winzip let you specify a website path? I thought you were using a separate method to download the file first.
Jan 4 '08 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
How did you do it with winzip? Does winzip let you specify a website path? I thought you were using a separate method to download the file first.
No and on testing I'm having a similar problem with winzip. OK so I need a method to download the file from the website. I'll try a straight copy file first and see if that works.
Jan 4 '08 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
No and on testing I'm having a similar problem with winzip. OK so I need a method to download the file from the website. I'll try a straight copy file first and see if that works.
OK this is becoming something of a nightmare. I'm having problems with the fact that this is a zip file. I can find a http protocol to use to download it but it was designed for Access and I can't get it to work in Excel. This application has to be in Excel unfortunatley.

This is the protocol I have been trying to use

http://www.mvps.org/access/modules/mdl0037.htm

Every time I try to set a HTTP object I get a "object doesn't support this property or method" error

Expand|Select|Wrap|Line Numbers
  1. Dim objHTTP As HTTP
  2. Const conTARGET = "http://www.xxx....somefile.zip?"
  3.  
  4.   Set objHTTP = New HTTP ' error occurs here
  5.  
It works fine in Access btw.
Jan 4 '08 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
I'll be around for the next few hours and then I'm away for the weekend. I'll be back sometime on Monday.

Feel free to post any suggestions and I'll address them on my return.

Mary
Jan 4 '08 #7

puppydogbuddy
Expert 100+
P: 1,923
Hi Mary,
See if this link helps, excerpted below;code available at link as noted.
http://answers.google.com/answers/threadview?id=770960

Clarification of Answer by theta-ga on 16 Oct 2006 04:14 PDT
Hi nandun-ga,
You can download the Excel file containing the final code from:
http://rapidshare.de/files/36944619/...Final.xls.html

The following are the major changes made to the code:
- It now logs into the server, submits the form and then
stores the zip file contents that the server returns to it.
- It now waits for Winzip to extract the data.csv file into
the temp folder before it proceeds further.
- It deletes the temp files it created once its done


The logic is quite simple, and the code has comments explaining the
functionality. The logic is as follows:
- Use XmlHttp and send a GET request to the login page (login.asp)
- This will return the session cookie that we extract and store
- Using the session cookie we POST the username and password to
the processlogin.asp page. now we are logged in.
- Using the session cookie, we POST the country and drug data to
the getdata.asp page. Now we have selected the required data.
- Using the session cookie, we send a GET request to the
getdata.asp page. It returns the zip file contents to us.
- We write the returned data in a data.zip file in the temp folder
- We use winzip to extract data.csv from this zip file
- We then open data.csv in text mode and read it line by line,
adding the contents of each line to the Excel Worksheet.
- Once we are done, we delete the zip and csv files.

I am including the code below:
Jan 4 '08 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks pdog. I am not going through a login procedure as this is a public web page. This is the link to the download.

http://www.ecb.int/stats/eurofxref/eurofxref-hist.zip?

I'll check out the code though and see if it helps me out

Thanks

Mary
Jan 4 '08 #9

MMcCarthy
Expert Mod 10K+
P: 14,534
OK I'm getting file not found on all the download links for rapidshare.
Jan 4 '08 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
BTW, there is an xml version of the file available if that makes life any easier.

http://www.ecb.europa.eu/stats/eurof...f-hist-90d.xml
Jan 4 '08 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
OK I'm getting file not found on all the download links for rapidshare.
OK I've found the code.
Jan 4 '08 #12

puppydogbuddy
Expert 100+
P: 1,923
OK I'm getting file not found on all the download links for rapidshare.
Mary,
Here is the code that was printed at the main site:

I am including the code below:
================================================== ==============
Expand|Select|Wrap|Line Numbers
  1. Const zipFileName = "data.zip"
  2. Const csvFileName = "data.csv"
  3.  
  4. Sub fetchdata_Click()
  5.  
  6.     Dim fso As Object
  7.     Dim tempFolder As String
  8.     fetchdata.Caption = "Retrieving Data. Please Wait..."
  9.     fetchdata.Enabled = False
  10.  
  11.     On Error GoTo Cleanup
  12.     ' get path to a temp folder where the zip file will be downloaded
  13.     ' and its contents extracted
  14.     Set fso = CreateObject("Scripting.FileSystemObject")
  15.     tempFolder = fso.GetSpecialFolder(2) + "\" + fso.GetTempName + "\"
  16.  
  17.     'Download the zip file
  18.     DownloadZipFile tempFolder + zipFileName
  19.  
  20.     'extract csv file from the zip
  21.     ExtractCsvFile tempFolder + zipFileName, tempFolder
  22.  
  23.     ' Import csv contents. Paste them into the sheet starting 
  24.     ' at the specified cell
  25.     ImportCsvData tempFolder + csvFileName, Range("A1")
  26.  
  27. Cleanup:
  28.     'Delete the tem folder and files we created
  29.     DeleteAllFiles tempFolder
  30.     'Reset the button status
  31.     fetchdata.Caption = "Fetch Data"
  32.     fetchdata.Enabled = True
  33. End Sub
  34.  
  35. Sub DownloadZipFile(zipFileName As String)
  36.   Dim fso As Object
  37.   Dim xmlObj As Object, stream As Object
  38.   Dim strSource As String
  39.   Dim cookie As String
  40.   Set fso = CreateObject("Scripting.FileSystemObject")
  41.   Dim loginPageUrl As String, loginFormUrl As String, dataFormUrl As String
  42.   Dim loginFormData As String, dataFormData As String, responseData As Variant
  43.  
  44.   'If the output folder doesnt exist, create it
  45.   If Not fso.FolderExists(fso.GetParentFolderName(zipFileName)) Then
  46.     MkDir fso.GetParentFolderName(zipFileName)
  47.   End If
  48.  
  49.   loginPageUrl = "https://www.equestionbank.com/login.asp"
  50.   loginFormUrl = "https://www.equestionbank.com/processlogin.asp"
  51.   dataFormUrl = "https://www.equestionbank.com/getdata.asp"
  52.   loginFormData = "userid=guest&password=guest&submit=Login"
  53.   dataFormData = "country=UK&drug=Lipitor&submit=Get%20Data"
  54.  
  55.   'Make a request to get the session cookie
  56.   Call MakeSessionRequest("GET", loginPageUrl, loginFormData, cookie, True)
  57.   'Make a request to submit the login form
  58.   Call MakeSessionRequest("POST", loginFormUrl, loginFormData, cookie)
  59.   'Make a request to submit the data form
  60.   Call MakeSessionRequest("POST", dataFormUrl, dataFormData, cookie)
  61.   'Get the zip file contents from the server
  62.   responseData = MakeSessionRequest("GET", dataFormUrl, dataFormData, cookie)
  63.  
  64.   'Write the returned zip file contents to a file on disk
  65.   Set stream = CreateObject("adodb.stream")
  66.   Const adTypeBinary = 1
  67.   Const adSaveCreateNotExist = 1
  68.   Const adSaveCreateOverWrite = 2
  69.   stream.Type = adTypeBinary
  70.   stream.Open
  71.   stream.Write responseData
  72.   stream.savetofile zipFileName, adSaveCreateOverWrite
  73.   stream.Close
  74.   Set stream = Nothing
  75.   Set xmlObj = Nothing
  76. End Sub
  77.  
  78. Sub ExtractCsvFile(zipFileName As String, outputFolder As String)
  79.   Dim fso As Object
  80.   Set fso = CreateObject("Scripting.FileSystemObject")
  81.   'Use Winzip to extract the zip file contents
  82.   Shell "C:\progra~1\WinZip\winzip32.exe -min -e -o " + zipFileName + " " _
  83.         + outputFolder
  84.   'Wait for winzip to finish extraction of files
  85.   While Not fso.FileExists(outputFolder + csvFileName)
  86.     DoEvents
  87.   Wend
  88.  
  89. End Sub
  90.  
  91. Sub ImportCsvData(filepath As String, startCell As Range)
  92.    Dim colOffSet As Integer, rowOffset As Integer, i As Integer
  93.    Dim token() As String, fhandle As Integer, fline As String
  94.    'Read the csv file line by line
  95.    'Split line items at a comma
  96.    'Insert each item into a seperate cell
  97.    fhandle = FreeFile()
  98.    Open filepath For Input Access Read Lock Write As #fhandle
  99.  
  100.     While (Not (EOF(fhandle)))
  101.        Line Input #fhandle, fline
  102.        token = Split(Trim(fline), ",")
  103.        For i = 0 To UBound(token)
  104.            startCell.Offset(rowOffset, colOffSet).Value = token(i)
  105.            colOffSet = colOffSet + 1
  106.        Next i
  107.        colOffSet = 0
  108.        rowOffset = rowOffset + 1
  109.     Wend
  110.     Close #fhandle
  111. End Sub
  112.  
  113. ' This method uses XmlHttp to make a GET or POST call to the server
  114. ' It also optionally extracts the cookie information from the response headers
  115. ' It returns the entire response body.
  116. Function MakeSessionRequest(method As String, url As String, data As String, _
  117.  ByRef cookie As String, Optional ByRef updateCookie = False) As Byte()
  118.  
  119.   If Len(cookie) = 0 Then cookie = "dummy=dummy;"
  120.   httpReferrer = Trim(url)
  121.   postVars = Trim(data)
  122.  
  123.   Dim XMLHTTP As Object
  124.  
  125.     Set XMLHTTP = CreateObject("MSXML2.serverXMLHttp")
  126.     XMLHTTP.Open method, Trim(url), False
  127.  
  128.     If UCase(method) = "POST" Then
  129.       XMLHTTP.setRequestHeader "Content-Type", _
  130.                        "application/x-www-form-urlencoded"
  131.     End If
  132.     XMLHTTP.setRequestHeader "Referer", httpReferrer 'in case the server cares
  133.     XMLHTTP.setRequestHeader "Cookie", "to deal with XMLHTTP bug"
  134.     XMLHTTP.setRequestHeader "Cookie", cookie
  135.     XMLHTTP.send postVars
  136.  
  137.     'wait for response
  138.     While XMLHTTP.readyState <> 4
  139.       XMLHTTP.waitForResponse 1000
  140.     Wend
  141.  
  142.     ' extract the cookie data from the response header
  143.     If updateCookie Then
  144.       cookie = ""
  145.       strHeaders = XMLHTTP.getAllResponseHeaders()
  146.       hArr = Split(strHeaders, "Set-Cookie: ")
  147.       For kk = 1 To UBound(hArr)
  148.           theCookie = Left(hArr(kk), InStr(hArr(kk), "path=/") - 2)
  149.           cookie = cookie & " " & theCookie
  150.       Next
  151.     End If
  152.  
  153.     'return the response body
  154.     MakeSessionRequest = XMLHTTP.responseBody
  155.     Set XMLHTTP = Nothing
  156. End Function
  157.  
  158. Public Function DeleteAllFiles(ByVal FolderSpec As String) As Boolean
  159. 'Deletes all files in folder specified
  160. 'by parameter FolderSpec. Also deletes the folder itself.
  161. 'Does not delete subfolders or files within subfolders
  162. Dim oFs As New FileSystemObject
  163. Dim oFolder As Folder
  164. Dim oFile As File
  165.  
  166. If oFs.FolderExists(FolderSpec) Then
  167.     Set oFolder = oFs.GetFolder(FolderSpec)
  168.     On Error Resume Next
  169.     For Each oFile In oFolder.Files
  170.         oFile.Delete True 'deletes read-only file also
  171.     Next
  172.     DeleteAllFiles = oFolder.Files.Count = 0
  173.     oFs.DeleteFolder FolderSpec, True
  174. End If
  175.  
  176. End Function
Jan 4 '08 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks for all your help on this. Hopefully, I'll be able to work out what I need. I'll let you know how I get on. Might not be until Tuesday though.

Mary
Jan 4 '08 #14

Post your reply

Sign in to post your reply or Sign up for a free account.