UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> SQL On Csv File In Xl VBA, Office 2010    
 
   
PaulBrand
post Mar 20 2017, 05:40 AM
Post#1



Posts: 1,631
Joined: 4-September 02
From: Oxford UK


Hi All,

I hope you can help.

I'm running the following code, but keep getting error syntax error in WITH OWNERACCESS OPTION declaration - I'm not running SQL against a database it's a CSV file.

CODE
Public Function getData(fileName As String) As ADODB.Recordset


    On Error GoTo getData_Error

    Dim path As String
    path = "C:\Perforce\SolvencyII\GRS\Mainline\RA\Excel\ARD_Tools\WARF_Indigo\Output\"
    Dim CN As ADODB.Connection
    Dim RS As ADODB.Recordset
    Set CN = New ADODB.Connection
    Set RS = New ADODB.Recordset
    CN.Open ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & path & ";" & _
                   "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""")
    RS.ActiveConnection = CN
    RS.Source = "select * from " & fileName & ""
    Set getData = RS


    On Error GoTo 0
    Exit Function

getData_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure getData of Function ExcelSQL"

End Function

Sub GetCSV()

    On Error GoTo GetCSV_Error

    Dim a As ADODB.Recordset
    Set a = getData("MyCSVFile.csv")
        a.Open
            MsgBox (a.GetString())
        a.Close
    
    
        On Error GoTo 0
        Exit Sub

GetCSV_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetCSV of Sub ExcelSQL"

End Sub

--------------------
Paul
Go to the top of the page
 
cheekybuddha
post Mar 20 2017, 07:12 AM
Post#2


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


I see you're using the code from here on StackOverflow

Where are you trying to use WITH OWNERACCESS OPTION ? And where does it fail (error numbers/descriptions etc)

d

--------------------


Regards,

David Marten
Go to the top of the page
 
PaulBrand
post Mar 20 2017, 08:54 AM
Post#3



Posts: 1,631
Joined: 4-September 02
From: Oxford UK


I shouldn't need to use WITH OWNERACCESS OPTION should I? But if I do it's between the quotes after fileName

Error in attachment:
Attached File(s)
Attached File  Capture.PNG ( 19.79K )Number of downloads: 0
 

--------------------
Paul
Go to the top of the page
 
cheekybuddha
post Mar 20 2017, 09:52 AM
Post#4


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Why are you trying to use WITH OWNERACCESS OPTION at all ?

AFAICR, it's related to access workgroup security (which is only available with .mdb/.mdw files, and has now been deprecated.)

You're trying to use it with Excel linking to a .csv file?

--------------------


Regards,

David Marten
Go to the top of the page
 
PaulBrand
post Mar 20 2017, 11:05 AM
Post#5



Posts: 1,631
Joined: 4-September 02
From: Oxford UK


I'm not - when I run the code it throws the error with or without the declaration :-(

--------------------
Paul
Go to the top of the page
 
cheekybuddha
post Mar 20 2017, 12:06 PM
Post#6


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


I don't get a problem with your code.

Let me check your environment.
Which version of Excel are you using?
Which version of ADO?
How large is the .csv file?

Try running this equivalent function - paste it in your ThisWorkbook module:
CODE
Function GetCSVAsString(strFile As String, strPath As String) As Variant
10    On Error GoTo Err_GetCSVAsString

        Dim strCn As String
        Dim cn As ADODB.Connection
        Dim ret As Variant

20      Set cn = New ADODB.Connection
30      strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & strPath & ";" & _
                "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;"""
40      cn.Open strCn
50      With cn.Execute("SELECT * FROM " & strFile & ";", , adCmdText)
60        ret = .GetString
70        .Close
80      End With
90      GetCSVAsString = ret

Exit_GetCSVAsString:
100     Exit Function

Err_GetCSVAsString:
110     Select Case Err.Number
        Case Else
120       MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
                 "Description: " & Err.Description & vbNewLine & vbNewLine & _
                 "Function: GetCSVAsString" & vbNewLine & _
                 IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
                 "Module: ThisWorkbook", , "Error: " & Err.Number
130     End Select
140     Resume Exit_GetCSVAsString
        
End Function


Call it from the Immediate Window (Ctrl+G) like:
CODE
?thisworkbook.GetCSVAsString("MyCSVFile.csv", "C:\Perforce\SolvencyII\GRS\Mainline\RA\Excel\ARD_Tools\WARF_Indigo\Output\")


d

--------------------


Regards,

David Marten
Go to the top of the page
 
PaulBrand
post Mar 21 2017, 05:22 AM
Post#7



Posts: 1,631
Joined: 4-September 02
From: Oxford UK


Excel v 14, ADO 2.8, approx. 2mb

Your function works for me, but I need to use it as a recordset and CopyFromRecordset into my app?

Thanks for your help!!! notworthy.gif

--------------------
Paul
Go to the top of the page
 
JonSmith
post Mar 21 2017, 05:48 AM
Post#8



Posts: 3,158
Joined: 19-October 10



I incorporated the copytorecordset into a single procedure. This was so I could clean up the connection properly afterwards. If you have a function return a recordset the connection and recordset variables have to be left open and cannot be closed explicitly.

I have indicated a preference to use a Schema.ini file rather list the details in the connection string. Its a more robust solution for many many reasons so I recommend doing that but if you want the other connection string I've left that commented in so you can switch.


CODE
Sub CSVToRange(strFilename As String, strFolderPath As String, rngTarget As Excel.Range)

    Dim rsCon As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    'rsCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" & strFolderPath & ";" & _
     "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;"""

    'It is recommended to use a Schema.ini file to get the details of the header _
     and delimiter and data types etc rather than specifying in the connection string _
     If you want to do it in the connection string try the commented version above.

    rsCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & strFolderPath & ";" & _
        "Extended Properties=""text"""
    rst.Open "SELECT * FROM [" & strFilename & "];", rsCon, adOpenStatic, adLockReadOnly
    
    rngTarget.CopyFromRecordset rst
    rst.Close: Set rst = Nothing
    rsCon.Close: Set rsCon = Nothing

End Sub

Sub test()
    CSVToRange "Test.csv", "C:\Temp\", ActiveCell
End Sub
Go to the top of the page
 
cheekybuddha
post Mar 21 2017, 06:08 AM
Post#9


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Hi Paul,

You can adjust the function like this:
CODE
Function CSVToExcelViaADO(strFile As String, _
                          strPath As String, _
                          strSheet As String, _
                          Optional iRowStart As Integer = 1, _
                          Optional iColStart As Integer = 1, _
                          Optional blHeaderRow As Boolean = True) As Boolean
On Error GoTo Err_CSVToExcelViaADO

  Dim strCn As String
  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim i As Integer

  Set cn = New ADODB.Connection
  strCn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & strPath & ";" & _
          "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;"""
  cn.Open strCn
  Set rs = cn.Execute("SELECT * FROM " & strFile & ";", , adCmdText)
  With Me.Worksheets(strSheet)
    If blHeaderRow Then
      For i = 0 To rs.Fields.Count - 1
        .Cells(iRowStart, iColStart + i).Value = rs.Fields(i).Name
      Next
    End If
    .Cells(iRowStart - blHeaderRow, iColStart).CopyFromRecordset rs
  End With
  rs.Close

Exit_CSVToExcelViaADO:
  If Not rs Is Nothing Then
    If rs.State = adStateOpen Then rs.Close
    Set rs = Nothing
  End If
  If Not cn Is Nothing Then
    If cn.State = adStateOpen Then cn.Close
    Set cn = Nothing
  End If
  CSVToExcelViaADO = (Err = 0)
  Exit Function

Err_CSVToExcelViaADO:
  Select Case Err.Number
  Case Else
    MsgBox "Error No.: " & Err.Number & vbNewLine & vbNewLine & _
           "Description: " & Err.Description & vbNewLine & vbNewLine & _
           "Function: CSVToExcelViaADO" & vbNewLine & _
           IIf(Erl, "Line No: " & Erl & vbNewLine, "") & _
           "Module: ThisWorkbook", , "Error: " & Err.Number
  End Select
  Resume Exit_CSVToExcelViaADO
  
End Function


You pass in the .csv filename, its location, the sheet to copy to, and optionally the row and column of the cell to start the copy at, and whether you want a header row copied.

However, wouldn't it be easier simply to open the .csv file in Excel and SaveAs .xlsm?

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Mar 21 2017, 06:10 AM
Post#10


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Ah well, Jon beat me to it while I was typing! cheers.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
JonSmith
post Mar 21 2017, 06:17 AM
Post#11



Posts: 3,158
Joined: 19-October 10



I would say David's code is abit more thorough than mine in its error handling etc and we open our recordsets in different ways, I am not sure if either method has an advantage over the other in this context.
David has included headers if desired which is nice. David I do think you should edit to add [ ] around the Filename in the SQL to prevent issues with spaces in the name?

Apart from my one bit of feedback I think David has a more comprehensive function so I would recommend his over my lean solution.

I still advocate the Schema.ini file over the "Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;""" bit. Just change your regional settings to Dutch for example and this code will fail whereas with a Schema.ini and the connection string in my code it will work anywhere. Combine both our ideas and you'll have a nice function.
Go to the top of the page
 
cheekybuddha
post Mar 21 2017, 06:25 AM
Post#12


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Definitely agree about the square brackets around the filename! blush.gif

With ADO, if I am just opening a forward-only read-only recordset then I always use the cn.Execute method - lazy and simple one-liner. And if you don't need to pass the object you can use a With .. End With block and avoid having to declare a recordset object variable at all!

The only thing about the schema.ini is that it is a separate file that requires 1. maintenance, and 2. being present!. Sometimes, it's easier to keep all the options in one place.

Also, the documentation on them is woefully sparse. But I take your point about their being more universal.

:thumbup;

d

--------------------


Regards,

David Marten
Go to the top of the page
 
JonSmith
post Mar 21 2017, 06:30 AM
Post#13



Posts: 3,158
Joined: 19-October 10



QUOTE
The only thing about the schema.ini is that it is a separate file that requires 1. maintenance, and 2. being present!. Sometimes, it's easier to keep all the options in one place.


Very true but you do have alot more flexibility.
If you have a CSV with ; as the delimiters its fine. If you don't have a header, its fine. If you have dates in an odd format, its fine. If you data types that are being read wrong (text as number for example) its fine.

When you specify the details in the connection in your function then you are suddenly very limited to only being able to deal with CSV's with the system list separator, with a header and hoping they guess the data type correctly.
I say its well worth the maintenance for the added reliability you get.

Fair point about the lack of documentation but thats why I am also promoting them so people are more aware!!!


BTW, still not seeing the advantage of the .execute method, you still need a recordset variable and mine is one line too!? It is also opened forward only and read only?

JS
Go to the top of the page
 
cheekybuddha
post Mar 21 2017, 06:40 AM
Post#14


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Jon,

I, for one, would be most appreciative if you were able to write up a comprehensive reference on how to use Schema.ini with text files and put it in the wiki, or just in a general info post.

Things like how to specify the different options for delimiters, formatting, where do you place the file etc, etc.

I would happily promote them if I know how to use them confidently.

Sadly, I always find that the time to research and work it out when I want a schema.ini takes longer than it is worth to spend on what I am doing.

... only if you have time!

thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Mar 21 2017, 06:59 AM
Post#15


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


>> BTW, still not seeing the advantage of the .execute method <<

Yes, in this particular instance, agreed.

But I was just adapting from the code in my previous function in Post#6 that didn't require the recordset variable.

It's all a bit academic now, since ADO is virtually obsolete, but I would quite often have code like:
CODE
Function AddUserToGroup(strFirst As String, strLast As String, lGroupID As Long) As Boolean

  Dim strSQL As String, lConID As Long, lAffected As Long

  With CurrentProject.Connection
    strSQL = "SELECT conID FROM tblCon WHERE conFirst = '" & strFirst & "' AND conLast = '" & strLast & "';"
    With .Execute(strSQL, , adCmdText)
      If Not (.BOF And .EOF) Then lConID = .Collect(0)
      .Close
    End With
    If lConID = 0 Then
      strSQL = "INSERT INTO tblCon (conFirst, conLast) VALUES ('" & strFirst & "', '" & strLast & "');"
      .Execute strSQL, , adCmdText + adExecuteNoRecords
      strSQL = "SELECT @@IDENTITY;"
      With .Execute(strSQL, , adCmdText)
        If Not (.BOF And .EOF) Then lConID = .Collect(0)
        .Close
      End With
    End If
    strSQL = "INSERT INTO tblConGroup (ConID, GroupID) VALUES (" & lConID & ", " & lGroupID & ");"
    .Execute strSQL, lAffected, adCmdText + adExecuteNoRecords
    AddUserToGroup = (lAffected > 0)
  End With

End Function

Above is just a made up example, but you get the idea.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 09:19 PM