Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2004
    Location
    the Netherlands
    Posts
    25

    Unanswered: Import CSV-file, different line-types: how to deal with it?

    Hi,

    I’m dealing with a problem of which I don’t know how to solve it….I hope someone over here can help me out (or at least tell me where to begin).

    I want to fill a table in an Access-database with data coming from an external source. This file in csv-format contains sales from multiple shops and contains three types of lines :

    Type 1: header, can be ignore
    Type 2: another header containing one shopid and a period (week 43 or month 11)
    Type 3: salesdata (amount of sales, sales-value, stock) for the shopid mention in the row with type 2

    This is a repeating sequence, so the file should look a bit like this: 1,2,3,3,3,3,1,2,3,3,1,2,3,3,3,3,3,3,1,2,3,3,3, etc.

    What do I want: the salesdata from type 3 from this csv-file in one access-table, each row containing the shopid and period from the type 2 line, followed by the salesdata from the type 3 line (so: one record per type 3 line).

    How do I do this? I know how to import/link csv-files into an Access-table, I’m not a VBA-wizard…

    I do have an example-file, but I don't know how to attach it to this post...

    I hope I explained the situation well enough...it's very hard to explain it in my native language (Dutch), let alone in english...

    Thanks for your help and kind regards,

    Michael

  2. #2
    Join Date
    Feb 2004
    Posts
    137
    To attach your sample file is very simple. Click on Post Reply, then under Additional Options, click on Manage Attachments. Your description of the scenario is somewhat obfuscated; the sample file could possibly provide a clearer picture.

  3. #3
    Join Date
    Jul 2004
    Location
    the Netherlands
    Posts
    25
    Well, that's simple.... Attachment included with this post!

    Small explanation: the first row of the file (first line-type 1) can be used as a header-row, it contains the column description of the line-type 3.
    Of the line-type 2 only field 1 (shopid) and field 4 (period) should remain in the table.

    The files are usually a lot bigger, this is only a small example...

    Thanks for any help!


    Michael
    Attached Files Attached Files

  4. #4
    Join Date
    Feb 2004
    Posts
    137
    Paste the following code into a new Module and save the module as mod_salesdatImport:
    Code:
    Public Sub salesdatImport(fileName As String)
    
    Dim ShopID As String, Period As String
    
    Dim strSQL As String
    Dim strHeader As String, varHeader As Variant
    Dim intLine As Long, varLine As Variant
    Dim intField As Long
    Dim tbd As TableDef, fld As Field
    
    ReDim varBuffer(0) As String
    
    If Dir(fileName, vbNormal) = "" Then MsgBox "File Not Found", vbExclamation: Exit Sub
    
    Open fileName For Input Shared As #1
    While Not EOF(1)
        ReDim Preserve varBuffer(UBound(varBuffer) + 1)
        Line Input #1, varBuffer(UBound(varBuffer) - 1)
    Wend
    Close #1
    ReDim Preserve varBuffer(UBound(varBuffer) - 1)
    
    strHeader = varBuffer(0)
    varHeader = varArray(strHeader, ";")
    
    For intLine = 0 To CurrentDb.TableDefs.Count - 1
        If CurrentDb.TableDefs(intLine).Name = Left(Dir(fileName, vbNormal), Len(Dir(fileName, vbNormal)) - 4) Then GoTo TableSkip: Exit For
    Next intLine
    
    Set tbd = CurrentDb.CreateTableDef(Left(Dir(fileName, vbNormal), Len(Dir(fileName, vbNormal)) - 4))
    tbd.Fields.Append tbd.CreateField("ShopID", dbText, 50)
    tbd.Fields.Refresh
    tbd.Fields.Append tbd.CreateField("Period", dbText, 50)
    tbd.Fields.Refresh
    
    For intField = 0 To UBound(varHeader)
        Set fld = tbd.CreateField(varHeader(intField), dbText, 50)
        fld.AllowZeroLength = True
        tbd.Fields.Append fld
        tbd.Fields.Refresh
    Next intField
    
    CurrentDb.TableDefs.Append tbd
    CurrentDb.TableDefs.Refresh
    
    TableSkip:
    
    intLine = 0
    
    Do
        If varBuffer(intLine) = strHeader Then
            varLine = varArray(varBuffer(intLine + 1), ";")
            ShopID = varLine(0)
            Period = varLine(3)
            intLine = intLine + 2
        End If
        While intLine <= UBound(varBuffer) And varBuffer(IIf(intLine > UBound(varBuffer), UBound(varBuffer), intLine)) <> strHeader
            strSQL = "INSERT INTO [" & Left(Dir(fileName, vbNormal), Len(Dir(fileName, vbNormal)) - 4) & "] (ShopID,Period,"
            For intField = 0 To UBound(varHeader) - 1
                strSQL = strSQL & varHeader(intField) & ","
            Next intField
            strSQL = strSQL & varHeader(UBound(varHeader)) & ") SELECT '" & ShopID & "','" & Period & "','"
            varLine = varArray(varBuffer(intLine), ";")
            For intField = 0 To UBound(varLine) - 1
                strSQL = strSQL & varLine(intField) & "','"
            Next intField
            strSQL = strSQL & varLine(UBound(varLine)) & "';"
            CurrentDb.Execute strSQL
            intLine = intLine + 1
        Wend
    Loop Until intLine > UBound(varBuffer)
    
    End Sub
    
    Private Function varArray(strArray As String, strDel As String) As Variant
    
    Dim strTemp As String
    ReDim varTemp(0) As String
    
    strTemp = strArray
    While InStr(strTemp, strDel) > 0
        ReDim Preserve varTemp(UBound(varTemp) + 1)
        varTemp(UBound(varTemp) - 1) = Left(strTemp, InStr(strTemp, strDel) - 1)
        strTemp = Mid(strTemp, InStr(strTemp, strDel) + 1)
    Wend
    varTemp(UBound(varTemp)) = strTemp
    
    varArray = varTemp
    
    End Function
    For example, if you call the code with:

    Call salesdatImport("C:\Example.csv")


    ...this will create a new table in your database named Example, with the related data.

    See if this works for you.

  5. #5
    Join Date
    Jul 2004
    Location
    the Netherlands
    Posts
    25
    Thanks a lot Matthew. Sorry for the late reply, but I was a few days not at the office. I'll take a look at it today and give it a try. It looks very promising!

    Thanks,
    Michael

  6. #6
    Join Date
    Jul 2004
    Location
    the Netherlands
    Posts
    25

    Question

    Tried Matthews solution, but got a compiling error...

    What I did was paste the supplied code into a new module and then created a form with a button; when the button is clicked there is piece of code executed which calls the newly created module (
    Call salesdatImport("C:\Example.csv") ). Hey, this might sound very simple for you guys, but for me this was a real achievement...


    Now I get the following error:

    Compile error: "User-defined type not defined" which refers to the the following line: "Dim tbd As TableDef".

    Did I do something wrong? Or should the code be changed ???

    Thanks once again,
    Michael

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    TableDef is a DAO thing and maybe you are using ADO?
    (wouldn't it be nice if everyone detailed their environment when posting questions & answers - it really really really helps).

    either you Alt-F11 ¦ Tools ¦ References ...and check the Microsoft DAO 3.6 library
    or
    you discover the ADO equivalent of DAO.TableDef (i guess it is Table, but ADO gurus out there are better placed to comment)

    izy


    DAO.Field is also alien to ADO (think it's Column in ADO-speak)
    Last edited by izyrider; 07-09-04 at 04:48.
    currently using SS 2008R2

  8. #8
    Join Date
    Jul 2004
    Location
    the Netherlands
    Posts
    25
    Thanks izy, that did the trick....

    So...now there's another error (I'm feeling a bit stupid, for asking so much - simple things (at least for the most of you I think) to you all...my apologies):

    Compile error: "Method or datamember not found" for the line:
    fld.AllowZeroLength = True

    Another checkbox to be ticked or....???

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    consider:
    Private Sub TestButton()
    Dim dabs As DAO.Database
    Dim tdef As DAO.TableDef
    Dim fld As DAO.Field
    Set dabs = CurrentDb
    Set tdef = dabs.TableDefs("myTable")
    For Each fld In tdef.Fields
    Debug.Print fld.name, fld.Type, fld.AllowZeroLength
    Next
    End Sub

    this returns the following in the immediate window for my test table:
    myID 4 False
    myText 10 False
    myInt 4 False

    attempts to set allowzerolength produce an error on text fields to the effect that the table is in use by another user, attempts on a non-text field produce an error similar to yours.

    drop matthew a private mail to prompt him to explain his code (or be patient and wait for someone else to reply).

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...this annoyed me enough to play some more:

    Private Sub TestButton_Click()
    Dim dabs As DAO.Database
    Dim tdef As DAO.TableDef
    Dim fld As DAO.Field
    Set dabs = CurrentDb
    Debug.Print dabs.TableDefs.Count, "Before the run"
    Set tdef = dabs.CreateTableDef("test", , "tblTest")
    tdef.Fields.Append tdef.CreateField("someText", dbText, 10)
    tdef.Fields("someText").AllowZeroLength = True
    tdef.Fields.Refresh
    tdef.Fields.Append tdef.CreateField("moreText", dbText, 10)
    tdef.Fields("moreText").AllowZeroLength = False
    tdef.Fields.Refresh
    Debug.Print dabs.TableDefs.Count, "After the run"
    dabs.Close
    End Sub

    returns:
    6 Before the run
    6 After the run

    i.e. i'm not doing what i thought i was doing, but at least the above code compiles & runs without error (including both allowzerolength).

    i give up and wait for a wise one to reply.

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ...and a last play:

    Private Sub TestButton_Click()
    Dim dabs As DAO.Database
    Dim tdef As DAO.TableDef
    Dim fld As DAO.Field
    Set dabs = CurrentDb
    Debug.Print dabs.TableDefs.Count, "Before the run"
    Set tdef = dabs.CreateTableDef("test", , "tblTest")
    tdef.Fields.Append tdef.CreateField("someText", dbText, 10)
    tdef.Fields("someText").AllowZeroLength = True
    tdef.Fields.Refresh
    Debug.Print "fields after 1st pass: ", tdef.Fields.Count
    tdef.Fields.Append tdef.CreateField("moreText", dbText, 10)
    tdef.Fields("moreText").AllowZeroLength = False
    tdef.Fields.Refresh
    Debug.Print "fields after 2nd pass: ", tdef.Fields.Count
    Debug.Print dabs.TableDefs.Count, "After the run"
    dabs.Close
    End Sub

    returns:
    6 Before the run
    fields after 1st pass: 1
    fields after 2nd pass: 2
    6 After the run

    now i stop!!!! izy
    currently using SS 2008R2

  12. #12
    Join Date
    Feb 2004
    Posts
    137
    The code should work as I wrote it. Make sure you have Microsoft DAO Object Library checked under your Visual Basic references. (In Access 97, this reference is checked by default)

    I did, however, have a couple of bugs in the varArray function. Here is the revised code for that function:
    Code:
    Private Function varArray(ByVal strArray As String, strDel As String) As Variant
    
        Dim nC As Integer
        ReDim sOut(0) As String
        Do
            If InStr(1, strArray, strDel, vbBinaryCompare) > 0 Then
                sOut(nC) = Left(strArray, InStr(1, strArray, strDel, vbBinaryCompare) - 1)
            Else
                sOut(nC) = strArray
            End If
            strArray = Mid(strArray, Len(sOut(nC)) + Len(strDel) + 1)
            nC = nC + 1
            If strArray = "" Then Exit Do
            ReDim Preserve sOut(nC)
        Loop While strArray > ""
        varArray = sOut
    
    End Function

  13. #13
    Join Date
    Jul 2004
    Location
    the Netherlands
    Posts
    25
    Many thanks to both izyrider and Matthew for all the effort...

    However, I still got stuck on the Compile error: "Method or datamember not found" for the line: fld.AllowZeroLength = True

    I've attached some screenshots of both the error and references, maybe that can give someone a clue.

    VB1.gif = the compiling error
    VB2.gfi = the top part of the references

    Thanks once again,
    Michael
    Attached Thumbnails Attached Thumbnails VB1.gif   vb2.gif  

  14. #14
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    hey Greetz from Amsterdam....

    Your problems seem to be with creating the table.... Why dont you forgo that.... and create your own Table Beforehand....

    Also the code is a bit of a round about way....

    I attached a DB file in both A2K and A97 together with your example...

    It has a predifined Table and imports the csv file without troubles....

    Groetjez en Succez

    Greetz & GL
    Attached Files Attached Files

  15. #15
    Join Date
    Jul 2004
    Location
    the Netherlands
    Posts
    25

    Talking

    De groeten terug uit Amstelveen

    Thanks a lot for helping me out...I used your example-database and that worked out fine! So it's now working like I hope it would work, now I'm trying to understand what the code does for my own understanding.

    I would never reached this result by myself so thanks to all contributors for giving me this solution!


    Ciao,

    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •