CSV --> EXCEL --> DBASEIII with Visual Basic 6

[novelle]

hi there!

I have a problem converting a CSV file to Excel and then converting it again to DBASEIII.
So my CSV File looks like this the first row are the column names and the seperator is the semicolon.

How can i convert it to EXCEL and then to DBASEIII using Visual Basic 6.

It will be really nice if u could help me with this.

thanks a lot!

P.S. I just started programming VB6 im very new to this...

[RobDog888]

Weocome to the Forums.

I have an Office Development FAQ that has several helpful items.

• How do I convert a csv file to an xls fileformat?

• How do I transfer data between Access and Excel?

The second example is not exact as you need it but if you used a linked table in Access to your DB III table you can use that linked table as the destination table.

[novelle]

hi again!

well the code there is not working well...
i have an error on the DataType:=xlDelimited
P.S. i just started programming Visual Basic 6 it will be nicer if u can
explain in detail to me...

thanks a lot!

Private Sub cmdConvert_Click()
 
    On Error GoTo ErrHandler
        
        Dim oApp As Excel.Application
        Dim oWB As Excel.Workbooks
    
        Set oApp = New Excel.Application
    
        Set oWB = oApp.Workbooks.Open(FileName:="& fileSource &", Origin:=xlMSDOS, DataType:=xlDelimited, Comma:=False)
        oWB.SaveAs FileName:="& toFile &", FileFormat:=xlWorkbookNormal
        oWB.Saved = True
        oWB.Close
        Set oWB = Nothing
        oApp.Quit
        Set oApp = Nothing
    
    Exit Sub
    
ErrHandler:
    If Err <> 0 Then
        MsgBox Err.Source & vbCrLf & _
            Err.Description & vbCrLf & _
            Err.Number, , "Error"
    End If
End Sub

[RobDog888]

Did you add a reference to MS Excel xx.0 Object Library like in the FAQ?

[novelle]

yes i did that but it still not working.
BTW: by Comma:= True.
well my delimiter is semicolon should i put this as FALSE?

thanks a lot!

can u please look at my code and try it to ur compiler

Private Sub cmdConvert_Click()
 
    On Error GoTo ErrHandler
        
        Dim oApp As Excel.Application
        Dim oWB As Excel.Workbook
    
        Set oApp = New Excel.Application
        Set oWB = oApp.Workbooks.Open(FileName:="& fileSource &", Origin:=xlMSDOS, DataType:=xlDelimited, Comma:=True)
        oWB.SaveAs FileName:="& toFile &", FileFormat:=xlWorkbookNormal
        oWB.Saved = True
        oWB.Close
        Set oWB = Nothing
        oApp.Quit
        Set oApp = Nothing
    
    Exit Sub
    
ErrHandler:
    If Err <> 0 Then
        MsgBox Err.Source & vbCrLf & _
            Err.Description & vbCrLf & _
            Err.Number, , "Error"
    End If
End Sub

[RobDog888]

Can you telll us what do you mean by "not working"?

If the delimiter is a semicolon then the other delimiters would be false as semicolon would be true.

[novelle]

when i convert the file i have this error message: 'look at the jpeg file'
Err.
Function or Variable Expected --> higlighted ".OpenText"

[RobDog888]

Somehow in your code doing the copy/paste you changed the function.

Set oWB = oApp.Workbooks.[hl]Open[/hl](FileName:="& fileSource &", Origin:=xlMSDOS, DataType:=xlDelimited, Comma:=True)

[novelle]

hhmm...
good i'll explain, if i use:
OpenText: i get the error message: Function or Variable Expected like the jpeg file shows.

if i use:
Open: the i got an error on: DataType:xlDelimited.

dont know how to solve this problem.

did u tried my code to compile on ur desktop?

[Comintern]

I actually prefer writing my own routines to import csvs into Excel. I find that they are a lot easier to customize (i.e. skipping columns or adding calculated data). Here's an example that creates a new workbook from a passed filename and application instance. It returns a reference to the sheet (handy for formatting in the calling function).

Private Function CSVToExcel(oApp As Excel.Application, sFile As String) As Excel.Worksheet
 
    Dim oBook As Excel.Workbook, lRow As Long, lCol As Long, sBuffer As String, iFile As Integer
    Dim sLines() As String, sRow() As String
    
    iFile = FreeFile
    Open sFile For Binary As #iFile
    sBuffer = String$(LOF(iFile), Chr$(0))
    Get #iFile, , sBuffer
    Close #iFile
    
    sLines = Split(sBuffer, vbCrLf)
    Set oBook = oApp.Workbooks.Add
    Set CSVToExcel = oBook.Worksheets(1)
 
    With CSVToExcel
        For lRow = 0 To UBound(sLines)
            sRow = Split(sLines(lRow), ",")
            For lCol = 0 To UBound(sRow)
                .Cells(lRow + 1, lCol + 1).Value = sRow(lCol)
            Next lCol
        Next lRow
    End With
 
End Function

