locked
Problem importing csv file into access RRS feed

  • Question

  • User1023565123 posted

    Hi

    I'm importing some csv files into access. I thought these files were working 100% but it seems not.

    I seem to have tracked it down to a problem with the carriage return.

    For example, initially the file doesn't work but if I open it in notepad and:

    1) Press backspace to get the 2nd row immediately after 1st row
    2) press the Enter button to move 2nd row back to immediately below 1st row

    then when I Save and import that file it works no problem....

    I've tried appending vbLF, vbCR, vbCRLF, chr(13) to try and mimick what I did in Notepa but no luck


    any clues ?

    Hope this makes sense

    Friday, January 25, 2008 6:19 PM

Answers

  • User187056398 posted

    Does this make sense?

    It makes sense that if the CRLF's are gone, the import will fail.

    It doesn't make sense that Notepad is stripping them off.  Something else is happening....

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 26, 2008 12:29 PM

All replies

  • User187056398 posted

    You may be experiencing da’ BOM.

    If a text file has Unicode characters, it generally is supposed to have a Byte Order Mark.  The Byte Order Mark is a binary tag at the beginning of the text file (yes…weird) that indicates to an operating system that Unicode (or other types of characters) are contained:

    http://en.wikipedia.org/wiki/Byte_Order_Mark

    If you open a text file with Unicode characters and save it, Notepad.exe will write the BOM automatically! 

    That is what you may be experiencing.

    To verify this:

    1)      Open text file with a binary editor and examine the first few bytes.  I use XVI32 (freeware) http://www.chmaas.handshake.de/

    2)      Open the text file with Notepad and then save it.

    3)      Then reopen the text file with the binary editor and look for da’ BOM

    Friday, January 25, 2008 9:01 PM
  • User1023565123 posted

    Hi

    I opened the file in XV and there was nothing at the beginning. Then I opened the file in Noteapd, closed it and re-opened it in XV. Nothing at the beginning. Still the same.

    The files are created with vb6. To tests further I have created 3 different files.

    one with vbCRLF at the end of each row
    one with vbCR at the end of each row
    one with Chr(13) at the end of each row

    None work.

    ...how do I fix this  so that I can actually import these csv files ?

    Saturday, January 26, 2008 7:21 AM
  • User187056398 posted

    Then I opened the file in Noteapd, closed it and

    Did you save the file in Notepad?  That is what will insert the BOM.

    If we do find out that this is the problem, you can alter the VB6 program to insert the BOM bytes.

    Saturday, January 26, 2008 9:45 AM
  • User1023565123 posted

    Hi

    I opened and saved as UTF-8 and then I saw the EF BB BF  at the beginning of the file.

    I 'm just so confused now.........

    for example I have a file calle category with many lines e.g

    C0001;Test txt 1;;something;;C0145;'0'
    C0002;Test text 2;;something;;C0145;'0'

    the error I am getting is that it seems as if the next line is not recognised as a next line. If I print out the INSERT I get:

     

    INSERT INTO Category ([CategoryID], [CategoryName], [CategoryImage], [CategoryDesc], [ToolTip], [MainCategory], [Views]) VALUES ('C0001','Test txt 1','','something','','C0145','0'C0002)

    and then I get a :

    Syntax error (missing operator) in query expression ''0' C0002'

     

    Saturday, January 26, 2008 10:02 AM
  • User1023565123 posted

    ok so I opened up the original file and this is the screenshot:

     

    then I opened in Notepad, removed the column headings and saved...it REMOVES the 2 bits at the end of each row which correspodn to CR LF

     

    well, at least that's what I THINK is happening...............this would confimr why I am getting that SQL error, because the text in the file is now one whole record.

    Does this make sense?

    Saturday, January 26, 2008 10:43 AM
  • User187056398 posted

    Does this make sense?

    It makes sense that if the CRLF's are gone, the import will fail.

    It doesn't make sense that Notepad is stripping them off.  Something else is happening....

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, January 26, 2008 12:29 PM
  • User1023565123 posted

    and only for that file so far, because there are 3 othe csv files that I have opened, ammended and saved and those work 100%

     

    would be nice to see what that "something is else" IS!

    diving back in...................

    Saturday, January 26, 2008 1:40 PM