Do CSV (comma delimited text files) have a sheet name? Do I need to specify a range for data to copy?
My SQL skills are weak.
I am trying to get all the text from my export.csv file into a worksheet where the "Price" column is not empty and sort it by "sku".
I can open the file and build the SQL string but there must be an error?
The debugger shows the string as:
"SELECT * FROM [export$] WHERE price IS NOT NULL ORDER BY sku;"
When I try and open the Connection and Recordset the debugger shoes they both contain the value of "".
Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")
When I get to using them I go straight to my error trap.
rsCon.Open szConnect
rsData.Open szSQL, rsCon, 0, 1, 1
I am calling the subroutine with the following data:
GetData "export.csv", "export", "A:AH", "BirdFeet", "B1", "sku", True, True
Here is the complete subroutine.
Public Sub GetData(SourceFile As Variant, SourceSheet As String, SourceRange As String, _
TargetSheet As String, TargetRange As String, _
TargetSortColumn As String, _
HaveHeader As Boolean, UseHeaderRow As Boolean)
Dim lColumn As Long
Dim lCount As Long
Dim lRow As Long
Dim rsCon As Object
Dim rsData As Object
Dim szConnect As String
Dim szSQL As String
lRow = Range(TargetRange).Row
lColumn = Range(TargetRange).Column
' Create the connection string.
If HaveHeader = False Then 'No there is NOT a header row.
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=No"";"
End If
Else 'Yes there is a Header Row
If Val(Application.Version) < 12 Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
Else
If (Right(SourceFile, 4) = ".csv") Then
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""text; HDR=Yes; FMT=Delimited; IMEX=1;"""
Else
szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 12.0;HDR=Yes"";"
End If
End If
End If
If SourceSheet = "" Then 'Create query strings
szSQL = "SELECT * FROM " & SourceRange$ & " ORDER BY sku;"
ElseIf SourceSheet = "DiamondAvian" Or SourceSheet = "export" Then
szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & "] WHERE price IS NOT NULL ORDER BY " & TargetSortColumn & ";" 'Drops all rows with no Price
Else
szSQL = "SELECT * FROM [" & SourceSheet$ & "$" & SourceRange$ & "] WHERE sku IS NOT NULL ORDER BY " & TargetSortColumn & ";" 'THIS WORKS FOR DICIONARY
End If
On Error GoTo SomethingWrong
Set rsCon = CreateObject("ADODB.Connection")
Set rsData = CreateObject("ADODB.Recordset")
rsCon.Open szConnect
rsData.Open szSQL, rsCon, 0, 1, 1
' Check to make sure we received data and copy the data
If Not rsData.EOF Then
If HaveHeader = False Then
Cells(1, 1).CopyFromRecordset rsData
Else
'Add the header cell in each column if the last argument is True
If UseHeaderRow Then
For lCount = 0 To rsData.Fields.Count - 1 'Builds the Header row one column at a time.
Cells(lRow, lColumn + lCount).value = rsData.Fields(lCount).Name 'lcount determines the Column to paste header info in.
Next lCount
Cells(lRow + 1, lColumn).CopyFromRecordset rsData 'This is the step that copies and Pastes the data.
Else
Cells(lRow + 1, lColumn).CopyFromRecordset rsData
End If
End If
Else
MsgBox "No records returned from : " & SourceFile, vbCritical
End If
rsData.Close ' Clean up our Recordset object.
Set rsData = Nothing
rsCon.Close
Set rsCon = Nothing
Exit Sub
SomethingWrong:
MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, vbExclamation, "Error"
On Error GoTo 0
End Sub
Sample data from export.csv
sku price post_date post_date_gmt
B1 - M 4/19/2015 11:15 4/19/2015 15:15
B8 8.06 4/19/2015 11:11 4/19/2015 15:11
B1 10.79 4/19/2015 11:08 4/19/2015 15:08
B2 11.65 4/19/2015 11:08 4/19/2015 15:08
B3 11.98 4/19/2015 11:08 4/19/2015 15:08
B3B 12.74 4/19/2015 11:08 4/19/2015 15:08
B4 16.24 4/19/2015 11:08 4/19/2015 15:08
SB 770 4/3/2015 12:37 4/3/2015 16:37
I was not using the path to the directory that holds the csv file in the szConnect string. I was using the file name not the path.
I added a string "strWorkingDir" and set it equal to the directory of the files and now It works fine.
https://msdn.microsoft.com/en-us/library/ms974559.aspx
CraigM