Results 1 to 10 of 10
  1. #1
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211

    Access VBA Problem Writing Long Record to CSV File

    Hi,



    I have a VBA routine which exports data to a CSV file. I have a 1-record recordset which is exported to the CSV file by iterating the fields and outputting a heading line, then iterating the fields of the record to fill in the data.

    The problem is, when it's a long record, the line is cut off at 1024 characters, and the balance is put on the next line in the CSV file.

    Unfortunately, I can't simply shorten the record to be exported, because it's all needed.

    I tried googling why the record is cut off at 1024 characters, but can't find anything.

    Any idea why this is happening? Thanks...

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The source is what, a query? AFAK, you can export more from a table if the field is memo/long text than you can from a query.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Have you tried using DoCmd.TransferText rather than your own vba?
    Code:
    Sub expLongText()     DoCmd.TransferText acExportDelim, , "Query59LongText", "c:\users\jack\documents\SampleCSVLOngText.csv", True     Debug.Print "all done  " & NoW() End Sub
    I just successfully tested the creation of a csv using a query of a table containing LongText. I selected those records whose prdct_desc was greater than 1200 and less than 3000 characters.

    This is SQL for Query59LongText
    Code:
    SELECT tbl_Spl_LCL_Std_EstProdDescEng.Est_Name
    , tbl_Spl_LCL_Std_EstProdDescEng.ESTBLMT_NO
    , Len([PRDCT_DESC]) AS Expr1
    , tbl_Spl_LCL_Std_EstProdDescEng.PRDCT_DESC
    FROM tbl_Spl_LCL_Std_EstProdDescEng
    WHERE (((Len([PRDCT_DESC]))>1200 And (Len([PRDCT_DESC]))<3000));
    Attached Files Attached Files

  4. #4
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Thanks Micron. The source is a SQL statement. None of the columns are too large by themselves, but all of them together are longer than 1024 characters. It's the entire CSV "record" that seems to be too long.

  5. #5
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Thanks orange. I will try this tomorrow morning. As mentioned in my other reply, it's not a single field that is too big, but all the fields together that exceed 1024 characters and the line in the CSV file wraps at 1024. I'll try TransferText.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This must be an Excel file then, not a text file as I originally thought?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Quote Originally Posted by Micron View Post
    This must be an Excel file then, not a text file as I originally thought?
    No, it's a text file in csv format. When I'm done with the export, I open the CSV file in Notepad, and where there should be a heading and one record on a single line, what I get is a heading, then the single record line of 1024 characters, then the last few characters have wrapped to the next line. The last field is a text field, and it's a bit long, but not long enough by itself to be a problem. It's all the fields together that exceed 1024 characters.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If the header isn't being repeated, then I think you're at the line character limit of Notepad, which I think is 1024.
    Try using Wordpad or Notepad++. From my research, both have much higher character limits.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    211
    Oh crap! It looks fine in Notepad++. That was stupid of me. Like Emily Latella used to say, "Never mind!"

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Emily Litella? I had to look that up - was never much of a SNL fan. But I did get a chuckle out of what I just saw (violins on television).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Problem writing code
    By JimO in forum Access
    Replies: 5
    Last Post: 02-13-2017, 07:23 PM
  2. writing to text file (array set)
    By pradeep.sands in forum Forms
    Replies: 5
    Last Post: 08-02-2013, 02:37 PM
  3. File I/O - Writing page headers.
    By Robeen in forum Access
    Replies: 13
    Last Post: 05-10-2013, 03:31 PM
  4. Writing text to log file.
    By winsonlee in forum Programming
    Replies: 2
    Last Post: 08-05-2011, 12:52 PM
  5. Replies: 1
    Last Post: 05-14-2006, 09:01 AM

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