Append Information from a Website

LadyDi

Registered User.
Local time
Today, 07:15
Joined
Mar 29, 2007
Messages
894
Someone within my company is going to post several csv files to an intranet site each day. I would like to be able to compile that data in a database without needing to go out and append each file individually. Is there a way this can be automated? Any assistance you can provide would be greatly appreciated.
 
Yes but it takes a few functions / code to do this. I have wrote quite a few databases that does this so far but one of the key pieces to this is using a file dialog box and being able to select and process multiple files one after the other. I am not sure where your at or what you exactly need but let me know.
 
I have recently joined a new department that works directly with my company's customers. One of our customers has requested that we set up reporting based on .csv files automatically sent from a piece of equipment every day denoting if that equipment encounters any problems. Someone in our IT department has set it up, so that these .csv files will be sent from the customer's equipment to my department's SharePoint site. There are approximately 30 machines involved in this project. We would like to set a database up to automatically gather the information in those .csv files (including the file name if possible), so that we can then export only the pertinent information to Excel and then create pareto charts to send to the customer.

Your assistance is greatly appreciated.
 
Are you designing this database and are asking for assistance or are you asking for someone to design it for you?
 
I am designing the database, I just need assistance with the importing piece of it. I have imported files off network drives one at a time, many times. However, I have never tried to use VB to import several files automatically off a SharePoint site. I was just trying to give you all the details that I have on the project to give you an idea of what I am trying to do.

If you could provide me with code to accomplish this and the best place to put it (on a form or in a module), I can take it from there.
 
I would first start at the basics. Create a function that will parse out the information from a text file an insert it into a table with the proper delimiter assigned ("," or "|" best to use a varibale to have the user pick the delimter). then pass the file contents through the function.

Once you have the funtion ironed out, use an Application.FileDialogue to setup a reaaly nice file picker (use .AllowMultiSelect for selecting mul files) Use a for each loop for the files to pass through the funtion and BAM. you got it!
 
Ok hopefully this will get you started. No worries I was just making sure that you were not asking someone to design the project. This code goes in a module.

Code:
Private Arr() As Variant
Function RetrieveManningData()
Dim lShop As Long, xlsApp As Excel.Application
Dim xlsWorkbook As Excel.Workbook, xlsWorkSheet As Worksheet
Dim xlsSheet1 As Worksheet
Dim strProject As String, strTSD As String, iXLSPosition As Long
iXLSPosition = 1
Set xlsSheet1 = Excel.Worksheets("Manning")
Set xlsApp = New Excel.Application
ReDim Arr(1 To 100)
BrowseFile "Excel"
If bCanceled = True Then
    Exit Function
End If
For x = LBound(Arr) To UBound(Arr)
    
    'Setup process to retrieve the informaiton
    debug.print CStr(Arr(x)) ' Returns File location and name
    'Break from array if findshop returns 0
    If lShop <> 0 Then
        'Debug.Print lShop
        Set xlsWorkbook = xlsApp.Workbooks.Open(CStr(Arr(x)))
        Set xlsWorkSheet = xlsApp.Worksheets("qryRAPAssignment")
        ' Do data digging here
    End If
    xlsApp.Quit
Next x
Set xlsSheet1 = Nothing
Set xlsWorkSheet = Nothing
Set xlsApp = Nothing

End Function
Function BrowseFile(Optional strFilter As String) As Variant
Dim fdg As FileDialog, vrtSelectedItem As Variant
Dim V() As Variant
ReDim V(1 To 100)
Dim i As Long
Set fdg = Application.FileDialog(msoFileDialogFilePicker)
If strFilter <> "" Then
    fdg.Filters.Clear
    
    Select Case strFilter
        Case "Pictures"
            fdg.Filters.Add "Pictures", "*.jpg; *.gif; *.bmp"
        Case "PDF"
            fdg.Filters.Add "PDF", "*.pdf"
        Case "Excel"
            fdg.Filters.Add "Excel", "*.xls; *.xlsx"
        Case "CSV"
            fdg.Filters.Add "CSV", "*.csv"
            
    End Select
End If
With fdg
    .AllowMultiSelect = True
    .InitialView = msoFileDialogViewDetails
    If .Show = -1 Then
        For Each vrtSelectedItem In .SelectedItems
            i = i + 1
            V(i) = vrtSelectedItem
        Next vrtSelectedItem
        Arr = V
    End If
    If .SelectedItems.Count = 0 Then
        bCanceled = True
    Else
        bCanceled = False
    End If
End With
  
Set fdg = Nothing
End Function

Ok so I stripped most of my data stuff from there. I know there are a few other things that would need cleaned up but hopefully it will get you enough to get started.

Also one last thing you need to add a reference in your VBA project for "Microsoft Office XX.X Object Library" or the browsefile function will not work.

The idea behind what I did is the browsefile function returns and array of all the files and locations that were selected. Then you just step through the array accessing the files and running your routines to get the data or whatever.
 
Thank you very much for the code. However, I am having a little trouble with it. When I try to run it, I get a message stating "User-defined type not defined", and it hightlights the "xlsApp as Excel.Application" line of code. I'm afraid, I don't know what it is looking for here. Any suggestions? I did add Microsoft Office Object Library as a reference.
 
Add a reference to "Microsoft Excel 1x.0 Object Library".
 
Thank you. I added the reference to Microsoft Excel Object Library and that cleared up that problem. Unfortunately, now I have another problem. Now it highlights "If bCanceled = True Then" and says "Variable not defined". If I comment out that If statement, then it highlights "For x = LBound (Arr) to UBound(Arr)" and says "Variable not defined". What should I do now?
 
