locked
Importing .csv file into MS-Access RRS feed

  • Question

  • Hi- I get a .csv file ftp'd to me daily at 5pm.  I'd like to build an app to automatically grab the file and import it into a MS-Access table.  Any ideas where to start- is file watcher capable of doing this?  To complicate matters, the file I receive has a new name each day.  I'm very new to VB, and am working on this as kind of a "proof of concept" project.
    • Moved by Jie Bao Wednesday, December 29, 2010 2:03 AM access (From:Visual Basic General)
    Tuesday, December 28, 2010 8:42 PM

Answers

  • That looks like VBA, but when I paste it into an Access Module, I get some errors immediately.

    Anyway, try this:

    strPath = "C:\ups-dqci\Outbound\"

    Does that work for you?

    • Proposed as answer by Bessie Zhao Wednesday, January 5, 2011 10:43 AM
    • Marked as answer by Bessie Zhao Friday, January 7, 2011 10:35 AM
    Tuesday, January 4, 2011 12:44 AM

All replies

  • What part of this process are you needing help with? You're not being specific here.

    Have you started anything on it yet?

    Tuesday, December 28, 2010 11:14 PM
  • 1. [File Access with Visual Basic ]

    2. [ADO.NET ]


    在現實生活中,你和誰在一起的確很重要,甚至能改變你的成長軌跡,決定你的人生成敗。 和什麼樣的人在一起,就會有什麼樣的人生。 和勤奮的人在一起,你不會懶惰; 和積極的人在一起,你不會消沈; 與智者同行,你會不同凡響; 與高人為伍,你能登上巔峰。
    Wednesday, December 29, 2010 1:38 AM

  • Hey Wiener!  There are several examples here:

    http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

    Change the "*.xls" stuff to "*.csv" and you're in business.

     

    Wednesday, December 29, 2010 3:08 PM
  • Ok- I used this as a template, replacing Excel with .CSV. When I run the code, I don't get any error messages, but I don't see any files importing to my database either. Can't figure out what I've done wrong- hoping you may have some idea? Here is my code...
    Public Class File_Importer
    
      Private Property acImport As Object
    
      Private Property acImportDelim As Object
    
      Private Sub btnImportFile_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImportFile.Click
        Dim strPathFile As String, strFile As String, strPath As String
        Dim strTable As String
        Dim blnHasFieldNames As Boolean
    
        ' Change this next line to False if the first row in text file has no field names
    
        blnHasFieldNames = True
    
        'Next line contains the path where the import .csv files are located
    
        strPath = "C:\ups-dqci\Outbound"
    
        ' Next line contains name of the table into which the data are to be imported
    
        strTable = "QVD_Feed"
    
        strFile = Dir(strPath & "*.csv")
        Do While Len(strFile) > 0
          strPathFile = strPath & strFile
          DoCmd.TransferText(acImport, acImportDelim,
             "QVD_Feed", "C:\ups-dqci\Outbound\*.csv", True)
    
          ' Uncomment out the next code step if you want to delete the file after it's been imported
    
          '    Kill strPathFile
    
          strFile = Dir()
        Loop
      End Sub
    
    End Class
    
    Wednesday, December 29, 2010 10:20 PM
  • That looks like VBA, but when I paste it into an Access Module, I get some errors immediately.

    Anyway, try this:

    strPath = "C:\ups-dqci\Outbound\"

    Does that work for you?

    • Proposed as answer by Bessie Zhao Wednesday, January 5, 2011 10:43 AM
    • Marked as answer by Bessie Zhao Friday, January 7, 2011 10:35 AM
    Tuesday, January 4, 2011 12:44 AM
  • Hello wienerdogman,

    How this issue is going on in your side. Does ryguy72's suggestion work for you? I am writing to check the status of the issue on your side. Could you please let me know if this suggestion works for or not? If you have any question or concern, please feel free to follow up or unmark it back. Have a nice day.


    Bessie Zhao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, January 5, 2011 10:42 AM