none
ADO strips leading character 'S' from column values if ALL values in column start with 'S' RRS feed

  • Question

  • why does ADO (Jet.OLEDB.4.0) when importing a csv/txt file ... remove the leading character 'S' from the column values?

    if all my values in a single column start with S and the remaining characters are numeric characters e.g. S12345, S5677 ... the import removes the leading 'S' character, so imports 12345, 5677,

    BUT if a single value in the column does not start with 'S', e.g. 1235S ... then all values in the column are imported OK (the leading S is not removed)

    This leads me to believe that a leading character 'S' must have a special meaning ... such as 'import as string' ... so that S1245 will be import as the string value 1245 (rather than as a number?)

    But I cannot find any documentation to confirm this (having searched the net extensively)

    Is there an explanation??


    Tracey

    Thursday, November 9, 2017 8:48 PM

All replies

  • It would probably help if you were to provide details of how you are doing the import.

    If there is a way to specify a data type for each column and if you are not specifying the data type explicitly then it seems to be guessing at the type of data. If it guesses wrong then you need to specify the type explicitly.



    Sam Hobbs
    SimpleSamples.Info

    Thursday, November 9, 2017 9:46 PM
  • The connection string is

    "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;"

    I do not specify the column types ... as I said, this normally works ... the only case that it seems not to work (which I discovered by accident) is when all values in the column start with the letter 'S' followed by numeric characters,

    e.g. S12345


    Tracey

    Thursday, November 9, 2017 10:06 PM
  • And it could be that ADO is attempting to determine the data type and guessing wrong and therefore you need to specify it explicitly.


    Sam Hobbs
    SimpleSamples.Info

    Thursday, November 9, 2017 11:22 PM
  • If it were to guess and the first character was 'alpha' ... I assume it would guess 'string' ... in which case it would work?

    Tracey

    Thursday, November 9, 2017 11:39 PM
  • That is a valid assumption but it is not doing that so the issue is not what it should do but instead determining what it actually is doing.


    Sam Hobbs
    SimpleSamples.Info

    Friday, November 10, 2017 12:25 AM
  • If I knew what it was doing I would not be on the forum ...

    This is not a chat line ... please only respond with knowledge and/or experience; Thx


    Tracey

    Friday, November 10, 2017 3:55 PM
  • If you were more specific then the responses can be more specific. You were not clear about how you do the import.


    Sam Hobbs
    SimpleSamples.Info

    Friday, November 10, 2017 4:46 PM
  • Hi Tracey,

    I have done some test and I could reproduce your issue.

    I think maybe the connection mixes "S" and "$" since I could see the column becomes Currency type column if i use below code to import the data from the csv to a new table.

    folderpath = "C:\Users\v-guaxu\Desktop\"
    csvfilename = "Test.csv"
    DoCmd.TransferText acImportDelim, , "TestTable", folderpath & csvfilename

    What's your requirement? Do you want to import data from CSV/Txt file to access?

    If so, I would suggest you use TransferText method. You could try to create a specification file and then use the file in the method to import the data.

    folderpath = "C:\Users\v-guaxu\Desktop\"
    csvfilename = "Test.csv"
    DoCmd.TransferText acImportDelim, "TestSpecification", "TestTable", folderpath & csvfilename

    Please refer to below link to create a specification file.

    import tab-delimited txt into Access table using VBA

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 13, 2017 9:52 AM
  • Hi Terry, I am glad that you were able to reproduce my issue.

    My requirement is that I open the csv (or txt) file using the OLEDB connection string >>

    "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Text;"

    and that I read all the records (for internal processing). I have to expect that the connection 'open' will read the record content correctly. I do not have the information of column 'type' but do not expect any records to contain formatting (such as currency).

    Your thought about the 'S' and '$' is interesting ... but I do not have a good way to test (or fix) this ...


    Tracey

    Monday, November 13, 2017 11:10 PM
  • Hi Tracey,

    This issue seems to be a limit of OleDbDataAdapter, and I would suggest you add a new row which contains 1235S, and then remove it after import it for a workaround.

    You could refer link below for more information.

    # OleDB & mixed Excel datatypes : missing data

    https://stackoverflow.com/questions/3232281/oledb-mixed-excel-datatypes-missing-data/5721521#5721521

    Best Regards,

    Edward 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, November 17, 2017 9:17 AM