Updating tables from CSV file?

[jonathanchye]

I have a client which gets emailed a CSV file containing the daily invoice updates. Currently data entry is all manual ie the employee responsible will open the CSV file and then copy/re-enter the information in an Excel table.

I am planning to automate all this. My question is if I create a table consisting of the same structure as the CSV file, is it possible to create a simple command/macro in Access to automatically read a CSV file and update as needed?

Any ideas/suggestions very much welcomed. Thank you.

 

[The_Doc_Man]

I believe you can treat the CSV file as a spreadsheet if it is identified as .CSV type, but I must admit I have not tried that lately.

At worst, import to Excel, clean it up manually, and import from Excel, which WILL work.

 

[jonathanchye]

Thank you for your reply. I haven't tried this before as well but client wants as much automation as possible.


Another question is how would I tackle this problem in Access? Let's say users dump data in an excel file. How would I read the file and pick only new values to import? Or perhaps pick a date range to import into Access?

 

[ChrisO]

In post #1 you mention "Access to automatically read a CSV file and update as needed"

Do you mean Access to automatically update the read of the CSV or is Access to update the CSV?
In other words; will Access need to write to the CSV?

Chris.

 

[jonathanchye]

In post #1 you mention "Access to automatically read a CSV file and update as needed"

Do you mean Access to automatically update the read of the CSV or is Access to update the CSV?
In other words; will Access need to write to the CSV?

Chris.

Hi, thank you for your reply. I would basically like Access to read information. From the csv and update the tables in Access as required. Basically the client is receiving a batch of delivery info sent as Csv. I would like to be able to grab the information from the csv into access. Client would mostly just use Access to view the data and generate charts and reports. They might want to also implement specific searches ie date range via Access.

Right now what they are doing is manually open the csv file and then cut and paste relevant cells into an Excel spreadsheet.

 

[ChrisO]

Simplest method would be to rename the CSV to something else other than its name in the email then go to: -

File>Get External Data>Link Tables

and directly link the renamed CSV to an Access table.

When a new CSV arrives, delete the Link, rename the CSV to its new name and re-link the table.
This last bit can be done at the click of a button.

Chris.

 

[jonathanchye]

Simplest method would be to rename the CSV to something else other than its name in the email then go to: -

File>Get External Data>Link Tables

and directly link the renamed CSV to an Access table.

When a new CSV arrives, delete the Link, rename the CSV to its new name and re-link the table.
This last bit can be done at the click of a button.

Chris.

Thanks for the idea. Delinking is angood suggestion but I am just wondering if there is a way to run an append or update query using a click of a button.

 

[ChrisO]

I’m almost certain it could be done with the click of a button. With the click of a button we can run VBA code and doing that can do almost anything. But the devils in the detail and you are not supplying much detail.

For example:
1. The CSV arrives via email so I guess that it will need manually extracting to some directory.
2. Code then re-links the raw CSV file to an Access table.
3. Update or append queries are run to take what you want and place the results into another table. Now at this point we need to look at some more detail. In Post #1 you talk about a table (singular) but in post #5 you talk about tables (plural) and there is a big difference.
4. We do not know the data in the CSV file and therefore can not say how that data should be distributed. Nor do we know your existing table structure. For all we know the CSV file may contain calculated totals which in turn would (should) be split back to units and unit cost.
5. We don’t know if the database is split.
6. We don’t know if it is, or ever will be, a multi-user system.
7. Should the CSV to table transfer be done by each FE or by the BE.


But yes, it should be possible at the click of a button, or done at Access opening, or done at Form open, on done on a timer event by monitoring the CSV file date/time stamp.

But the devil still remains in the detail.

Chris.

 

[jonathanchye]

I’m almost certain it could be done with the click of a button. With the click of a button we can run VBA code and doing that can do almost anything. But the devils in the detail and you are not supplying much detail.

For example:
1. The CSV arrives via email so I guess that it will need manually extracting to some directory.
2. Code then re-links the raw CSV file to an Access table.
3. Update or append queries are run to take what you want and place the results into another table. Now at this point we need to look at some more detail. In Post #1 you talk about a table (singular) but in post #5 you talk about tables (plural) and there is a big difference.
4. We do not know the data in the CSV file and therefore can not say how that data should be distributed. Nor do we know your existing table structure. For all we know the CSV file may contain calculated totals which in turn would (should) be split back to units and unit cost.
5. We don’t know if the database is split.
6. We don’t know if it is, or ever will be, a multi-user system.
7. Should the CSV to table transfer be done by each FE or by the BE.


But yes, it should be possible at the click of a button, or done at Access opening, or done at Form open, on done on a timer event by monitoring the CSV file date/time stamp.

But the devil still remains in the detail.

Chris.

Thank you Chris for the very detailed and helpful reply I've just had a first meeting so still waiting to finalise the details. Right, a few answers :

1) The code will be sent via email. Data in the CSV file would be fixed. It would consist of several columns ie : Department, Date of Invoice, Delivery Destination, Delivery Cost, Customer etc.

2) This CSV is basically a summary for transport costs emailed to the client from the courier company. My client will be using the courier's online system to make orders (they are currently using a more manual phone/email system). By using the couriers online system they won't be using their current system which means they would need to rely on the courier to send the information they need to avoid double data entry. This can be resolved as the courier company is willing to send some raw data in a CSV file. My client then wants me to grab data from this CSV file and create a database for it. They would want bespoke reporting features which could be easily implemented in Access if there's a way for me to update the data from the CSV file.

