Import csv file into one record 1

[Joallyn]

Hi all,

I'm facing meltdown in the face of what is most likely a very simple operation.

I set up a command button on my form to call an external application, which outputs a *.csv file. The file consists of a number of rows and columns of test data results.

There are 2 things I'd like to do with those results:

1. I'd like the file to show up in a text box, so that the user could see what the program threw out, and

2. I want to import the results into a "test results" table. Ideally, the entire file would be one record (defined as memo format). However, when I try to set up the import spec, Access chunks up the data into different fields in the table. I've tried importing in both delimited and non-delimited format, and each time I wind up with data spread over rows and columns. How do I import the whole thing as one element? Or is there a much more elegant way to store a text file in Access?

thanks in advance!

J

 

[RoyVidar]

To import it, I'd look into the filesystemobject, for instance something like this:

[tt]dim rs as dao.recordset
dim fs as scripting.filesystemobject
dim txt as scripting.textstream
dim sInString as string

set fs=new scripting.filesystemobject
set txt=fs.opentextfile("c:\path\name.csv",ForReading)
sInString=txt.readall
txt.close
set txt=nothing
set fs=nothing
set rs=currentdb.openrecordset("MemoTable",dbopentable)
rs.addnew
rs.fields("YourMemo").value=sInString
rs.update
rs.close
set rs=nothing[/tt]

- typed not tested, would require referencec to Microsoft Scripting Runtime and Microsoft DAO 3.# Object Library (in VBE - Tools | References)

Or instead of using the recordset, you could move to a new record, and dump the sInString variable to the memo text control.

Roy-Vidar

 

[Craig0201]

Joallyn,

Why save it in the database? If you make each filename unique, you can save the reference to the file name. This is much more efficient on space.

Craig

 

[Joallyn]

Thanks for the replies!

Unfortunately, I'm too much of a newbie to effective use of either one.

RoyVidar, after combing through the TT site for more examples of filesystemobject use, I still can't figure out where to put the code. On Enter? Focus???? Even the microsoft filesystemobject tutorial page (

http://msdn.microsoft.com/library/d...s6/vbasic/vbenlr98/vaobjFileSystemObject.htm)

doesn't get that specific. So...sorry to ask you to break it down even more simply, but where should I insert this?

Craig0201, I'm going for the brute-force method of database design because it seemed simpler: load the data into the table, end of problem. Of course, nothing ever turns out to be quite that easy...

thanks again,

J

 

[RoyVidar]

You would have to determine which event to use, that fits your current challenge. But since you ask, you're alredy stating you're using a click event of a button to invoke the other application producing the csv file, why not place this within the same event? Or another button, or...

Roy-Vidar

 

[cgarts]

Googling "FileSystemObject read file txt" yields sites with clear code samples:

http://www.4guysfromrolla.com/webtech/faq/FileSystemObject/faq2.shtml

http://www.juicystudio.com/tutorial/asp/files.asp

Check out the ReadAll command.

Best,

C

 

[Joallyn]

OK! It works--not elegantly, but for now that's just fine. I'll tinker with the details and hopefully it will improve somewhat.

Thanks again for the helpful advice--

J