csv to Array

[hascons]

Hello

I'm trying to upload a csv file into an Array then add records to a table.
I have the following code which gets the information from a csv file which works fine.

Open filePath For Input As #1

Do While Not EOF(1)
Line Input #1, MyData

Problems:
1. When i try to load this into an array , it does not return all the information. It will if a smaller amount of data is sought. For example if only 1 months month of data is requested. Is there a maximum data limit that can be parsed into an array with this method?

2. I'm not quite sure how to parse individual lines (records) to update a table in access. I have provided the code that I'm trying to use to accomplish this below. From code below am I doing this correctly?

3. For each record that is created in the database I would like to add a ticker string to the record for later querying. Can this be done and am I on the right track from the supplied code?

Private Sub ImportData(filePath As String, ticker As String)

'On Error GoTo Errorhandler

Dim arrData() As String
Dim MyData As String
Dim i As Integer
Dim Db As DAO.Database
Dim rst As DAO.Recordset

Set Db = CurrentDb
Set rst = Db.OpenRecordset("Quotes1")

Open filePath For Input As #1

Do While Not EOF(1)
Line Input #1, MyData
arrData = Split(MyData, ",")


For i = 0 To UBound(arrData)
Debug.Print arrData(i)

' Add Data to Database Table.

With rst
.AddNew
!qTicker = ticker
!qDate = CDate(arrData(i))
!qOp = CDbl(arrData(i + 1))
!qHi = CDbl(arrData(i + 2))
!qLo = CDbl(arrData(i + 3))
!qCl = CDbl(arrData(i + 4))
!qVo = CDbl(arrData(i + 5))
.Update
End With

Next i
Loop
Close #1




rst.Close
Set rst = Nothing
Set Db = Nothing

'Getout:
'Exit Sub
'Errorhandler:
'MsgBox "Error #" & Err & ": " & Err.Description
'Resume Getout

End Sub

Any Help would be appreciated

Thanks

 

[Isskint]

You could save yourself a lot of hassle by using the TransferText method. In the example below I import Book2 to NewTable with no headers.

DoCmd.TransferText acImportDelim, , "NewTable", "C:\Users\user\Desktop\Book2.csv", False


Import, validation and manipulation of data is a common feature in Access DBs. The general process would be:

1. Create a table to hold the imported data (usually a copy of the main table the inported data will be added to)
2. create a Delete query to delete the data in the holding table (run this query before importing)
3. create a query to do the manipulation/validation
4. create a query to do the Appending of data
5. Create a macro or VBA procedure to Run the delete query, import the data, run the manipulation/validation query, run the append query

 

[hascons]

Isskint

I have tried this method but always get error message Field "?" doesn't exist in "TempTable". I have tried setting has field names to true but the data gets downloaded with names like (Date) which are reserved for access which also throws up an error.

I understand the methods you describe as part of the data manipulation by creating queries for certain actions. Is there a benefit gained by doing that after the data has been downloaded?

If there were many files to updated regularly would the docmd.transfertext be quicker than importing from an Array?

 

[Isskint]

If there were many files to updated regularly would the docmd.transfertext be quicker than importing from an Array?

I would say yes. The docmd.transfertext is a built in function which generally works faster than written code.

I understand the methods you describe as part of the data manipulation by creating queries for certain actions. Is there a benefit gained by doing that after the data has been downloaded?

Creating the queries or running the queries?? Assuming you mean running the queries, that depends on what needs manipulating/validating. But you can only manipulate the data after downloading into the temp table.
If you meant creating the queries, then generally NO, the queries should already be designed. However there could be situations where you might need to change the manipulation/validation, in which case you could write the QueryDef on the fly.

I have tried this method but always get error message Field "?" doesn't exist in "TempTable". I have tried setting has field names to true but the data gets downloaded with names like (Date) which are reserved for access which also throws up an error.

You need to make sure all fields in CSV file have a corresponding field in the table. Watch out for restricted characters (like # or & or ' etc). These are the bane of our lives. Where you have control over data input into the CSV file, tell people not to use restricted characters and make sure columns/fields have 'useful' names - like InputDate instead of date! Where you do not control over data input into the CSV file, you could include a routine to open the CSV as an Excel file, check the headings & adjust them suitably and check the data for restricted characters. This however will slow down your Import Data routine.

 

[ChrisO]

Hascons.

The data in the Split array is indexed incorrectly.

Equivalent code:-

Private Sub ImportData(filePath As String, ticker As String)
    Dim arrData() As String
    Dim MyData    As String
    
    Open filePath For Input As #1
    
        With CurrentDb.OpenRecordset("Quotes1")
            Do While Not EOF(1)
                Line Input #1, MyData
                arrData = Split(MyData, ",")
                
                .AddNew
                    !qTicker = ticker
                    !qDate = CDate(arrData(0))
                    !qOp = CDbl(arrData(1))
                    !qHi = CDbl(arrData(2))
                    !qLo = CDbl(arrData(3))
                    !qCl = CDbl(arrData(4))
                    !qVo = CDbl(arrData(5))
               .Update
            Loop
        End With
        
    Close #1

End Sub

Chris.

 

[DavidAtWork]

I've experienced this 'field' error and the way to get round it is to have an intermediary step where you first import the data into a temp table where all the field names are Field1, Field2, Field3, ... etc. All fields are set as text fields and in the next step you can use an append query to transform/convert the datatypes and insert into your real table.

David

 

[hascons]

Thanks guys

I was able to accomplish what i needed to with the following code. I have been told elsewhere that this was unecessary and waiting for explanation as to why but here's the Code.

I can't say enough what forums like this and people like you guys are to novices like myself. Thank you so much for the help!!

Public Sub GetFileToArray(ByVal FileName As String)
On Error GoTo ErrorHandler

Dim oFSO As New FileSystemObject
Dim oFSTR As Scripting.TextStream
Dim i As Long
Dim arrData() As String


If Dir(FileName) = "" Then Exit Sub

Set oFSTR = oFSO.OpenTextFile(FileName)

If Not oFSTR.AtEndOfStream Then oFSTR.SkipLine

Do While Not oFSTR.AtEndOfStream

ReDim Preserve arrData(i) As String
arrData(i) = oFSTR.ReadLine
i = i + 1
DoEvents 'optional but with large file
'program will appear to hang
'without it

Loop

oFSTR.Close


ErrorHandler:
Set oFSTR = Nothing

End Sub