[RESOLVED] Populate a dataset from a CSV file...

[Pzykotik]

Well I've completely lost interest in this project.

I'm now being told that there will be no database access from my mobile device, it's going to be recieving CSV files via an FTP server. I've been told that the file then needs to be read into a local SDF file and then worked on. Now I can't see why I would need to bother putting the information into another flat file to work on, so it SHOULD be possible to just copy the info out of the CSV and put it into a dataset, then after the data is modified or whatever write another CSV file.

I already know it can be done easily with an XML file but for some reason by boss doesn't want to know. I've been reading this morning and I can read a line from the CSV with StreamReader but I don't really know what to do from there... Does anybody know of a solution?

Thanks

[Jigabyte]

Hi Mate,

I have done this, look at the code below:-

Dim csvFile As New System.IO.StreamReader("csv file name")
Dim sRow As String
Dim sColumns As String()

sRow = csvFile.ReadLine
sColumns = sRow.Split(";")

The sColumns now contains all the columns from the first CSV line so sColumns(0) would be your first column. You then just define your dataset and populate as normal.

Regards,

Jiggy

[Jigabyte]

I had my data transfered using the semi colon because I was using addresses and some columns had a comma in.

[Pzykotik]

legendary! I've been messing around with SQLServer CE for a while as I thought that I would have to use that!

Thankyou SO much

[Pzykotik]

ok n00b question... I need to write a do while loop now right? This is a little more complicated than I thought... You couldn't give me a bit more code could you please?

Thankyou very much!

[Jigabyte]

OK what you now do is the following:-

While sRow <> ""
'Do your bit with the sColumns

sRow = csvFile.ReadLine
if sRow <> '' Then
sColumns = sRow.Split(";")
endif

Do While

[Jigabyte]

I have an SQL CE database and read the CSV file into that:-

Public Sub ImportCustomers()

        Dim csvFile As New System.IO.StreamReader("/Program Files/PocketAudit/Stores List.txt")
        Dim sRow As String
        Dim sColumns As String()

        Dim sqlDAStores As SqlCeDataAdapter
        Dim sqlDSStores As New DataSet
        Dim sqlCBStores As SqlCeCommandBuilder
        Dim sqlDRStores As DataRow

        Dim sSQL As String

        sSQL = "SELECT * FROM ST_STORES"
        sqlDAStores = New SqlCeDataAdapter
        sqlDAStores.SelectCommand = New SqlCeCommand(sSQL, CeData)
        sqlDSStores = New DataSet
        sqlDAStores.Fill(sqlDSStores, "ST_STORES")

        sRow = csvFile.ReadLine
        sColumns = sRow.Split(";")

        While sRow <> ""
            If sColumns(0) <> "" Then
                sqlDRStores = sqlDSStores.Tables(0).NewRow
                sqlDRStores("ST_STORECODE") = sColumns(1).PadLeft(3, "0").ToString
                sqlDRStores("ST_STORENAME") = sColumns(2).ToString
                sqlDRStores("ST_ADD1") = sColumns(3).ToString
                sqlDRStores("ST_ADD2") = sColumns(4).ToString
                sqlDRStores("ST_ADD3") = sColumns(5).ToString
                sqlDRStores("ST_POSTCODE") = sColumns(6).ToString
                sqlDRStores("ST_TELENO") = sColumns(7).ToString
                sqlDRStores("ST_TURNOVER") = "0"
                sqlDRStores("ST_NO_CHECKOUTS") = "0"
                sqlDRStores("ST_RECORD_STATUS") = "NO_CHANGE"
                sqlDRStores("ST_CONV_CONTACT_NAME") = ""
                sqlDRStores("ST_CONV_SPECIAL_INSTRUCT") = ""

                sqlDSStores.Tables(0).Rows.Add(sqlDRStores)
            End If

            sRow = csvFile.ReadLine
            If sRow <> "" Then
                sColumns = sRow.Split(";")
            End If

        End While

        sqlCBStores = New SqlCeCommandBuilder(sqlDAStores)
        sqlDAStores.Update(sqlDSStores, "ST_STORES")
        sqlCBStores.Dispose()
        sqlDSStores.Dispose()
        sqlDAStores.Dispose()

        MessageBox.Show("Complete!", "Stores Import", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1)

    End Sub

[Pzykotik]

Thankyou SO much

[Pzykotik]

It might work better for me to use an SQL CE database anyway, it'll give me more functionality right?

[Pzykotik]

sqlDAStores.SelectCommand = New SqlCeCommand(sSQL, CeData)

What's the CeData all about? I would have looked it up but I've been in a meeting since my last post!!!

[Shaggy Hiker]

That would be your DB Connection.

Looks like you have this pretty well covered.

[Pzykotik]

Thankyou ever so much guys!

[Pzykotik]

Jigabyte, how do you get your CSV file to your PDA? I've been reading about FTP access but apparently it's not supported!

[petevick]

Hi,
FTP is not natively supported, but you can write your own, or take advantage of a 3rd party. Try opennetcf for an FTP class

Pete

[Pzykotik]

I'd read the NetCF page... I can't work out if I have to buy something from them or I can use one of their freely downloadable libraries...? I wouldn't know where to even start writing my own! lol

[petevick]

Hi,
you can use the community edition with no problems, but the paid edition is integrated with VS2005.
In my opinion, it is money well spent.
Alternately, I am sure there are some VB.Net FTP classes that are easily adaptable for the CF

Pete