Question about retrieving CSV-file

  • Thread starter Thread starter Mika M
  • Start date Start date
M

Mika M

I'm retrieving CSV-file like this way...

Dim strFilePath As String = MyCSVSourceFilePath

Dim strConnStr As String = String.Format( _
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended
Properties=Text;", _
Path.GetDirectoryName(strFilePath))

Dim strSQL As String = String.Format("SELECT * FROM {0}",
Path.GetFileName(strFilePath))

Dim da As OleDbDataAdapter = New OleDbDataAdapter(strSQL, strConnStr)
Dim dt As DataTable = New DataTable
da.Fill(dt)
....rest of code continues here...

....and it's working fine, but is it possible to use with this sql-query
something like "WHERE MyFirstField LIKE 'ABC%'" when csv-file has no
column names as first line, only data rows?

Propably not. This would make it easier to get only those data rows I
really need, and avoid to check each row separately like...

For Each dr As DataRow In dt.Rows
If (dr(0).ToString.StartsWith("ABC")) Then
'... doing something here...
End If
Next

....not much code lines saved, but good to know! :)
 
Hi Mika,

Yes you can. If you apply the property HDR=NO in the extended properties in
the connection string the Jet provider will name the columns for you (e.g. F1
for field one, F2 for field two, etc...). So a query like

SELECT F1, F3 FROM [Sheet1$]

will return you all of the the first and the third fields in the document.

I have posted a link to an article on the MSDN that discusses this.

I hope this helps.
 
¤ I'm retrieving CSV-file like this way...
¤
¤ Dim strFilePath As String = MyCSVSourceFilePath
¤
¤ Dim strConnStr As String = String.Format( _
¤ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended
¤ Properties=Text;", _
¤ Path.GetDirectoryName(strFilePath))
¤
¤ Dim strSQL As String = String.Format("SELECT * FROM {0}",
¤ Path.GetFileName(strFilePath))
¤
¤ Dim da As OleDbDataAdapter = New OleDbDataAdapter(strSQL, strConnStr)
¤ Dim dt As DataTable = New DataTable
¤ da.Fill(dt)
¤ ...rest of code continues here...
¤
¤ ...and it's working fine, but is it possible to use with this sql-query
¤ something like "WHERE MyFirstField LIKE 'ABC%'" when csv-file has no
¤ column names as first line, only data rows?
¤
¤ Propably not. This would make it easier to get only those data rows I
¤ really need, and avoid to check each row separately like...
¤
¤ For Each dr As DataRow In dt.Rows
¤ If (dr(0).ToString.StartsWith("ABC")) Then
¤ '... doing something here...
¤ End If
¤ Next
¤
¤ ...not much code lines saved, but good to know! :)

Yes you can define column names for your text file (if the first row is not used for this purpose)
by using a schema.ini file:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp

Otherwise, the default column names are F1, F2, F3, etc.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top