Results 1 to 9 of 9

Create sequential csv output from alphanumeric for X quantity

  1. #1
    Seussie is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    5

    Create sequential csv output from alphanumeric for X quantity

    Hi everybody,
    I'm new here. I'm not sure where to start with this project, or even where to post this - sorry if I'm in the wrong Forum.

    I have a table:
    Model FGNum BegSerial QtyOrdered
    4578 06-758 A004000 2000
    2519 331-47-0001 X135000 10000
    ...
    For each Model, I need to end up with an output file (csv) that looks like:
    06-758~A004000
    06-758~A004001
    ...
    for the quantity ordered ([FGNum]+"~"+[BegSerial])
    I'm thinking that I can just do it all with a report? If so, how?
    I'm just not sure where to start.
    I accomplished the same thing with a 'fill down' in Excel last time I worked on this job, and it was pretty tedious. Hoping for a better solution with Access.
    Thanks for any help!
    Susie

  2. #2
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    30,566
    How do you know how far to carry the sequence?

    What is the structure for model 2519 - like this?
    331-47-0001~X135000
    331-47-0001~X135001
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    Seussie is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    5
    The quantity ordered field is how many records I need in the csv file
    The model would be the name of the output file I need (4578.csv)
    The FGNum+Beg Serial is the structure of the numbers - sequentially from there to (FGNum+BegSerial)+QtyOrdered

  4. #4
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    30,566
    You showed a sequence that incremented BegSerial. By that example the sequence would be:

    4578 06-758~A004000
    ...
    4578 06-758~A006000


    and

    331-47-0001~X135000
    ...
    331-47-0001~X145000

    What if the quantity for model 4578 is 12000 or 102000?

    You want a separate CSV file for each model?

    And you do want the tilde character in the string?

    I doubt this can be done with query alone and will need VBA writing records to a table which is then exported to CSV. I have never done code to write directly to CSV. I suppose it is possible.
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  5. #5
    Seussie is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    5
    Correct on the sequence
    If the quantity for 4578 is 12000, it would be to 06-758~A017999, 102000 would be to 06-758~A105999
    Yes a separate csv for each model.
    yes tilde char in the string.
    Thank you for helping meeeeee!

  6. #6
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    30,566
    Did you mean 15999?

    Any possibility the quantity will exceed the number of digits in the numeric parts?

    Is it important for the length of each line to be same?
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  7. #7
    Seussie is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    5
    yes - math in my head - sorry
    no possibility qty will exceed number of digits
    I'm not sure what you mean by the 3rd question? It has to be the number of digits from the starting number. I think they're all the same - 1 alpha + 6 digits padded with leading 0's.

  8. #8
    June7's Avatar
    June7 is offline Moderator
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    30,566
    The third question was related to the one about the quantity exceeding the number of digits. That would make the string longer than the template example. Code behind Excel is an option but consider:
    Code:
    Sub ExportCSV()
    Dim rs As ADODB.Recordset
    Dim i As Integer
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM Table1 ORDER BY Model, FGNum, BegSerial;", CurrentProject.Connection, adOpenStatic, adLockPessimistic
    While Not rs.EOF
        CurrentDb.Execute "DELETE FROM Table2"
        For i = 0 To rs!QtyOrdered - 1
            CurrentDb.Execute "INSERT INTO Table2(CSVString) VALUES('" & rs!FGNum & "~" & Left(rs!BegSerial, 1) & Format(Mid(rs!BegSerial, 2) + i, "000000") & "')"
        Next
        DoCmd.TransferText acExportDelim, , "Table2", "C:\" & rs!Model & ".csv"
        rs.MoveNext
    Wend
    rs.Close
    End Sub
    This version appears to run faster:
    Code:
    Sub ExportCSV()
    Dim rs1 As ADODB.Recordset, rs2 As ADODB.Recordset
    Dim i As Integer
    Set rs1 = New ADODB.Recordset
    rs1.Open "SELECT * FROM Table1 ORDER BY Model, FGNum, BegSerial;", CurrentProject.Connection, adOpenStatic, adLockPessimistic
    While Not rs1.EOF
        CurrentDb.Execute "DELETE FROM Table2"
        Set rs2 = New ADODB.Recordset
        rs2.Open "SELECT * FROM Table2;", CurrentProject.Connection, adOpenDynamic, adLockPessimistic
        For i = 0 To rs1!QtyOrdered - 1
            rs2.AddNew
            rs2!CSVString = rs1!FGNum & "~" & Left(rs1!BegSerial, 1) & Format(Mid(rs1!BegSerial, 2) + i, "000000")
        Next
        rs2.Update
        rs2.Close
        DoCmd.TransferText acExportDelim, , "Table2", "C:\" & rs1!Model & ".csv"
        rs1.MoveNext
    Wend
    rs1.Close
    End Sub
    To provide db: Make copy, remove confidential data, run compact & repair, zip if large - 2mb allowed, attach to post. Attachment Manager is below the Advanced post editor window.
    If suggestion in this post resolves your issue, please use the Thread Tools and mark the thread as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  9. #9
    Seussie is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2012
    Posts
    5
    Yea! A few tweaks and I got the 1st one to work. I have a little time so I'll probably play around with both of them a bit more.
    THANK YOU THANK YOU THANK YOU!!!
    Super Moderator is my new hero! I want to get you a cape! And a car!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-05-2012, 06:31 PM
  2. alphanumeric primary key
    By slimjen in forum Database Design
    Replies: 4
    Last Post: 09-18-2011, 09:20 PM
  3. Format a field(AlphaNumeric)
    By Bakar in forum Database Design
    Replies: 1
    Last Post: 12-20-2010, 04:36 AM
  4. Replies: 4
    Last Post: 10-03-2010, 08:54 PM
  5. create sequential id
    By proudestmnky1 in forum Programming
    Replies: 0
    Last Post: 12-15-2008, 10:10 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
  •  
Tech Forums: Microsoft Office Forums