locked
Access 2010 - Docmd.transfertext - BUG RRS feed

  • Question

  • Hi

    I use for uploading a csv file this method

    DoCmd.TransferText transferType:=acImportDelim, SpecificationName:=sSpecImport, _
    TableName:=sTableName, FileName:=sPath & sFileName, hasfieldnames:=True

    It works fine but if a string starts/ finishes with SPACE characters, the SPACE characters are systematically removed by the import process.

    So there is a bug because import csv/text file process must preserve the data in its entirety
    • Edited by technet65 Wednesday, January 29, 2020 5:51 PM
    Tuesday, January 28, 2020 1:14 PM

Answers

  • Hi Imb,

    Thanks for your code but INPUT is definitively to be outlawed because this command doesn't recognize LF like end of Line.

    The solution is to use TextStream object. Go here Explaination in FR with test function & file to load and Why used FileSystemObject TextStream object

    [LIST]
    [*]a LF without a CR is UNIX, Linux, AmigaOS and now Mac -
    [*]since Mac OS X.
    [*]Until Mac OS version 9 the Mac was usung a single CR without
    [*]a LF. Same for the Apple II.
    [*]CR+LF is Windows, DOS, OS/2, CP/M, Atari TOS.
    [/LIST]

    [QUOTE]For the old teletypes you needed both CR + LF.
    In the mid-seventies I saw a program using a teletype as "printer".
    They used multiple CR without a LF to write columns.
    With a teletype you had to send first the CR (carriage return) then
    the LF (line feed) due to the latency time of the mechanical equipment.
    If you did it the other way, the first character of the new line was
    printed somewhere to the right, not in the leftmost position.

    Helmut.[/QUOTE]

    Source here







    • Marked as answer by technet65 Monday, February 3, 2020 3:23 PM
    • Edited by technet65 Monday, February 3, 2020 3:24 PM
    Monday, February 3, 2020 3:14 PM

