CSV file as database

[Ed]

I want to use a table from an Excel worksheet as a data source for a
Web application. figuring that a CSV file would be more likely to work
than XLS, I saved it as CSV and placed it in my project directory.
However, when I try to connect to it via the connection properties
dialog it fails. It seems to want an MDB file, but I don't know what
that is.

I'm working in VB .NET

Any ideas?

TIA

Ed

 

[JB]

Not to get into your original problem but mdb file is an Access database file.

 

[Cor Ligthert[MVP]]

Hi Ed,

Have a look at these

http://www.connectionstrings.com/

http://www.vb-tips.com/CSVDataSet.aspx

I hope this helps,

Cor

 

[Cor Ligthert[MVP]]

Ed,

I forgot, be aware that you cannot connect without by instance Active X to a
whatever database on the clientside

Cor

 

[sloan]

http://sholliday.spaces.live.com/Blog/cns!A68482B9628A842A!176.entry

This sample will show you how to connect to:
Sql Server
JET Database ("access database" is the errant but commonly used name for it,
but its an mdb file).
Excel
Sql Server CE
TextFile.


The sample will work with the non RDBMS (Excel,Jet,Textfile) pretty well as
a READONLY datastore.

Download the code. If you only invoke Excel,Jet,Text, it'll work out of the
box.

 

[Ed]

Cor,

Doesn't work. Here's what I did:

Created a WebApp in Visual Studio .NET 2002.
Added a Webform, WebForm1
Added a Label "Using CSV file as data source"
Added a Datagrid DataGrid1 with 3 columns and 6 rows
Pasted the code given in VB Tips page you referenced into the Code
page of WebForm1
Did a Build. This fails, saying MessageBox is undefined.
Commented out the MessageBox & did a successful Build
Created C:\Test1\Test2.txt with 6 rows of 3 strings separated by
commas
Opened WebFor1.aspx in IE.
The page is displayed, but only the Label "Using CSV file as data
source" is displayed... no DataGrid.

I've never used a DataGrid before, so maybe there's something I don't
understand about that.

Any suggestions?

Ed

 

[Ed]

Fixed it!
To get the dataGrid to show up I had to put :

DataGrid1.DataBind()

after setting its datasource to the ds.Table(0)
Ed

 

[Hal Rosser]

I want to use a table from an Excel worksheet as a data source for a Web
application. figuring that a CSV file would be more likely to work than
XLS, I saved it as CSV and placed it in my project directory. However, when
I try to connect to it via the connection properties dialog it fails. It
seems to want an MDB file, but I don't know what that is.

You may find it easier to treat the csv file like a regular sequential
access text file.
Open the file and read it one line at a time.
Use the split function to Split the 'line String' into values at the commas.
The other way is to import the csv into an Access database and you'll be
able to use SQL to manipulate the data.

 

[Ed Sowell]

Thanks, Hal. Now that's something I understand!
Ed

 

[sloan]

Did you look at my sample?

It has the code to connect to:

TextFile

as well as the others

Excel
JET (Access)

 

[Cor Ligthert[MVP]]

Hal,

Why, because there is nothing easier then to get the csv file direct as a
dataset?

Cor

 

[Ed Sowell]

Seems that I've caused this CSV input thing to drift two a couple different
threads. I wanted to report some progress, and I believe this thread is the
best.

I now have a process that gets my CVS into MySQL without too much fuss.
Here's what worked.

1. Go to http://www.sqldbu.com/eng/sections/tips/mysqlimport.html , which
provides a Web application that converts CSV to MySQL. I just browsed to my
ModelTable.csv on my Windows machine, and then pressed the Start button on
the above page. That created a file ModelTable.txt that has a bunch of SQL
INSERT INTO table commands.
2. Following a tip from Andrew Morton, I asked the tech at my Web hosting
service how to run a MySQL admin console. The tech told me how to go to my
account admin page, go to the MySQL databast tools page, and set myself up
as a MySQL user.
3. That allowed me to create a database with nothing in it. It also gave me
access to phpMyAdmin. which is MySQL console, if I can use that term. It
allowed me to select the empty database. Once the database was created,
there was a command button called SQL, that offered a panel into which SQL
commands could be typed. It also, importantly, offered a "textfile" option
which allows one to point at a text file containing SQL commands. It had a
browse button that, much to my surprise, allowed me to browse on my Windows
machine. After browsing to ModelTabel.csv I clicked the Go button, and
walla, it created a table in my MySQL database with my ModelTable data in
it. Note that I did not have to FTP the ModelTable.csv file to the Web
server.

This is a big step forward. Now, I'm hoping that I can get back to my VB
..NET Web app and come up with a "connection string" that will work on both
my Windows machine and the Web server.

Thanks to Cor, Andrew, and everyone else who has coached me on this. Don't
go away, though!

Ed

 

[Ed Sowell]

Cor,

The term "easy" is always relative to where a person is coming from. For me,
if I had taken Hal's suggestion I would have been finishd with this project
days ago simply because that's what I've been doing with problems of this
nature for years. I knew that database techniques must be better, but
presented me with a learing curve to deal with. Now that I've made some
progress in that direction, I think it will pay off. I reported the progress
in a reply.

Ed