But the main question is why you need to go through Excel before putting the data into DBaseIII? Do you actually need to have a copy of it in Excel or were you just planning of using Excel to export to DBase? If not, I'd just use ADO and skip the Excel part.

[RobDog888]

xlDelimited is a constant. Take the double quotes off of it.

[novelle]

hi comintern!
thank you for ur reply! sure it will be much easier for me if its possibly to
transfer the CSV file Recordset to a DBASE III Table.
as i've said im very new to this and i dont know to handle things.
but do u think its easier to get the data from a CSV file instead of a EXCEL file into a DBASE III table?
it will be great if theres any solution for this.

thanks a lot!

[Comintern]

I definately think this would be easier and faster to just open the DB with ADO and write the records directly. You shouldn't have much trouble finding help on using ADO. If the csv columns line up with the database columns, something like this would do the trick:

'Add a reference to Microsoft ActiveX Data Objects
Private oConnect As ADODB.Connection
 
Private Sub InsertCSV(sCSVFile As String, sDBFile As String)
 
    Dim oRS As ADODB.Recordset, lRow As Long, lCol As Long, sBuffer As String, iFile As Integer
    Dim sLines() As String, sRow() As String
    
    iFile = FreeFile
    Open sCSVFile For Binary As #iFile
    sBuffer = String$(LOF(iFile), Chr$(0))
    Get #iFile, , sBuffer
    Close #iFile
    
    sLines = Split(sBuffer, vbCrLf)
    Set oRS = OpenDB(sDBFile)
 
    With oRS
        For lRow = 0 To UBound(sLines)
            sRow = Split(sLines(lRow), ",")
            For lCol = 0 To UBound(sRow)
                .Fields(lCol + 1).Value = sRow(lCol)
            Next lCol
        Next lRow
        .Update
        .Close
    End With
 
    Set oRS = Nothing
    oConnect.Close
    Set oConnect = Nothing
 
End Sub
 
Private Function OpenDB(sDBPath As String) As ADODB.Recordset
 
    Dim sCon As String
    
    On Error Resume Next
       
    sCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath & ";Extended Properties=DBASE III"
    Set oConnect = New ADODB.Connection
    
    With ConnectDB
        .CursorLocation = adUseClient
        .Open sCon
    End With
    
    If Err.Number <> 0 Then
        Set OpenDB = New ADODB.Recordset
        'Might have to change some of the settings.
        With OpenDB
            .CursorType = adOpenStatic
            .CursorLocation = adUseClient
            .LockType = adLockPessimistic
            .Source = "SELECT * FROM Table"     'Replace Table with your table name.
            .ActiveConnection = oConnect
            .Open
            .MoveFirst
        End With
    End If
 
End Function

If the columns don't line up, it might be easier to parse each individual line and build an insert statement.

[novelle]

hi comintern!
Well the bad thing is my CSV file dont have the same columname as my DBASE III Table.
Do you think its possible for me to insert the Recordset from the CSV file to my DBASE III table?
How can i say in VB6:
Take all the data from the COLUMNAME: Name FROM CSV-file and THEN
INSERT IT TO the COLUMNAME: 2Name FROM DBASE III TABLE.

BTW: my CSV file is with SEMICOLON delimited. and the first ROW are the columnames.

Thanks alot!

[Comintern]

If the header names are the same, you can access the database fields with the field names from the csv file. Just pull the first line from the csv, put it into it's own array, and index the .Field of the recordset with the corresponding column name:

Dim sHeaders() As String
    
    '...
 
    With oRS
        sHeaders = Split(sLines(0), ";")                    'Build a header array.
        For lRow = 1 To UBound(sLines)
            sRow = Split(sLines(lRow), ";")
            For lCol = 0 To UBound(sRow)
                .Fields(sHeaders(lCol)).Value = sRow(lCol)  'Index the field off of the corresponding header.
            Next lCol
        Next lRow
        .Update
        .Close
    End With
 
    '...

[novelle]

hi comintern!

Im having trouble with this line

Set oBook = oApp.Workbooks.Add

not with a WITH-Clause define.

what does it mean?

Thanks a lot!

[Comintern]

Whoops! Forgot to pull that out when I modified the code for ADO. I edited the post above.

[novelle]

hi there comintern!

still some problem with this! sorry really...
.Update i received a error message: Not Allowed for a CLOSED object or
something like that...

With oRS
        For lRow = 0 To UBound(sLines)
            sRow = Split(sLines(lRow), ",")
            For lCol = 0 To UBound(sRow)
                .Fields(lCol + 1).Value = sRow(lCol)
            Next lCol
        Next lRow
        .Update ' HERES THE LINE WHERE THE ERROR HAPPENS
        .Close
    End With

[Comintern]

Wow, I really hacked that didn't I. Need to add a new record in the loop before setting values to it. If you still get the same problem you can move the update statement inside the loop too:

With oRS
        For lRow = 0 To UBound(sLines)
            sRow = Split(sLines(lRow), ",")
            .AddNew
            For lCol = 0 To UBound(sRow)
                .Fields(lCol + 1).Value = sRow(lCol)
            Next lCol
            .Update
        Next lRow
        .Close
    End With