Querying CSV file
I was wondering if I could be given some guidance on how to query some CSV files. I have an online store and have about 4 or 5 suppliers and our store hold about 400 products. I receive stock information daily but it's practically impossible to manually check the contents of the CSV files to strip out stock level, price changes since the last run. In a nutshell, I would like to be able to run a code that will check my supplier's CSV files which will return stock availability, price change since the last run. These 2 information should show in the columns within the worksheet.
Querying CSV file
hey..
Try to use the below code.
Private Function ReadDataFromWorkbook(sourceFilePath As String, sourceFileName As String) As Variant
Dim dbConnection As ADODB.Connection, rs As ADODB.Recordset
Dim dbConnectionString As String
dbConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
sourceFilePath & ";Extended Properties=""CSV;HDR=Yes;FMT=Delimited"""
Set dbConnection = New ADODB.Connection
On Error GoTo InvalidInput
dbConnection.Open dbConnectionString ' open the database connection
Set rs = New ADODB.Recordset
strSQL = "SELECT * FROM " & sourceFileName
rs.Open strSQL, dbConnection, 3, 3
ReadDataFromWorkbook = rs.GetRows
rs.Close
dbConnection.Close ' close the database connection
Set rs = Nothing
Set dbConnection = Nothing
On Error GoTo 0
Exit Function
InvalidInput:
MsgBox "The source file or source range is invalid!", vbExclamation, "Get data from closed workbook"
Set rs = Nothing
Set dbConnection = Nothing
End Function
Ajeesh.
Hi Ajeesh, thanks very much
Hi Ajeesh, thanks very much for the code. Where can i run it - excel or access?
hi Tony, we are taking a look
hi Tony, we are taking a look and will get back to you