asked on

Access VBA Code used for Loading data from CSV file

Hi ,

My VBA is of average standard but not great.
I inherited some code which is beyond my understanding.

In simple terms the code uploads data (esp a date field) from a CSV file in tblBankAIB

The format of the date I need is dd/mm/yyyy - However, the upload seems to mix dd/mm/yyyy and mm/dd/yyyy.

I believe that the code in mdlStatementsUpload is the relevant bit.
How do I adjust this code to ensure that the date format dd/mm/yyyy is what loads into tblBankAIB

This 53 second video shows the load process and error.
http://screencast.com/t/a8fTqePrZ

Thanks for help!
DateUploadDDMMYYYY.accdb
EEUploadDates.csv

Patrick O'Dea

ASKER

See the text in asterisks .... this may be the code that needs to be adjusted??

For i = 1 To lRows
    sText = Trim(wrksh.Cells(i, m_AIB_Col_Text).Value)
    If Trim(wrksh.Cells(i, m_AIB_Col_Type).Value) = "2" Then
      If wrksh.Application.WorksheetFunction.CountA(wrksh.Range(wrksh.Cells(i, 1), wrksh.Cells(i, m_AIB_Columns))) > 0 Then
        rec.AddNew
   ******************    [b] vtmp = wrksh.Cells(i, m_AIB_Col_Date).Value    ****************
        sDebit = wrksh.Cells(i, m_AIB_Col_DebitValue).Value
        sCredit = wrksh.Cells(i, m_AIB_Col_CreditValue).Value

Open in new window

ste5an

Why are you using Excel to import a CSV?

Use the Access import functions (wizard). They are more reliable, than those of Excel. Especially as depending on the Excel settings, you may not read what is in the file, but what Excel thinks it should be.

aikimark

I don't see any instance of an m/d/y ordered date in the CSV file that you posted with your question.

PatHartman

Import the CSV file ONCE manually. Press the advanced button which gives you options regarding how dates are formatted. Specify the format in the .csv file which looks like it is dd/mm/yyyy. Then in the final step, save the import spec with a nice name. In the future, you can automate the import and reference this Import Spec.

Keep in mind that Access will display dates according to your Windows settings. So, if your Windows dates are displayed as dd/mm/yyyy, then Access will respect that UNLESS you have specifically formatted a control. The other IMPORTANT point is that SQL Server natively assumes US date format which is mm/dd/yyyy so you have to be careful when working with text strings to format them as the query engine expects. And finally, dates are not stored as strings which is why databases (and even Excel) can deal with this at all. Dates are stored as double precision numbers with positive values being dates after Dec 30, 1899 and negative dates being dates prior to Dec 30, 1899. The origin date (0 date) is Dec 30, 1899. Time is stored as a decimal. NEVER under any conditions format a date except to display it for humans. In all code and queries, leave the date data types alone. DO NOT format them. Formatting a date turns it into a string at which point it behaves like a string rather than a date. It is hard enough in the US where we are not constantly fighting the battle of month vs day. In countries where date order is not US standard, it is even more important that you understand the difference between a string that looks like it might be a date and a field that actually is a date.

Patrick O'Dea

ASKER

Thanks all for contributions.

Pat, two simple questions:

1. You say "press the advanced button"..... Where is that exactly??

2. Also, can I clarify. When you say import the file once manually ... Are you implying that the database will somehow "learn" from this import and then behave differently (on a subsequent import) as a result of this learning.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Patrick O'Dea

ASKER

Thanks Pat, I'd never spotted that "advanced" button before.

PatHartman

It is very useful when working with import files that are slightly out of normal.