All replies

  • That's Access trying to be helpful, because trailing spaces rarely make sense. If your situation is an exception you may need to import the file yourself using some good old File I/O functions, like Open, Line Input, etc., and pick lines apart using Split, InStr, Mid, etc.


    -Tom. Microsoft Access MVP

    Tuesday, January 28, 2020 3:23 PM
  • What is the reason for NOT truncating those spaces? What purpose do they serve?

    One quick way to keep those spaces is to use a SQL Server database as your back end and import directly through the SQL engine using a stored procedure. That will retain the spaces, but I'm still curious as to why you want those spaces.


    Bill Mosca
    https://wrmosca.wordpress.com https://groups.io/g/MSAccessProfessionals

    Tuesday, January 28, 2020 4:39 PM
  • Hi Tom,

    Thanks for your help and just a remark, INPUT must used instead of LINE INPUT because unlike the Input # statement, the Input function returns all the characters it reads, including commas, carriage returns, linefeeds, quotation marks, and leading spaces. Source Microsoft here

    Saturday, February 1, 2020 8:21 PM
  • Hi Bill thanks for your help

    To avoid truncating of trailing spaces when we import flat file with SQL, the string must be surrounded  with double quote marks and delimiter string parameter must be precised in schema.ini..

    Saturday, February 1, 2020 8:34 PM
  • Thanks for your help and just a remark, INPUT must used instead of LINE INPUT because unlike the Input # statement, the Input function returns all the characters it reads, including commas, carriage returns, linefeeds, quotation marks, and leading spaces.

    Hi technet,

    I have made many flexible import functions. In all these functions I use the LINE INPUT statement.

    I found LINE INPUT, in combination with the (nested) Split function, far more flexible for all kind of situations, and easier to automate, than INPUT.

    Imb.

    Saturday, February 1, 2020 8:57 PM
  • Hi Imb-hb

    My original problem was to avoid truncating of spaces characters at the beginning /end of string and it’s possible only with Input(number, [ # ]filenumber)

    With LINE INPUT #, spaces are removed.

    Another solution, is to use TextStream object from the Microsoft Scripting Runtime library with one of the methods below

    • Read (X): X-character bed
    • ReadLine: reads the whole line
    • ReadAll: reads the whole file

    • Edited by technet65 Saturday, February 1, 2020 9:57 PM
    Saturday, February 1, 2020 9:53 PM
  • My original problem was to avoid truncating of spaces characters at the beginning /end of string and it’s possible only with Input(number, [ # ]filenumber)

    With LINE INPUT #, spaces are removed.

    Hi technet,

    Unless we are doing different things, this is very remarkable.

    When I import a file with the standard I/O functions and read the file line by line using LINE INPUT, then I get ALL the characters in the line, including leading and trailing spaces.

    I can imagine that you can loose trailing spaces when you assign the line content to a control, or both leading or trailing spaces by a Trim function, but not because of just reading the line. Do you have any after-processing after reading a line?

    Imb.

    Sunday, February 2, 2020 6:58 AM
  • Hi Imb,

    Thanks for four help.

    Did you test your routines with lines beginning /ending  with space characters?

    Below a flat file example with 

    • Line 2 starting & ending with  space characters
    • On line 2, strings starting / ending whit space characters

    Col1;col2;col3

      Test space character   ;   test space characters  ; test space character  





    • Edited by technet65 Sunday, February 2, 2020 6:03 PM
    Sunday, February 2, 2020 7:30 AM
  • Did you test your routines with lines beginning /ending  with space characters

    Hi technet,

    Yes, I did test this with lines beginning and ending with spaces. e.g.

      col1  ;  col2  ;  col3  ;  col4  
      test space character  ;  test space characters  ;  test space character  


    When I split line 2 using:  line_arr  = Split(line,";")  and inspect the array elements, I get:

    line_arr(0) = "  test space character  "
    Line_arr(1) = "  test space characters  "
    Line_arr(2) = "  test space character  "

    Imb.

    • Marked as answer by technet65 Sunday, February 2, 2020 2:15 PM
    • Unmarked as answer by technet65 Sunday, February 2, 2020 2:15 PM
    • Marked as answer by technet65 Sunday, February 2, 2020 2:20 PM
    • Unmarked as answer by technet65 Sunday, February 2, 2020 2:21 PM
    Sunday, February 2, 2020 11:05 AM
  • Hi lmb

    Is the variable LINE  initiated with the string returned by LINE INPUT #?


    • Edited by technet65 Sunday, February 2, 2020 2:30 PM
    Sunday, February 2, 2020 2:20 PM
  • Is the variable LINE  initiated with the string returned by LINE INPUT #?

    HI technet,

    Yes, that is right. In my general routine to read files I use "regel", and just translated this to "line".

    My way of working to import files is like:

            Do While (Active_file(filespec, progress_ind, regel))
                'data specific handling per regel
            Loop

    Imb.

    Sunday, February 2, 2020 2:45 PM
  • Hi lmb,

    Could you post the part of  your VBA Active_file function with LINE INPUT #, please




    • Edited by technet65 Sunday, February 2, 2020 2:56 PM
    Sunday, February 2, 2020 2:53 PM
  • Could you post the part of  your VBA Active_file function with LINE INPUT #, please

    Hi technet,

    This is the code - on this moment - of the function Active_file:

    Function Active_file1(cur_filenaam As String, busy_nr As Byte, cur_regel As String) As Boolean
      Dim tot_lengte As Long
      
      On Error GoTo Open_file
      
      If (active_first) Then active_first = False
      
    ga_door:
        
      If (EOF(file_nr)) Then
        Close #file_nr
        Busy_close busy_nr
        Active_file1 = False
        file_nr = 0
        Exit Function
      Else
        Active_file1 = True
      End If
      
      Line Input #file_nr, cur_regel
      Busy_next busy_nr, Len(cur_regel) + 2
      Exit Function
      
    Open_file:
      file_nr = FreeFile
      Open cur_filenaam For Input As #file_nr
      tot_lengte = LOF(file_nr)
      Busy_open busy_nr, tot_lengte
      active_first = True
      GoTo ga_door
    
    End Function
    

    You can place this function in a general module. The function is then available in the whole application. ?In this module you also declare the global variable: file_nr As Integer.

    If you place this module in a (library) database, and make a reference to this database, you can use the function in any application.

    You can comment out the lines with Busy_close, Busy_next and Busy_open. These routines update the Busy_form, where a wealth of progress-indicators are available, selected by busy_nr.
    It is hard to deliver the "Busy_form". In all my (100+) applications I use only a few generalized forms, that are tuned by meta-data table or pieces code in a module. It would take too much code for only that one "form".

    Imb.

    Sunday, February 2, 2020 9:14 PM
  • Hi Imb,

    Thanks for your code but INPUT is definitively to be outlawed because this command doesn't recognize LF like end of Line.

    The solution is to use TextStream object. Go here Explaination in FR with test function & file to load and Why used FileSystemObject TextStream object

    [LIST]
    [*]a LF without a CR is UNIX, Linux, AmigaOS and now Mac -
    [*]since Mac OS X.
    [*]Until Mac OS version 9 the Mac was usung a single CR without
    [*]a LF. Same for the Apple II.
    [*]CR+LF is Windows, DOS, OS/2, CP/M, Atari TOS.
    [/LIST]

    [QUOTE]For the old teletypes you needed both CR + LF.
    In the mid-seventies I saw a program using a teletype as "printer".
    They used multiple CR without a LF to write columns.
    With a teletype you had to send first the CR (carriage return) then
    the LF (line feed) due to the latency time of the mechanical equipment.
    If you did it the other way, the first character of the new line was
    printed somewhere to the right, not in the leftmost position.

    Helmut.[/QUOTE]

    Source here







    • Marked as answer by technet65 Monday, February 3, 2020 3:23 PM
    • Edited by technet65 Monday, February 3, 2020 3:24 PM
    Monday, February 3, 2020 3:14 PM
  • Thanks for your code but INPUT is definitively to be outlawed because this command doesn't recognize LF like end of Line.

    Hi technet,

    Of course, it is your choice to use what suites you the best.

    I did not know you are handling import files with only a LF as end of line, and indeed LINE INPUT needs as CR or CRLF end of line.

    In those cases I read the whole file in a variable, replace all LF's by CR (or CRLF), and write the file back. At least for me, the problem is then solved.

    Imb.

    • Marked as answer by technet65 Monday, February 3, 2020 4:19 PM
    • Unmarked as answer by technet65 Monday, February 3, 2020 4:19 PM
    Monday, February 3, 2020 3:40 PM
  • Hi Imb

    What happens if the size of your file is larger than your variable?

    On my case, I treat files exported from big dat base ans their size is very hudge!
    • Edited by technet65 Monday, February 3, 2020 4:23 PM
    Monday, February 3, 2020 4:21 PM
  • What happens if the size of your file is larger than your variable?

    Hi technet,

    You could use the Input function to read the large file in chunks of 65k bytes.

    But for that large files I think you could better use a stream. I have no experience with that, because I never had (not yet) that question.

    Imb.

    Monday, February 3, 2020 4:54 PM
  • Thanks once again for your help Imb.

    Our exchange permits me to clarify my knowledge about flat file reading process.

    Monday, February 3, 2020 5:36 PM
  • Our exchange permits me to clarify my knowledge about flat file reading process.

    Hi technet,

     i now remember that long time ago (A2.0 or perhaps the Commodore era), with not much internal memory, it took too long - if possible - to load a whole file.

    I then read the first 1k or 2k bytes, processed them using any EndOfLine definition, read the next number of bytes, processed them. etc., until the whole file was read. This is easily to build in VBA.

    Imb.

    Monday, February 3, 2020 7:25 PM