VBA Import Code Problem

[jhrBanker]

Following is the Vonage statement file to be imported. Note that the 2nd field is named "null" for some reason. My reference to "null" in the code (If strArray(1) <> "null" Then) is simply to exclude the Header row when importing the records.

YY-MMDD.csv Downloaded File


Date/Time,null,Number,Length,Cost
12/20/2012 12:11 PM,Forwarded,BANK OF AMERICA (949) 994-7496,00:32,--
12/20/2012 09:03 AM,Forwarded,BANK OF AMERICA (949) 994-7496,00:03,--
12/14/2012 02:34 PM,Outgoing,(781) 975-7822,01:43,--
12/14/2012 11:10 AM,Incoming,TOLL FREE CALL (866) 294-1503,00:09,--
12/14/2012 10:15 AM,Outgoing,(708) 695-6619,00:14,--
12/12/2012 11:41 AM,Incoming,TOLL FREE CALL (866) 854-3732,00:04,--

[ItsMe]

I count 4 commas. That means you have 5 columns. Null is the header for the transaction type, eg Forwarded.

I would not include
(If strArray(1) <> "null" Then)

Also, not sure what the integer 4 is doing in your Split function. I mean, I do not know what it represents. According to help files, there are two options 0 and 1 for Binary and Textual respectively. Do not know if it is an issue. I could not find a list of constants or even a help file specific to VBA and Access.

[jhrBanker]

Correct, the file has 5 columns. However, the 5th column is always "--", and not needed. The Integer 4 in the Split Function limits it to 4 columns.
Split(expression [, delimiter ] [, limit ] [, compare ] )
The Split function syntax has these arguments:
ARGUMENT DESCRIPTION
expression Required. String expression containing substrings and delimiters. If expression is a zero-length string(""), Split returns an empty array, that is, an array with no elements and no data.
delimiter Optional. String character used to identify substring limits. If omitted, the space character (" ") is assumed to be the delimiter. If delimiter is a zero-length string, a single-element array containing
the entire expression string is returned.
I've removed the Integer 4, and it makes no difference.

When I remove (If strArray(1) <> "null" Then), only the Header row is imported. None of the actual records are imported.


limit Optional. Number of substrings to be returned; –1 indicates that all substrings are returned.
compare Optional. Numeric value indicating the kind of comparison to use when evaluating substrings. See Settings section for values.

[ItsMe]

I got it to work. I just need to fix the loop. It is not recognizing the file name. Give me a few.

[ItsMe]

THis is working. Once I got it into a VBA module intelisense told me that there are three arguments for Switch. So the integer 4 is counting the array.

Private Sub Command0_Click()

'DoCmd.Hourglass True
' CurrentDb.Execute "DELETE * FROM VonageStmt;"
' lblVonageStmt.Visible = False
' txtVonageStmt = ""
 Dim rs1 As DAO.Recordset
 Dim FileName As String
 Dim FileDate As Date
 Dim FileNum As Integer
 Dim strFilter As String
 Dim strFileDesc As String
 Dim strFileExt As String
 Dim strRecord As String
 Dim strRecordArray() As String
 Dim intCount As Integer
 Dim InputString
 
FileName = "C:\Test\SampleImport.csv"
' FileName = ahtCommonFileOpenSave( _
' InitialDir:="C:\Users\jhrAcer\Downloads\Vonage", _
' Filter:=strFilter, _
' OpenFile:=True, _
' DialogTitle:="Select File for Import... *.csv", _
' Flags:=ahtOFN_HIDEREADONLY)
' FileDate = FileDateTime(FileName)
' FileNum = FreeFile()
 Set rs1 = CurrentDb.OpenRecordset("VonageStmt")
' DoCmd.RepaintObject acForm, "FrmMainMenu"
' DoCmd.Hourglass True
'Me.Command0.HyperlinkAddress = FileName
Dim intFile As Integer
intFile = FreeFile()
Dim strMyChange As String
' Open FileName For Input As #FileNum
 Open FileName For Input As intFile
 
 
' On Error Resume Next
Do Until EOF(intFile)

 Line Input #intFile, InputString
 strRecordArray = Split(InputString, ",", 4)
 If strRecordArray(1) <> "null" Then
 rs1.AddNew
 rs1.Fields("DtTm") = strRecordArray(0)
 rs1.Fields("Type") = strRecordArray(1)
 rs1.Fields("Number") = strRecordArray(2)
 
strMyChange = Left(strRecordArray(3), InStr(strRecordArray(3), ",") - 1)
 rs1.Fields("Length") = strMyChange
 
 RecCnt = RecCnt + 1
 rs1.Update
 End If
Loop
 rs1.Close
 Set rs1 = Nothing
 Close #intFile
' DoCmd.Hourglass False
' lblVonageStmt.Visible = True
' txtVonageStmt = RecCnt
 
End Sub

[jhrBanker]

It's working!!!!
Many, many thanks!

[ItsMe]

Sure. I would probably use another option like Paul mentioned eg the Import Wizard or something else but, sometimes parsing data before the DB sees it is best.