Opening .CSV From Access

[twoplustwo]

Hi all.

I have had a look around the forums for a similar thread but it didn't quite match my requirements.

I am trying to read in a file for each customer in rsCustomer. Each records in the recordset builds the following file path:

R:\Portfolio\GB Consumption Account\B\Bend\Aggregate.FC2

Where the first letter/customer name changes depending on present customer.

Similar code works in Excel and I think I have refernces turned on but I'm a little short on ideas!

Public Sub TestImport()
Dim db As Database
Dim dtEndDate As Date
Dim dtStartDate As Date
Dim rsCustomer As DAO.Recordset
Dim strCustomer As String
Dim strCustomerLetter As String
Dim strFilePath As String
Dim strFullFilePath As String
Dim strToOpen As String
Dim strRuid As String
Dim strSql As String
Dim xlApp As Excel.Application
Dim xlBookFile As Excel.Workbook
Dim xlRangeToCopy As Excel.Workbook
Dim wb As Excel.Workbook
Set db = CurrentDb
dtStartDate = CDate(Format(#1/4/2009#, "dd/mm/yyyy"))
dtEndDate = CDate(Format(#1/4/2009#, "dd/mm/yyyy"))
strSql = "SELECT Customer FROM tblCustomerExc GROUP BY Customer"
Set rsCustomer = db.OpenRecordset(strSql)
With rsCustomer
    If Not .BOF Then
        Do While Not .EOF
        strCustomer = !Customer
        
        strCustomerLetter = Left(strCustomer, 1)
        strFilePath = "R:\Portfolio\GB Consumption Account\"
        strToOpen = "\Aggregate.FC2"
        strFullFilePath = strFilePath & strCustomerLetter & "\" & strCustomer & strToOpen
                       
        Workbooks.OpenText _
        Filename:=strFullFilePath, _
        Origin:=xlWindows, _
        StartRow:=1, _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, _
        Semicolon:=False, _
        Comma:=True, _
        Space:=False, _
        Other:=False
        
        Loop
    End If
End With
Set rsCustomer = Nothing
End Sub

Thanks for any help!

 

[DCrake]

Apart from the obvious what are you attempting to do? and what is the reason for the FC2 extension ? Don't know if Access will recognise it?

David

 

[twoplustwo]

Hi DC.

We generate new forecast positions overnight for each customer. The algorithm that generates this forecast stores the data in these CSV files with different extensions that open as CSV files. FC2 = Forecast2 for info. A graphical viewer utilises these files to display data.

For a given date the database will select the appropriate range and import it into the database. I have this working in Excel fwiw.

There is no database that stores the files and I'm working on a project where I need to know FC2 values for each customer. FC1 (Forecast1) data *is* held in an Oracle database but this is useless for this task as this forecast is the version loaded when we sign a customer.

 

[twoplustwo]

Right, in the immediate window if I type:

?workbooks(1).Worksheets(1).cells(1,1).value

01/10/2006

is returned - the first value in the worksheet. So it is opening the workbook I guess, I just can't see it.

 

[gemma-the-husky]

look at

docmd.transfertext

this will import the csv into a table in access (the same as in excel)

its just that in excel you get it visible in the current worksheet - in access it just becomes a new table, that you can then use. note that access import is FAR more powerful than the one in excel

 

[namliam]

You are trying to import this file into access??
If so, make a copy using the "Filecopy" copmmand to a temporary .CSV file.

Access requires the extension to be CSV or TXT (and some others) for loading text files.

FYI
An excel app is by default opened "Visible = false". You can use xlApp.visible = true to make it visible.
Ofcourse loading the CSV directly to Access is highly preferably and MUCH faster.

 

[twoplustwo]

Hi guys,

I have the workbook open, the range selected and copied.

Because each file contains all forecast data since we signed the business I need to select different ranges rather than importing the entire thing. Thus, loading the entire file seems like overkill. The code I have so far reads as:

Public Sub TestImport()
Dim db As Database
Dim dtEndDate As Date
Dim dtStartDate As Date
Dim lngFcStartDate As Long
Dim lngStartDate As Long
Dim lngDateParameterDifference As Long
Dim lngFcToDate As Long
Dim lngFcToRow As Long
Dim rsCustomer As DAO.Recordset
Dim strCustomer As String
Dim strCustomerLetter As String
Dim strFilePath As String
Dim strFullFilePath As String
Dim strToOpen As String
Dim strRuid As String
Dim strSql As String
Dim xlRangeToCopy As Excel.Workbook

Set db = CurrentDb
dtStartDate = CDate(Format(#1/4/2009#, "mm/dd/yyyy"))
dtEndDate = CDate(Format(#1/4/2009#, "mm/dd/yyyy"))
strSql = "SELECT Customer FROM tblCustomerExc GROUP BY Customer"
Set rsCustomer = db.OpenRecordset(strSql)
With rsCustomer
    If Not .BOF Then
        Do While Not .EOF
        strCustomer = !Customer
        
        strCustomerLetter = Left(strCustomer, 1)
        strFilePath = "R:\Portfolio\GB Consumption Account\"
        strToOpen = "\Aggregate.FC2"
        
        'Full path to open
        strFullFilePath = strFilePath & strCustomerLetter & "\" & strCustomer & strToOpen
                       
        'Open workbook
        Workbooks.OpenText _
        Filename:=strFullFilePath, _
        Origin:=xlWindows, _
        StartRow:=1, _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, _
        Semicolon:=False, _
        Comma:=True, _
        Space:=False, _
        Other:=False
        
        lngFcStartDate = Range("A1")                               'Set open RAFT file first date
        lngStartDate = CLng(dtStartDate)                           'Convert parameter from excel to lng
        lngDateParameterDifference = CLng(dtEndDate - dtStartDate) 'Diff in days between parameters in excel
        lngFcToDate = CLng(lngStartDate - lngFcStartDate) + 1      'Diff in days between excel parameter and file
        lngFcToRow = lngFcToDate + lngDateParameterDifference      'Row number to go to in file
                
            If Workbooks("Aggregate.FC2").Worksheets("Aggregate").Range("A" & lngFcToDate) <> "" Then
               Workbooks("Aggregate.FC2").Worksheets("Aggregate").Range("A" & lngFcToDate & ":AW" & lngFcToRow).Copy
                
            'Data to be written to tblFC2
            
            End If
        
        Debug.Print strCustomer & " " & Workbooks(1).Worksheets(1).Cells(1, 1).Value
        ActiveWorkbook.Close
        .MoveNext
        Loop
    End If
End With
Set rsCustomer = Nothing
End Sub

My issue now is writing this data to tblFC2 in my database. Some form of append action like PasteAppend would have helped but I can't seem to find it.

Thanks again, I hope this is clearer.

 

[namliam]

You are going to load the complete file anyways.... Listen to the advice of the "experts" that you yourself are trying to find.... Import your CSV into a temp table.... Easier, faster, less prown to errors.

 

[twoplustwo]

Mailman,

I am trying this. FileCopy gives me a path/file error (75) - I think due to the extension (.FC2)

 

[namliam]

Filecopy is not bothered by extensions... Offcourse you want to change the extension to .CSV... so some trickery is needed to change the name.

What is the command you are trying to execute?

 

[twoplustwo]

Morning mailman.

It looks like I am sorted.

Public Sub CopyForecastFiles()
Dim db As Database
Dim dtEndDate As Date
Dim dtStartDate As Date
Dim fs As Scripting.FileSystemObject
Dim rsCustomer As DAO.Recordset
Dim rsFC2 As DAO.Recordset
Dim strCopiedFile As String
Dim strCustomer As String
Dim strCustomerLetter As String
Dim strFilePath As String
Dim strFullFilePath As String
Dim strToOpen As String
Dim strSql As String
Dim xlApp As Excel.Application
Set db = CurrentDb
Set fs = New Scripting.FileSystemObject
Set xlApp = Excel.Application
dtStartDate = CDate(Format(#1/4/2009#, "mm/dd/yyyy"))
dtEndDate = CDate(Format(#1/4/2009#, "mm/dd/yyyy"))
strSql = "SELECT Customer FROM tblCustomerExc GROUP BY Customer"
Set rsCustomer = db.OpenRecordset(strSql)
With rsCustomer
    If Not .BOF Then
        Do While Not .EOF
        strCustomer = !Customer
        
        strCustomerLetter = Left(strCustomer, 1)
        strFilePath = "R:\Portfolio\GB Consumption Account\"
        strToOpen = "\Aggregate.FC2"
        
        'Import CSV
        'Full path to open
        strFullFilePath = strFilePath & strCustomerLetter & "\" & strCustomer & strToOpen
        'Copy to temp area location
        strCopiedFile = p_strCopyLocation & strCustomer & ".CSV"
        'Copy actual, paste to temp
        fs.CopyFile strFullFilePath, p_strCopyLocation & strCustomer & ".CSV"
        'Import full file
        DoCmd.TransferText acImportDelim, , "tblTemp", strCopiedFile, False
        
        'Append required data
        DoCmd.SetWarnings False
        DoCmd.OpenQuery ("appFC2")
        DoCmd.SetWarnings True
        
        'Update Customer names with imported data set
        strSql = "SELECT Customer FROM tblFC2 WHERE Customer IS NULL"
        Set rsFC2 = db.OpenRecordset(strSql)
            With rsFC2
                If Not .BOF Then
                    Do While Not .EOF
                    .Edit
                    !Customer = strCustomer
                    .Update
                    .MoveNext
                    Loop
                End If
            End With
        Set rsFC2 = Nothing
                    
        'Empty tblTemp
        DoCmd.SetWarnings False
        strSql = "DELETE * FROM tblTemp"
        DoCmd.RunSQL strSql
        DoCmd.SetWarnings True
        
        'Delete CSV from p_strCopyLocation
        If FileExists(strCopiedFile) Then
            SetAttr strCopiedFile, vbNormal
            Kill strCopiedFile
        End If
        
        'Next customer/file
        .MoveNext
        Loop
    End If
End With
Set rsCustomer = Nothing
End Sub

This is more efficient that opening/saving as then importing as I was doing earlier!

p_strCopyLocation is just a temp area I am using to store each file.

Does this method look suitable?

Thanks for your suggestions!