[RESOLVED] Problem saving a csv file to mdb..

[Chowking]

Hi All,

My objective is to open a .csv file (with out field name) and save to .mdb (with field name) i've created. My problem is the csv file doesn't have any field name to select and supposedly assign to mdb fields.

Csv file;
A 1 jon ax 3 4 5 8 9 0 1 3
B 5 bell 7 8 6 9 7 8
C 9 ton ax 0 4 4

Mdb filed name;
set no. name A B C D E F G H I

I can open the csv file but saving the content inside the mdb is my problem..
I am usign VB.. Hoping to hear from you soon..

Many Thanks!

[MartinLiss]

Do you need to be able to extract the data from the database by field name?

[fish4fun]

Chowking,

CSV files are simply tab-delimited text files. You would need to "parse" the file either first, or as you go. That is, you will need to separate the fields and assign them to variables. If you work with the fields a lot, you might consider creating a datatype. Once you have a line parsed you will need to append a record to your database. To do this you will need a statement something like this:

Sql = "INSERT INTO PluInfo ( PluRef, [Desc], Retail, Cost, LastCost, InvAv, "
Sql = Sql & "Taxable, Mfg, QOH, ReordBelow, DefaultVendor, TaxRate, Discountable, "
Sql = Sql & "NormReord, MinReord, LastMod, Status )  "
Sql = Sql & " SELECT "
Sql = Sql & NewPlu.PluRef & ", "
Sql = Sql & Chr(34) & Trim(NewPlu.Desc) & Chr(34) & ", "
Sql = Sql & NewPlu.Retail & ", "
Sql = Sql & NewPlu.Cost & ", "
Sql = Sql & NewPlu.LastCost & ", "
Sql = Sql & "0, "                           'InvAvg = 0
Sql = Sql & NewPlu.Taxable & ", "
Sql = Sql & NewPlu.Mfg & ", "
Sql = Sql & 0 & ", "                        'QOH = 0
Sql = Sql & NewPlu.ReOrdBelow & ", "
Sql = Sql & NewPlu.DefaultVendor & ", "
Sql = Sql & NewPlu.TaxRate & ", "
Sql = Sql & NewPlu.Discountable & ", "
Sql = Sql & NewPlu.NormReord & ", "
Sql = Sql & NewPlu.MinReord & ", "
Sql = Sql & "#" & Now() & "#, "
If NewPlu.Status < 1 Then
    Sql = Sql & "1; "
Else
    Sql = Sql & NewPlu.Status & ";"
End If

Db.Execute Sql

This presumes that you have opened your database.

Parsing your csv into lines and then parsing each line into fields is the way I generally handle this. You can use a function like this:

Public Function ParseLines(Txt As String, Separator As String) As String()
    Dim iBeg As Long
    Dim iEnd As Long
    Dim iLen As Long
    Dim S() As String
    Dim Cnt As Long
    Dim L() As String
    Dim X As Long

    ReDim S(100)
    
    iBeg = 1
    
    Txt = Trim(Txt)
    Do
        iEnd = InStr(iBeg, Txt, Separator)
        iLen = iEnd - iBeg
        If iLen < 0 Then
            Exit Do
        End If
        S(Cnt) = Mid(Txt, iBeg, iLen)
        iBeg = iEnd + Len(Separator)
        Cnt = Cnt + 1
        If Cnt > UBound(S) Then
            ReDim Preserve S(Cnt + 500)
        End If
    Loop Until iBeg > Len(Txt)
    S(Cnt) = Mid(Txt, iBeg, (Len(Txt) - iBeg) + 1)
    
    If Len(S(Cnt)) > 0 Then
        Cnt = Cnt + 1
    End If
    
    ReDim L(Cnt - 1)
        
    For X = 0 To Cnt - 1
        L(X) = S(X)
    Next X
    
    ParseLines = L
    
End Function

To parse the csv file into lines you would do this:

Dim CSV as String
Dim L() as String
Dim F() as String
Dim X as Integer

CSV = GetCSVTxt         'I am Assuming you have some function that does this

L = ParseLines(CSV,VBCrLf)

For X = 0 to ubound(L)
     F = ParseLines(L(x))
     'F is now an array of the Fields For Line X
     'You Would then add a call to a function to save this record to the DataBase
Next X

Hope this helps

Fish

[Siddharth Rout]

I don't have vb6 only vba (I am in office ) now so doing it offhand... Please amend syntax errors, if any.

Private Sub Command1_Click()
    Dim oAccss As Access.Application
 
    Set oAccss = New Access.Application
    
    '~~> Change this to your relevant file
    oAccss.OpenCurrentDatabase "c:\MyDatabase.mdb"
    
    '~~> Do the transfer
    oAccss.DoCmd.TransferText acImportDelim, _
    , "Table1", "c:\MyCsv.csv", True
    
    '~~> Close and Cleanup
    oAccss.CloseCurrentDatabase
    Set oAccss = Nothing
End Sub

[RobDog888]

You should have some error handling in case Access isnt installed or available on the users system too as well as make it a bit more dynamic with a commondialog control.

Bored at work and its lunch time

Option Explicit
'Add a reference to Microsoft Access xx.0 Object Library
Private moApp As Access.Application

Private Sub Command1_Click()
    On Error GoTo My_Error

    'Create object only if it doesnt exist
    If TypeName(moApp) = "Nothing" Then
        Set moApp = CreateObject("Access.Application")
    End If
    'No need to show Access database
    moApp.Visible = False
    With CommonDialog1
        .CancelError = True
        .Filter = "Microsoft Access 1997-2003 Database (*.mdb)|*.mdb|Microsoft Access 2007 Database (*.accdb)|*.accdb"
        .FilterIndex = 1
        .FileName = vbNullString
        .Flags = cdlOFNFileMustExist Or cdlOFNPathMustExist
        .ShowOpen
        If .FileName <> vbNullString Then
             moApp.OpenCurrentDatabase .FileName
        Else
            Exit Sub
        End If
    End With
    'Turn off warning messages like "Macros" etc.
    moApp.DoCmd.SetWarnings False
    'Do the actual import. Maybe add a checkbox in your
    'app to identify if it has first row with field names
    'or if you have a saved import specification to use
    With CommonDialog1
        .CancelError = True
        .Filter = "Comma Delimited Files Only (*.csv)|*.csv"
        .FilterIndex = 1
        .FileName = vbNullString
        .Flags = cdlOFNFileMustExist Or cdlOFNPathMustExist
        .DialogTitle = "Select CSV File To Import"
        .ShowOpen
        If .FileName <> vbNullString Then
            moApp.DoCmd.TransferText acImportDelim, , "Table1", .FileName, False
        Else
            'They clicked Cancel
            Exit Sub
        End If
    End With
    'Return the warnings back
    moApp.DoCmd.SetWarnings True
    Exit Sub
My_Error:
    If Err.Number <> cdlCancel Then
        MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbExclamation
    End If
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
    On Error GoTo My_Error
    'Clean up objects in memory
    If TypeName(moApp) <> "Nothing" Then
        moApp.CloseCurrentDatabase
        moApp.Quit acQuitSaveNone
    End If
    Set moApp = Nothing
    Exit Sub
    
My_Error:
    Set moApp = Nothing
End Sub

[Chowking]

Well appreciated! Thank you so much!

[RobDog888]

No prob, glad to help.

Ps,, if all your questions have been answered on this topic, please dont forget to mark your thread as Resolved so others know its done.