Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    jmichaels is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    20

    Question Import .csv file line item by line with modifications

    Hello,
    I'm very new to Access VBA. I'm trying to import a csv file which needs to have certain line items modified before being imported. The first 3 fields to be imported are:

    (1) (2) (3)
    CC Desc. Type -->followed by numerous other fields

    The type field alternates between the following six types:

    CASH


    REMAIN
    CREDIT
    REMAIN
    TOTAL
    REMAIN

    And then it starts all over for a different CC. I need the "REMAIN' records to specify if it's a CASH REMAIN, CREDIT REMAIN or TOTAL REMAIN. I don't even know where to begin. I have only used the DoCmd.TransferText command for importing. Any suggestions?

    Thanks
    jm

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Provide a sample file of data. To attach file to post, follow instructions at bottom of my post.

    How are there 6 types? - REMAIN is repeated 3 times so I see 4 types.

    Simplest may be to do the import with TransferText then run an UPDATE action SQL. But really need to see representative data.

    Otherwise, only way to import is to use Read/Write text file code. Open a text file object and read one line at a time and parse its elements.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Import the CSV to a temp 'tImport table',
    run update queries to modify the data,
    then run append queries to their final table.

  4. #4
    jmichaels is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    20
    I ended up writing a script that imports the data as is with a record ID. Next, I wrote a query adding a field called “Linked_ID” which equals ID + 1. Then, I created a second query linking the original table to the table created by the query with the JOIN ID = Linked_ID. I was able to use a conditional statement to get the results I want. My only concern is that I don’t know if Access ALWAYS pulls the csv data in the same order that it exists in the file. So far it has. However, if it doesn’t, for one reason or another, my queries obviously won’t work the way I intended. Could there be a situation that Access doesn't import the data in the same order as it exists in the csv file? If so, is there a way to force it?

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Could there be a situation that Access doesn't import the data in the same order as it exists in the csv file? If so, is there a way to force it?
    difficult to answer - it may import in the same order, but it may not store in the same order. In databases there is no such thing as first/last/previous/next without specifying an order. Ideally your csv file should have a field that enables you to do that.

    Even Excel has the same issue - easy to refer to the previous row, but change the order and you get a different result

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I think one way to ensure the order of the import is by doing it in VBA using read file and using your own custom "line_number" field in the target table.

    Some links (most for Excel VBA but easy to convert for Access:
    https://p2p.wrox.com/access-vba/9261...ess-table.html
    https://excel.officetuts.net/en/vba/read-a-text-file
    https://www.thespreadsheetguru.com/b...ide-text-files
    http://codevba.com/office/read_text_...m#.XoItiohKhPY

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    jmichaels is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    20

    Example Posted

    @June7


    The REMAINS records in the "Type" column need to distinguish between being a CASH-REMAIN, CREDIT-REMAIN or TOTAL-REMAIN. So, the first record would just have CASH in the Type field, the second record would be CASH-REMAIN, the third record would be CREDIT, the fourth would be CREDIT-REMAIN, etc...
    Attached Files Attached Files

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    distinguish between being a CASH-REMAIN, CREDIT-REMAIN or TOTAL-REMAIN. So, the first record would just have CASH in the Type field, the second record would be CASH-REMAIN
    this is different to your original post

    The type field alternates between the following six types:

    CASH
    REMAIN
    CREDIT
    REMAIN
    TOTAL
    REMAIN
    if your latest post is correct then you have an order, at least within the context of a single CC. at the very least you can sort by CC and type which will give you an order (cash comes before cash-remain, which comes before credit etc)

    Or have a separate table listing these types with a sort order - link the two and sort on CC and the sort order field

    For the future - it would really help if you provided more complete example data from the beginning

    Edit: OK, just seen the sample you have finally provided - it is per your original post so please accept my apologies - but the request for more complete data stands

    As it is I think you need to explain more clearly what the data represents (it looks like stock movements although does not make sense to me!) and also what you intend to do with it as it may be you can ignore the 'remain' rows and possibly the total row as well and calculate them as and when required

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I tried importing file into Access table and got import errors on date columns. Had to format those Excel columns as Date. Then file imports properly. There is one error on row 69 which has some unrecognizable character and this can be disregarded.

    I allowed Access to generate autonumber ID field so each record has a unique identifier.

    Built following query:
    SELECT (SELECT TOP 1 [Type] FROM RPT0231A_SP_BTOB_ITW AS Dupe WHERE Dupe.ID<RPT0231A_SP_BTOB_ITW.ID ORDER BY ID DESC) AS Prev, * FROM RPT0231A_SP_BTOB_ITW;

    Then another query:
    SELECT *, IIf([Type]="Remain", [Prev] & "-" & [Type], [Type]) AS NewType FROM Query1;

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    jmichaels is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    20
    @Ajax

    Yep, sorry, I probably wasn't very clear in my original request. The file represents cancellations of orders. The REMAINS rows are important because they represent how much is left after cancellations. The distinction between CASH-REMAINS and CREDIT-REMAINS is important because the accounting is handled differently for each. The numbers in this file probably won't make sense because it's just a dummy file set up just like our actual files are. So, I literally need the file imported as it is except the records that have "REMAIN" in the type field need to specify whether it's a CASH-REMAINS or CREDIT-REMAINS or TOTAL-REMAINS. I have a folder that has a few dozen csv files that are set up like this for different subscriptions. I figured out how to automatically go through and import each csv file in the folder into one table in Access using autonumber (then I used a couple of queries to get where I need to be). But, as you can see if I'm just relying on the import function, it's imperative that the records are imported in the exact same order in which they exist in the csv file and it doesn't sound like that's a guarantee. I hope I'm doing a better job at explaining...

  11. #11
    jmichaels is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    20
    @June7

    But, then doesn't this require that the csv imports into Access in the same order that it exists in the csv file? Is it safe to assume that it will?

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    It did in my test but I make no guarantees.

    Alternative is to open and read text file in VBA one line at a time, saving Type value to variable and doing some If Then conditional tests in loop structure. Using Split function to parse each line into fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    As I mentionted in post #6 one way to ensure that is to read the file line by line.

    Have a look at the attached, it will import the file line by line so the order will always be correct.

    Cheers,
    Vlad
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    agree with June - or perhaps talk to whoever supplies the data and explain that type='remains' is just plain unacceptable - I'd go as far as saying amateurish

  15. #15
    jmichaels is offline Novice
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    20
    THANK YOU ALL! As you suggested, I think importing line by line solves my problem. @Gicu, the code you provided seems to be working great! @Ajax, I agree with your comment about the data. Unfortunately, it's a canned report and the company charges quite a bit to make that kind of change.

    Thanks again!!

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

Similar Threads

  1. Link an email to line item in Access
    By BaldEagleOne in forum Access
    Replies: 5
    Last Post: 02-04-2020, 06:30 PM
  2. Line item dependencies
    By mcucino in forum Programming
    Replies: 1
    Last Post: 10-18-2018, 11:15 AM
  3. Replies: 6
    Last Post: 09-29-2017, 04:58 PM
  4. Replies: 2
    Last Post: 05-07-2014, 11:16 AM
  5. Line item query
    By michaeltorpedo in forum Queries
    Replies: 9
    Last Post: 05-02-2012, 01:17 PM

Tags for this Thread

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