locked
Importing CSV is truncating my data, why? RRS feed

  • Question

  • I use DoCmd.TransferText acImportDelim using HasFieldNames to import a CSV file. I have a field [Notes] with data type "Long Text" in my table. However, during import, Access is truncating data from my CSV to 255 chars and giving me an ImportErrors table for those records in which this field exceeds 255 chars.

    What magical dragon do I have to slay to get my data to import right?
    Tuesday, February 23, 2016 2:14 PM

Answers

  • You'll find your answer here.

    https://support.office.com/en-us/article/Import-or-link-to-data-in-an-Excel-workbook-a1952878-7c58-47b1-893d-e084913cc958?CorrelationId=287ee85e-ff57-4f21-8da2-61cddaaa3457&ui=en-US&rs=en-US&ad=US

    In short, to avoid errors during importing, ensure that each source column contains the same type of data in every row. Access scans the first eight source rows to determine the data type of the fields in the table. We highly recommend that you ensure that the first eight source rows do not mix values of different data types in any of the columns. Otherwise, Access might not assign the correct data type to the column.

    So, when appending an excel file to an existing table, even when columns are formatted and saved as memo fields, that if all 8 of the first rows in the excel file are less than 256 chars, Access assumes you actually meant to specify text, thus truncating the remaining rows after 255 chars. I have performed several tests placing "dummy" rows within the top 8 rows, and each triggered the import of more than 255 chars.

    Now, if you import to a new table, the wizard allows you to pick all of the formatting options. Importing to a new table is convenient if you are okay with overwriting all of the data already in the table. However, if you truly need to append, I would suggest importing to a temporary table, then appending from there.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by HTHP Tuesday, February 23, 2016 5:05 PM
    Tuesday, February 23, 2016 2:53 PM
  • What magical dragon do I have to slay to get my data to import right?

    Hi HTHP,

    For far better control on what happens during import, I constructed my own import routines.

    For csv files this is easy: open the csv file for read, read line by line, split the line using the Split function over the separator.

    With the AddNew method and - if necessary - a little interpretation of the array elements after splitting, you can do almost any csv import. Even the problematic where the separator is also used within a field, can be solved.

    Imb.

    • Marked as answer by HTHP Tuesday, February 23, 2016 5:05 PM
    Tuesday, February 23, 2016 4:11 PM

All replies

  • You'll find your answer here.

    https://support.office.com/en-us/article/Import-or-link-to-data-in-an-Excel-workbook-a1952878-7c58-47b1-893d-e084913cc958?CorrelationId=287ee85e-ff57-4f21-8da2-61cddaaa3457&ui=en-US&rs=en-US&ad=US

    In short, to avoid errors during importing, ensure that each source column contains the same type of data in every row. Access scans the first eight source rows to determine the data type of the fields in the table. We highly recommend that you ensure that the first eight source rows do not mix values of different data types in any of the columns. Otherwise, Access might not assign the correct data type to the column.

    So, when appending an excel file to an existing table, even when columns are formatted and saved as memo fields, that if all 8 of the first rows in the excel file are less than 256 chars, Access assumes you actually meant to specify text, thus truncating the remaining rows after 255 chars. I have performed several tests placing "dummy" rows within the top 8 rows, and each triggered the import of more than 255 chars.

    Now, if you import to a new table, the wizard allows you to pick all of the formatting options. Importing to a new table is convenient if you are okay with overwriting all of the data already in the table. However, if you truly need to append, I would suggest importing to a temporary table, then appending from there.


    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    • Marked as answer by HTHP Tuesday, February 23, 2016 5:05 PM
    Tuesday, February 23, 2016 2:53 PM
  • What magical dragon do I have to slay to get my data to import right?

    Hi HTHP,

    For far better control on what happens during import, I constructed my own import routines.

    For csv files this is easy: open the csv file for read, read line by line, split the line using the Split function over the separator.

    With the AddNew method and - if necessary - a little interpretation of the array elements after splitting, you can do almost any csv import. Even the problematic where the separator is also used within a field, can be solved.

    Imb.

    • Marked as answer by HTHP Tuesday, February 23, 2016 5:05 PM
    Tuesday, February 23, 2016 4:11 PM
  • I'm can't use the wizard because I have users who are intimidate by such mysterious creatures with all their scary options. So I'm trying to do this all in code.

    It seems I shall have to write my own import routine too. Shame on you MS Access, bad boy! Not sure I have the time to do this right now. But I will probably be back with questions as soon as I do have time.
    Thanks
    • Edited by HTHP Tuesday, February 23, 2016 5:05 PM typo
    Tuesday, February 23, 2016 5:05 PM
  • It's Dec 2019 and this problem is still present!    !!!!!!!! Microsoft
    Tuesday, December 10, 2019 2:50 AM
  • Microsoft won't let me downvote your answer. The transfertext method is an obvious bug and is not reliant on datatypes
    Sunday, March 29, 2020 11:40 PM
  • docmd.transferText OBVIOUS  BUG!. why my field S205 truncated to 205 when datatype is short text 255 chars??? Still outstanding 30/3/2020

    Sunday, March 29, 2020 11:43 PM
  • bot

    Sunday, March 29, 2020 11:44 PM