3) Table structure - this shouldn't be so tricky as every information should be available in the CSV file. We just need to make sure it could be updated correctly. I am thinking how should I prevent duplicate entry etc. So in essence there is mainly only one main table we are talking about which is the table with details about the daily transport costs. I guess I could further normalise that according to departments but that should be trivial once we find a way to import the data

4) The system would be a multi user system. However, only one user should be responsible for the "importing". The rest of the users would use the FE to see reports and query data by date range etc.

5) So according to 4), the BE should be the one "scanning" the directory which the CSV is dumped and then update information as needed? I am not so sure about this as I have never designed something like this before.

p/s: Thanks for the discussion by the way. I've realised a few details I need to finalise with the client which I would've overlooked otherwise

 

[ChrisO]

Because the file is read only it turns out fairly simple…

No BE required.

Create two tables in the FE:
tblInvoiceData: holds the data
tblLastImport: holds the last import date/time


Behind any Form which requires the data: -
Form timer interval set to 1 millisecond.

Option Explicit
Option Compare Text


Private Sub Form_Timer()

    Me.TimerInterval = 60000
    
    ImportCSV
    
    Me.Requery

End Sub

Which calls this in a standard module: -

Option Explicit
Option Compare Text

Public Const conCSVPath       As String = "\\COB\C\Access\"
Public Const conCSVName       As String = "InvoiceData.csv"
Public Const conTableName     As String = "tblInvoiceData"
Public Const conDateFormat    As String = "yyyy\-mm\-dd hh\:nn\:ss"
Public Const conDbFailOnError As Long = 128


Public Sub ImportCSV()
    Dim datDateLastAccessed As Date
    Dim datDateLastImport   As Date
    
    datDateLastAccessed = CreateObject("Scripting.FileSystemObject") _
                                       .GetFile(conCSVPath & conCSVName) _
                                       .DateLastAccessed
                   
    datDateLastImport = DLookup("LastImportDate", "tblLastImport")
    
    If datDateLastAccessed > datDateLastImport Then
        DoCmd.TransferText acImportDelim, , conTableName, conCSVPath & conCSVName, 0
        
        CurrentDb.Execute " UPDATE tblLastImport" & _
                          " SET LastImportDate = #" & Format(datDateLastAccessed, conDateFormat) & "#", conDbFailOnError
    End If
    
End Sub

Change names as required…

The operator receives an email attachment.
Extracts to \\COB\C\Access\InvoiceData.csv

Any Form which requires it will:
Reset its timer interval to 60000
Check InvoiceData.csv DateLastAccessed file property.
If greater than the last saved LastImportDate then import the data and update the LastImportDate.
Repeat every minute.

Chris.

 

[jonathanchye]

If I could thank you twice I would Thank you Chris. Brilliant solution! Looks like I might even meet the manic deadlines this time

A question though, isn't it better to save and update the date in a linked table (BE) on a network drive? So basically there are two FEs here. One is for the operator to update/import the CSV data. The other FE would be strictly to access information of the BE and subsequently used to query results, produce reports etc.

What do you think of this idea?

 

[ChrisO]

Yes, that method should work as well.

I did not go that way because I did not know if you already have a BE and there doesn’t seem to be a need for one. If this is all the database is doing then the CSV file is the BE and can be refreshed at anytime by calling ImportCSV.

It would be simple enough to test both ways and see what seems to fit best.

Chris.

 

[bilaal4d]

Because the file is read only it turns out fairly simple…

No BE required.

Create two tables in the FE:
tblInvoiceData: holds the data
tblLastImport: holds the last import date/time


Behind any Form which requires the data: -
Form timer interval set to 1 millisecond.

Option Explicit
Option Compare Text


Private Sub Form_Timer()

    Me.TimerInterval = 60000
    
    ImportCSV
    
    Me.Requery

End Sub

Which calls this in a standard module: -

Option Explicit
Option Compare Text

Public Const conCSVPath       As String = "\\COB\C\Access\"
Public Const conCSVName       As String = "InvoiceData.csv"
Public Const conTableName     As String = "tblInvoiceData"
Public Const conDateFormat    As String = "yyyy\-mm\-dd hh\:nn\:ss"
Public Const conDbFailOnError As Long = 128


Public Sub ImportCSV()
    Dim datDateLastAccessed As Date
    Dim datDateLastImport   As Date
    
    datDateLastAccessed = CreateObject("Scripting.FileSystemObject") _
                                       .GetFile(conCSVPath & conCSVName) _
                                       .DateLastAccessed
                   
    datDateLastImport = DLookup("LastImportDate", "tblLastImport")
    
    If datDateLastAccessed > datDateLastImport Then
        DoCmd.TransferText acImportDelim, , conTableName, conCSVPath & conCSVName, 0
        
        CurrentDb.Execute " UPDATE tblLastImport" & _
                          " SET LastImportDate = #" & Format(datDateLastAccessed, conDateFormat) & "#", conDbFailOnError
    End If
    
End Sub

Change names as required…

The operator receives an email attachment.
Extracts to \\COB\C\Access\InvoiceData.csv

Any Form which requires it will:
Reset its timer interval to 60000
Check InvoiceData.csv DateLastAccessed file property.
If greater than the last saved LastImportDate then import the data and update the LastImportDate.
Repeat every minute.

Chris.

Hi chris. I am still learning about access and yet i need to implement this code into a database I am working on. is there any chance you can explain how to do this, as i am still at a fairly basic level

regards, and much appreciated in advance