Private Arr() As Variant
has to go at the very top of the module not inside a function. This makes it available to all functions in that module.

Secondly the code was only a guideline as some of it I did not clean out completly. So take your time and read the error messages. If it says Variable not defined then that's exactly what it means.
 
Lastly bCanceled should also be located at the top with Arr being declared just like this.

Private bCanceled as boolean
 
Okay, I took the "Option Explicit" completely out of the module and added the "Private bCanceled as Boolean" at the top. That solved the first problems.

I understand this is to be a guide. I'm afraid, when the VB gets more complex, I don't know what I need to do to fix it. I realize, when it says that the "variable is not defined", that I need to define it. My problem, is I don't know how it needs to be defined, so that the code will run. For example, on the line that says "Set xlsSheet1 = Excel.Worksheets("Manning")", I assumed that "Manning" was the name of the Excel sheet where the data was to be found. The Excel sheet where my data will be found is simply titled "Data". So I made this change. However, I am getting an error that says "Method 'Worksheets' of object'_Global' failed", and it hightlights that line of code. I don't know what that problem is. I have never seen that message before.
 
Option Explicit is ok to have at the top as well because it makes you declare your variables before you use them. Don't get me wrong everyone has to start somewhere but to do the functionality that you are trying to do is going to take quite some effort and understanding VBA.

I'll go through the code and clean it up some more and also understand that some of the code I gave you I wrote in Excel and Access. There are differences in the VBA structure and what functions to use and how to use them in either.

Again try to be patiant as I am writing eight projects write now that are really big. 10K + users but I'll smooth out the code and repost it either later today or tomorrow.

Have a good one.
 
I would first start at the basics. Create a function that will parse out the information from a text file an insert it into a table with the proper delimiter assigned ("," or "|" best to use a varibale to have the user pick the delimter). then pass the file contents through the function.

Once you have the funtion ironed out, use an Application.FileDialogue to setup a reaaly nice file picker (use .AllowMultiSelect for selecting mul files) Use a for each loop for the files to pass through the funtion and BAM. you got it!

How would you go about doing this? The delimiter is a comma. Before I started this project, I felt like I knew a good bit of vba. Now, I'm not so sure. Please help me :banghead:
 
Lady Di,

I am assuming that each of the csv files will have the same layout and format. If this is correct, have you considered creating an Import Specification in Access. To do this you simply go through the process of manually importing one of the csv files and just as you get to the final step in the import wizard, click the Advanced button at the lower left corner of the wizard and one of the options is to save the import definition as an Import Specification. You will just give the specification a name and save it.

Then you can use the:
Docmd.TransferText method in VBA code, providing all of the parameters including the name of the import specification that you saved. It will import the csv file to the table you specify. This can be a temporary table.

If you know that there will only be a specific list of csv files then all you have to do is create a process to import each of the csv files and then you can determine if you want to process the new records in your temporary table before you clear all of current records and import the next csv file. Here I am thinking that you might want to have an append query that would append these new records to a permanent table, adding some formatting as needed.

You would then just repeat the process for each file you know you need to import.

You should need very little code to just import the records.

One side note: If you are attempting to import these records from files in a SharePoint library, you may find that you will need to copy the file to a temporary folder and import it form there. I have been importing Excel files like this and I had to copy the file to a temp folder, import the data and then delete the copy of the file.

Hope this helps.
 
Is there a way to import the file name as one of the columns in the table?
 
I am sorry, but I do not understand what you are wanting to do. Can you provide a little more detail?
 
Is there a way to import the file name as one of the columns in the table?

You could update a file field during the import process to store this information on the new records. This is done using a paramertised query where the filename is taken by VBA and passed to the parameter.

However I would be inclined to refine this and store the filenames in a separate table then use a number in the main table as a foreign key to incicate the file record.

Generally import jobs are best done to a holding table then the any extra processing done there (eg adding the key) before inserting the records into the final table.

Normally I would complete the import entirely as text to ensure all the records will be accepted then validate and convert the datatype before or during the insert.

However these days I have moved entirely over to processing imported text using a TextStreamObject to read the file and then writing to recordsets. This allows for much more versatility than can be supported with an ordinary text import. For example, multiline records can be read.

Where the import is done daily I have converted this processing into a VB.NET application run on the Scheduler so I never even have to think about it at all.
 
I have a few questions for you.

How can I get the database to import the file names into a different table? If the data and the file name are two different imports, how can I get it set up to link the two together?

How would you write a parameterised query where the filename is captured by vb? I've tried everything I can think of to get the database to pickup the file name, and nothing seems to work. I need the file name, because that is where the unit ID and report date are listed (all file names are as follows: Date_UnitID_Summary.csv)

How do you use the textStreamObject? I've never heard of that one, and don't know where to begin.

Does a VB.NET application have to be run on a server? If so, our IT department won't let us do that. If not, could you tell me how to do that too? It would be awesome to automate this process that much. This import will need to be done on a daily basis. Everyday, there will be a file added for each unit involved with this project. So there will need to be about thirty spreadsheets imported every day.

If it would help at all, I am attaching the database I have been working on and a sample csv file. I have a "TemporaryTable" and an "IDMSummary" table (the second is my permanent main table). I tried doing a TransferText command on a button on the "test" form but couldn't get that to work. I also tried the modual provided earlier in this post and could not get that to work. If I could just get the data import automated, I know I could take it from there. I just have not had any luck automating this import.

I really appreciate your help and patience.
 

Attachments

Users who are viewing this thread

Back
Top Bottom