URGENT: Connecting to a CSV file and SQL SELECT? 
Author Message
 URGENT: Connecting to a CSV file and SQL SELECT?

Hi all

I wonder if someone would be kind enough to help me out.

For a website, using ASP pages I have managed to put together a DSN-less
connection to an Access DB and am able to create a recordset and display
records. However, I've just been told that the database I'm using is now
going to be supplied as a CSV text file.

Although looking a various sources (ASP101.com, 4Guys....com etc) try as I
might, I cannot find enough pieces of understandable code in order to
connect to the CSV and use SQL to pull the data out. It's an emergency, so
would someone kindly drop me a reply or email with an working example of how
I might make a connection and the correct SQL string needed to pull the data
out.

Many thanks for any help offered,

Steve Britton



Tue, 26 Oct 2004 14:57:58 GMT  
 URGENT: Connecting to a CSV file and SQL SELECT?
Hi,

To connect to CSV file you need to use next kind of
connection string

lcConnectionString
= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Path to
file name, without file name itself;Extended
Properties=""TEXT;DriverId=27;FIL=text;MaxBufferSize=2048;P
ageTimeout=15;"""

Now you can read from CSV file file using SQL statements,
but you should rememeber, that you can work with text file
only in read-only mode. You cannot insert anything into
file using ADO.

loRecordset.Open "SELECT * FROM FileName.CSV", , , ,
adCmdText

That statement will read each line of your file as a one
field. In case if you need to break your line into fileds
you need to setup Schema.ini file in same folder where
your CSV file is. you can do it using ODBC Data Source
from Control Panel.

Val
Microsoft MVP

Quote:
>-----Original Message-----
>Hi all

>I wonder if someone would be kind enough to help me out.

>For a website, using ASP pages I have managed to put
together a DSN-less
>connection to an Access DB and am able to create a

recordset and display
Quote:
>records. However, I've just been told that the database
I'm using is now
>going to be supplied as a CSV text file.

>Although looking a various sources (ASP101.com,

4Guys....com etc) try as I
Quote:
>might, I cannot find enough pieces of understandable code
in order to
>connect to the CSV and use SQL to pull the data out. It's
an emergency, so
>would someone kindly drop me a reply or email with an

working example of how
Quote:
>I might make a connection and the correct SQL string

needed to pull the data
Quote:
>out.

>Many thanks for any help offered,

>Steve Britton

>.



Tue, 26 Oct 2004 19:42:32 GMT  
 URGENT: Connecting to a CSV file and SQL SELECT?
On Fri, 10 May 2002 07:57:58 +0100, "Steve Britton"

Hi all

I wonder if someone would be kind enough to help me out.

For a website, using ASP pages I have managed to put together a DSN-less
connection to an Access DB and am able to create a recordset and display
records. However, I've just been told that the database I'm using is now
going to be supplied as a CSV text file.

Although looking a various sources (ASP101.com, 4Guys....com etc) try as I
might, I cannot find enough pieces of understandable code in order to
connect to the CSV and use SQL to pull the data out. It's an emergency, so
would someone kindly drop me a reply or email with an working example of how
I might make a connection and the correct SQL string needed to pull the data
out.

Many thanks for any help offered,

Code similar to what Val posted plus a link to Schema.ini info. You can also
include some of the schema info in your connection string (such whether to
assume the first line in the text file contains column headings):

Sub OpenTextWithADO()

Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & "D:\My Documents\TextFiles;" & _
          "Extended Properties=Text;HDR=No;"

rs.Open "Select * from People#txt", cnn, adOpenStatic, adLockReadOnly, adCmdText

'...

rs.Close
Set rs = Nothing

cnn.Close
Set cnn = Nothing

End Sub

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

Microsoft did restore ISAM updatabilty (without requiring the Borland Database
Engine) in a Jet service pack although I haven't checked this out for Text
files.


Microsoft MVP (Visual Basic)



Tue, 26 Oct 2004 21:02:11 GMT  
 URGENT: Connecting to a CSV file and SQL SELECT?
Paul

You too are a lifesaver! Between the code you've pasted
and Val's I've managed to get up and running with this...
needless to say, deadline has been met!

Many thanks and an owed beer,

Steve britton

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Tue, 26 Oct 2004 22:27:59 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Connecting to a CSV file using ADO.NET

2. connecting to a CSV text file via VB

3. ADO to connect to CSV file

4. urgent: importing and exporting csv files

5. Import CSV file - URGENT

6. Urgent : VB6 ADO Connecting to SQL 6.5

7. URGENT ! SQL Server connect and Refresh

8. Automating an import from a CSV file to a SQL 7.0 database

9. HELP: Importing CSV file into SQL Server DB

10. Updating remote SQL db with CSV file

11. SQL; Select Into Xl-File From XL-File

12. Using Vbscript and SQL-DMO to connect to SQL Server and run a T-SQL script

 

 
Powered by phpBB® Forum Software