Inserting data from text file into database

[Dan]

Is it possible to write a script in an ASP page that reads a comma delimited text file and inserts the data row by row into an Access db? Each comma delimit in the text file represents one field in the db table.

[Bob Barrows [MVP]]

Dan wrote:

> Is it possible to write a script in an ASP page that reads a comma
> delimited text file and inserts the data row by row into an Access
> db? Each comma delimit in the text file represents one field in the
> db table.

Yes, but it's easier to use Access' built-in import functionality to do
this.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bullschmidt]

<<
Is it possible to write a script in an ASP page that reads a comma
delimited text file and inserts the data row by row into an Access db?
Each comma delimit in the text file represents one field in the db
table.

>>

You can connect to a csv file (which can be opened in Excel and which an
Excel file can be converted into) in good form just as you can to a
regular database.

The .csv file needs to be uploaded to the server.

And you can have two recordsets open at the same time.

So I'd suggest going through this recordset one row at a time and within
this loop add a new record to the "real" database's recordset.

And for help connecting to a text file using the Jet OLE DB provider:
[url]http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm#O[/url]
LEDBProviderForMicrosoftJetText

And based on the above link realize that the actual filename does NOT go
in the connection string - rather it goes in the SQL statement
(definitely a little tricky).

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
[url]http://www.Bullschmidt.com[/url]
Classic ASP Design Tips, ASP Web Database Demo, ASP Bar Chart Tool...


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

[Jamie Collins]

Bullschmidt wrote ...

> > Is it possible to write a script in an ASP page that reads a comma
> > delimited text file and inserts the data row by row into an Access db?

> You can connect to a csv file (which can be opened in Excel and which an
> Excel file can be converted into) in good form just as you can to a
> regular database.
>
> The .csv file needs to be uploaded to the server.
>
> And you can have two recordsets open at the same time.
>
> So I'd suggest going through this recordset one row at a time and within
> this loop add a new record to the "real" database's recordset.

Both a .csv file and a Jet database can be queried using the MS OLE DB
Provider for Jet. Therefore, recordsets and looping should be
avoidable and instead use an INSERT INTO..SELECT query to update the
table in one hit e.g. while connected to the Jet DB:

INSERT INTO
MyJetTable
(MyCol1, MyCol2)
SELECT
MyCol1, MyCol2
FROM
[Text;Database=C:\MyFolder\;].[MyFile#csv]
;

Some fine tuning (column names, data types, etc) can be done on the
csv file using a schema.ini file:

[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/office97/html/workingwithtextfiles.asp[/url]

Jamie.

--