Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253

    Writing to a text file and linking table to text file

    I have data in a QR code that is ; delimited and each record is separated by *. There will be a varied amount of records in the QR code but each record will have 25 fields that will always be the same. I have code to parse out each record and I have code to parse out a record into the 25 fields. What i wanted to do was write this to a text file and then link a table to the text file so i can use the data. I was also thinking of appending the text file to a table and then deleting the contents of the text file to keep the text file from getting out of hand. Does anyone have links or a path you can point me to? Or possibly some example code that i can study to modify to fit what i am needing. Thank you. --Walker

    Here is an example of the QR code.
    Code:
    N;1;9999;CD9999R;N;4/29/20;89.0;PASS;300.00;-10.00;3.000;1.00;200.00;-20.00;PASS;PASS;11.0;1;JD*N;1;9999;CD9999R;N;4/29/20;89.0;PASS;300.00;-10.00;3.000;1.00;200.00;-20.00;PASS;PASS;11.0;1;JD*N;1;9999;CD9999R;N;4/29/20;89.0;PASS;300.00;-10.00;3.000;1.00;200.00;-20.00;PASS;PASS;11.0;1;JD*N;1;9999;CD9999R;N;4/29/20;89.0;PASS;300.00;-10.00;3.000;1.00;200.00;-20.00;PASS;PASS;11.0;1;JD*N;1;9999;CD9999R;N;4/29/20;89.0;PASS;300.00;-10.00;3.000;1.00;200.00;-20.00;PASS;PASS;11.0;1;JD*N;1;9999;CD9999R;N;4/29/20;89.0;PASS;300.00;-10.00;3.000;1.00;200.00;-20.00;PASS;PASS;11.0;1;JD*N;1;9999;CD9999R;N;4/29/20;89.0;PASS;300.00;-10.00;3.000;1.00;200.00;-20.00;PASS;PASS;11.0;1;JD


  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    put the text file in the same place everytime, ie: c:\temp\QRfile.txt
    in access link the text file as an external table. (Done once, then its permanent)
    then build your query to import the text file.

    then the steps are:
    1. save the txt file to c:\temp\QRfile.txt
    2. run import query
    done.

  3. #3
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    i was thinking i need to parse out each record before i write it to a text file. is that not the correct thinking?

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Build a temporary Access table with the corresponding 25 fields, then look into using the Split function to parser the records and add them to it. Once in you can use an append query to move them to the final Access table then empty the temp table to get it ready for the next run.
    https://support.microsoft.com/en-us/...f-10078c7878f5
    https://www.access-programmers.co.uk...unction.97750/
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your example QR code only has 19 fields between '*' - not 25

    you can use vba and the split function to append the data to a table, rather than exporting to a text file. Something like this


    Code:
    Function QRStrToTable(Qstr As String)
    Dim qrArr() As String
    Dim rArr() As String
    Dim i As Integer
    Dim f As Integer
    Dim sqlstr As String
    
    
        qrArr = Split(Qstr, "*")
        
        For i = 0 To UBound(qrArr)
            sqlstr = ""
            rArr = Split(qrArr(i), ";")
            
            For f = 0 To UBound(rArr)
                
                Select Case f 'format based on field type
                    
                    Case 0, 1, 4 'a text field as required
                        
                        sqlstr = sqlstr & ",'" & rArr(f) & "'"
    
    
                    Case 5 'a date field
                        
                        sqlstr = sqlstr & ",#" & rArr(f) & "#"
                    
                    Case Else 'it's a number
                        
                        sqlstr = sqlstr & "," & rArr(f)
                
                End Select
            
            Next f
            
            sqlstr = "INSERT INTO myTable (fld1, fld2, fld3.....) VALUES (" & Mid(sqlstr, 2) & ")" ' enter your actual table and field names
            CurrentDb.Execute sqlstr
        
        Next i
    
    
    End Function

  6. #6
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    I wrote the code that works great until i get to the INSERT INTO. it keeps telling me i have a syntax error. UnitID is the primary key (autonumber) and not in the string that i am parsing. here is the code for my sql. Does anyone see anything obvious that i am missing in the syntax? Thank you. --Walker

    Code:
    sqlstr = "INSERT INTO tblTemp (unitID, [ATFLoc], [ItemNo], [ProjNum], [SerialNum], [CR], [PolChk], " _
                                               & "[DateTested], [Beam], [MRA], [Imp1], " _
                                               & "[Ph1], [Imp2], [Ph2], [Imp3], [Ph3], [TPR], " _
                                               & "[FF], [ActTemp], [DailyCount], [Operator]) VALUES (" & Mid(sqlstr, 2) & ")"

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Can you please post the entire code to see how/where you declare and use the variables (strsql in particular). If UnitID is an Autonumber just leave it out from the Insert Statement: "INSERT INTO tblTemp ( [ATFLoc], [ItemNo], [ProjNum], [Serial Num],......."
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Here is all of the code

    Code:
    Private Sub btnPop_Click()
        Call QRStrToTable(txtQRCode)
    End Sub
    
    Function QRStrToTable(Qstr As String)
    Dim qrArr() As String
    Dim rArr() As String
    Dim i As Integer
    Dim f As Integer
    Dim sqlstr As String
    
    
        qrArr = Split(Qstr, "*")
        
        For i = 0 To UBound(qrArr)
            sqlstr = ""
            rArr = Split(qrArr(i), ";")
            
            For f = 0 To UBound(rArr)
                
                Select Case f 'format based on field type
                    
                    Case 0, 1, 4 'a text field as required
                        
                        sqlstr = sqlstr & ",'" & rArr(f) & "'"
    Debug.Print sqlstr
    
                    Case 5 'a date field
                        
                        sqlstr = sqlstr & ",#" & rArr(f) & "#"
    Debug.Print sqlstr
                    Case Else 'it's a number
                        
                        sqlstr = sqlstr & "," & rArr(f)
    Debug.Print sqlstr
                End Select
            
            Next f
            Debug.Print sqlstr
            
            sqlstr = "INSERT INTO tblTemp ([ATFLoc], [ItemNo], [ProjNum], [SerialNum], [CR], [PolChk], " _
                                               & "[DateTested], [Beam], [MRA], [Imp1], {Ph1}, [Imp2], " _
                                               & "[Ph2], [Imp3], [Ph3], [Imp4], [Ph4], [TPR], " _
                                               & "[FF], [ActTemp], [DailyCount], [Operator]) VALUES (" & Mid(sqlstr, 2) & ")" 
            Debug.Print sqlstr
            CurrentDb.Execute sqlstr
        
        Next i
    
    
    End Function

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    and what is the sqlstr created when you debug.print? - the one just before you execute.

    I see you are using the code I provided. Given you said there were 25 fields, your latest sql appears to have 21 fields but I could only see 19 - which is what you have in the sql posted in post #6, you need to check the case statement is operating correctly.

    As regards syntax, in your latest version of your sql you have

    & "[DateTested], [Beam], [MRA], [Imp1], {Ph1}, [Imp2], " _

  10. #10
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Ajax,

    here is the sqlstr just before the execute

    Code:
    INSERT INTO tblTemp ([ATFLoc], [ItemNo], [ProjNum], [SerialNum], [CR], [PolChk], [DateTested], [Beam], [MRA], [Imp1], [Ph1], [ImpZ], [FreZ],[PhZ], [Imp2], [Ph2], [Imp3], [Ph3], [TPR], [FF], [ActTemp], [DailyCount], [Operator]) VALUES ('N','1',9999,CD9999R,'FALSE',#4/29/20#,TRUE,89.0,PASS,300.00,-10.00,3.000,75.00,1.00,100.00,25.00,200.00,-20.00,PASS,PASS,11.0,1,JD)
    I got rid of the syntax error thanks to your eagle eyes and I also found the missing field. Thank you. In the table that this data is being inserted to has 23 fields. the 24th and 25th would have been the UnitID(autonumber) which I left out of the sql statement per Gicu in post#7 and the whole QR code for each record which I am no longer saving. The autonumber field is in the main table that i will be appending to.

    Now i am getting a (Too few parameters. Expected 3) error.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you have a number of fields you need to define correctly - I did say you need to check the case statement


    • 'FALSE' - should not have quotes assuming the destination is a boolean field (booleans are numeric)
    • PASS - might be boolean, might be text. If the latter then should be surrounded with single quotes
    • some fields are text which should have quotes, CD9999R, JD

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe you could post a dB with just the table "tblTemp" so we could see the field types.

    Also you are missing a value for one of the first 5 fields. Or you have 1 too many fields in the INSERT INTO part of the SQL string.
    It would be nice to see the actual QR string before it is parsed........
    Code:
    [ATFLoc]     - 'N'
    [ItemNo]     - '1'
    [ProjNum]    - 9999
    [SerialNum]  - CD9999R
    [CR]         - 'FALSE'
    [PolChk]     - #4/29/20#  <<-- this looks like it should be down one line. That means you have too many field names above or you are missing a value
    [DateTested] - TRUE
    [Beam]       - PASS 
    [MRA]        - 300.00
    [Imp1]       - -10.00
    [Ph1]        - 3.000
    [ImpZ]       - 75.00
    [FreZ]       - 1.00
    [PhZ]        - 100.00
    [Imp2]       - 25.00
    [Ph2]        - 200.00
    [Imp3]       - -20.00
    [Ph3]        - PASS
    [TPR]        - PASS
    [FF]         - 11.0
    [ActTemp]    -  1
    [DailyCount] - JD
    [Operator]   -  ??????????

  13. #13
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Ssnafu an example of the unparsed QR string is in post#1. I did find out that you were correct in the date being out of order. The info given to me was not correct and i corrected it to have the PolChk after the DateTested field. Thank you for catching that.

  14. #14
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253

    I have attached the current version of my database that i am working on.

    I have gotten all the data formatted properly but now i have a syntax error in the SQL statement that i cannot figure out. If any of you have a chance could you let me know what you think. Thank you.

    ATFQR.accdb

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I went through your dB and corrected the code.

    Declarations of variables must be explicitly defined. if you have
    Code:
    Dim i, i2, iRec, iFld As Integer
    iFld is declared an Integer
    i, i2 and iRec are defaulted to type Variant.
    This is the proper declaration
    Code:
        Dim i As Integer, i2 As Integer, iRec As Integer, iFld As Integer

    "tblstr" ended up with an extra comma at the end. I added a line to remove the last comma.
    In "sqlstr", when you added "tblstr", the leading single quote was being deleted. Both of these caused an error when trying to insert the data to the table.


    I condensed your case construct to 8 lines. And a added a record loop to get all of the records in Me.txtQRCode.



    I also added the code provided by Ajax using arrays. Much easier to use but maybe harder to understand.
    Look at the code for "Private Sub btnPop_Click()". One of the two lines must be commented out or you will get duplicate records in the ATFdataTemp table.


    Almost forgot. For the text box "Me.txtQRCode", I set the DEFAULT Property to the "SAMPLE QR CODE" that is at the top of the module. the first record has a ProjNum of "9999". The second record, I changed the ProjNum to "1111" so I could tell the records apart.

    There are probably a few other changes that I forgot about......
    But the QR codes get inserted into the table.


    BTW, why did you delete the first record (in your code)? Does a real QRCode have a header??
    Attached Files Attached Files

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 09-09-2015, 12:10 PM
  2. Linking a table to a 3GB text file?
    By kestefon in forum Access
    Replies: 2
    Last Post: 12-20-2013, 05:50 PM
  3. writing to text file (array set)
    By pradeep.sands in forum Forms
    Replies: 5
    Last Post: 08-02-2013, 02:37 PM
  4. Replies: 2
    Last Post: 12-27-2012, 09:37 AM
  5. Writing text to log file.
    By winsonlee in forum Programming
    Replies: 2
    Last Post: 08-05-2011, 12:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums