Populate Dataset from a text file located in a ftp server

Hi experts,

I have an application that fills a dataset out of a text file. The text file is first downloaded from a ftp server.

I want to simplifly the application.

I can handle the ftp download easily on vb.net, but I want to know if its possible to allocate a dataset and dump the content of the file into that dataset.

example:

What I currently do is similar to:

get "myftpfile" "mylocalfile"

Once I have the "mylocalfile", I fill a dataset with the tab-delimited information from it.

Instead of phisically having the tab-delimited "mylocalfile" on my pc, I want to directly dump its contents into a dataset to eventually handle the datatable from there like:

get "myftpfile" "mydataset"  

Thanks

LVL 1
aarons34Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

w3timCommented:
If the data is uniform i.e.
where "," = delimiter
data1.0,data2.0,data3.0,data4.0
data1.1,data2.1,data3.1,data4.1
data1.2,data2.2,data3.2,data4.2
etc.

Simply read through the text file, line by line.
On each line split the string into an array by the delimiters then add each item in the array into the relevent column in the dataset.

For the following example, i used the above example data using a "," as the delimiter!

Example:
    Public NewDataSet As DataSet
    Private Sub MyBase_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        NewDataSet = New DataSet("DataSetName")
        NewDataSet.Tables.Add(New DataTable("DataToSave"))
        NewDataSet.Tables("DataToSave").Columns.Add(New DataColumn("Field1", GetType(String)))
        NewDataSet.Tables("DataToSave").Columns.Add(New DataColumn("Field2", GetType(String)))
        NewDataSet.Tables("DataToSave").Columns.Add(New DataColumn("Field3", GetType(String)))
        NewDataSet.Tables("DataToSave").Columns.Add(New DataColumn("Field4", GetType(String)))

        ReadTxtToXmlFile()
    End Sub
    Public Sub ReadTxtToXmlFile()
        Dim ReadTextFile As New System.IO.StreamReader(Application.StartupPath & "\testFile.txt")
        Do Until ReadTextFile.Peek = -1
            Dim LineArr As String() = Split(ReadTextFile.ReadLine, ",", 4)
            Dim NewDataRow As DataRow = NewDataSet.Tables("DataToSave").NewRow
            NewDataRow("Field1") = LineArr(0)
            NewDataRow("Field2") = LineArr(1)
            NewDataRow("Field3") = LineArr(2)
            NewDataRow("Field4") = LineArr(3)
            NewDataSet.Tables("DataToSave").Rows.Add(NewDataRow)
        Loop
        NewDataSet.WriteXml("XMLDataFile.xml", XmlWriteMode.WriteSchema)
    End Sub


Hope this helps, let me know if this isn't what you wanted!

Tim
0
aarons34Author Commented:
hi Tim,

Thanks for your help. However, the transformation of the text file doesn't work quite the same as you suggest when you read in a ftp server.

In your example, I would have to download the file to the local machine through FTP protocol anyways and that is what I want to avoid.

When opening the socket of the ftp, the stream of bytes transfer end up in a local file. I want that stream of bytes end up in a dataset.

I'm raising the points to 500.

Thanks
0
w3timCommented:
OK, so I assume my code is what you wanted appart from it requiring you to download the file first!?

That being the case, can you post the code you are using to currently download the files off your FTP server and I will try to modify it to write the data to a stream rather than a file.  Then the code I gave you above will still apply but rather than the streamreader reading a file it will read the downloaded stream directly!

Thanks,

Tim
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

aarons34Author Commented:
It doesn't apply because that is not what I need. As I stated in my question, I already do that. I have no problems reading a local file file and parsing it into a dataset and I don't have problem downloading the file because I use a third party ftp class that uses sockets.

Therefore, you are repeating what I already know. I want to mix both

Thanks

0
platinumbayCommented:
What Tim suggested cuts of the local file, just read the FTP buffer into the dataset from the FTP stream.  Re-read it again.  There is no direct coversion between a data file and a dataset, without using XML and a schema.

Maybe you should post some code regarding what you are trying to do.
0
aarons34Author Commented:

Hi platinum,

The thing is that when you read from the local file, you can tell what is a tab, and what is data, but I don;t know exactly how can I do that (define the schema) for the FTP stream

Any ideas?
0
platinumbayCommented:
How to Access a File Transfer Protocol Site by Using Visual Basic .NET

http://www.developer.com/net/vb/article.php/3424121
http://support.microsoft.com/kb/832679/EN-US/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
w3timCommented:
Sorry for the long reply, I've been ill for the last week.  Something really nasty going around, everyone I know seems to be getting it!

Anyway, Platinumbay is right, the easiest way to do this is to hash up the standard code that Microsoft issue (there are some refined/improved versions around) for FTP.

I believe there is a DownloadFile sub in the code somewhere, i seem to remember using it.  It probably uses a filestream to output to file.  All you need to do is dump the data to the memory rather than a file and then read the stream into the code I gave before.
0
aarons34Author Commented:
Thank you all,

by modifying the way I transfer the file from the buffer to the local machine, I was able to create the dataset that I needed and load the file in memory to then process it.

Thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.