Import csv data file into access database..

[alander]

I now have a .csv data file, i want to update/add records into my current employee database, however this data file contains fields from multiple table and not all the fields in the database are in this data file..

using SQLBulkCopy? i am not sure..

Any advise would be appreciated

 

[giadich]

You can write a script to parse the data file and import it in.

I know for sure you can use oledb to open excel and as if you open a database but have not tried it on a .csv file. My guess is that you can - if not do a quick convertion from .csv to .xls that way you don't need to parse the file.

 

[ss7thirty]

There is a plethora of options for how to do this. I would have to look at the actual data to decide what is best. Have you tried just important the CSV file through the Wizard. If you you could just use a streamreader and an oledb connection to insert the records "manually".

Sloppy but may work:

    Public Sub ReadCSVManually()
        Dim sr As New System.IO.StreamReader("c:\pathofmyfile.txt")
        Dim line As String = sr.ReadLine()
        'if first line has field names create collection so you can decide which to keep
        Dim strCol As New Collections.Specialized.StringCollection
        For Each str As String In line.Split(","c)
            strCol.Add(str)
        Next
        line = sr.ReadLine()
        Do Until line Is Nothing
            Dim intCounter As Integer = 0
           'for each cell in the record
            For Each str As String In line.Split(","c)
                Select Case strCol(intCounter)
                 'if the column name matches what you want  
                  Case "ACOLUMNYOUWANT", "ANOTHERCOLUMNYOUWANT"

                End Select
                intCounter += 1
            Next
            line = sr.ReadLine()
        Loop
    End Sub

 

[alander]

i used this component works pretty fine..

http://msdn2.microsoft.com/en-us/library/cakac7e6(VS.80).aspx


ty for the reply