Post reply

Importing a CSV files in SSMS

  • December 29, 2019 at 7:49 pm

    #3711021

    Hi,

    My manger uses the SSMS import to import his CSV files. he has one file he just got that has, like most carriage retunes and line feeds in them. For some reason he wants to import the file with the carriage retunes, but not the line feeds. I think you can, during the import state the row delimiter, and in here you can specify the CR/FL or just CR (see image). So I was wondering if that would do it, or do I have to do something else?

    Thank you

    Attachments:
    You must be logged in to view attached files.

  • December 29, 2019 at 8:42 pm

    #3711026

    How in SSMS is he importing the data?  BULK INSERT or something from the menu?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 29, 2019 at 9:06 pm

    #3711028

    Thanks for the reply. I guess you would say the menue. He is using the Import Export wizard.

    Thank you

     

     

  • December 30, 2019 at 4:01 am

    #3711081

    I'll be back.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 30, 2019 at 4:43 am

    #3711086

    What you have posted in the image of your original post should do it.  I'm kind of confused, though.  You were right there or you wouldn't have been able to get the picture you did.   Why didn't you just try it to find out?  Or are you not privy to the files that your boss is?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 30, 2019 at 4:45 am

    #3711087

    Ah... and just noticed... you said your boss is using the import EXCEL wizard in your last post, which is different than your first post.  Which is it??? EXCEL or CSV???

    If it's truly the EXCEL import wizard  he's trying to use, then there's no concept of an "end-of-line" marker.

     

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 30, 2019 at 1:33 pm

    #3711164

    Hi,

    I am sorry if you are getting this twice now, I replyed twice and I am not seeing it here; so I am trying again.

    First I did not have the file at the time, I used a dummy file just so that I can show you the screen; I do have the file now.

    Second, I do not see where I said that he was using an Excel file import, he is using the import/export wizard. Also, I should point out in the image that I showed you I am using the flat file to get to that area.

    Third, I now I did try this with the actual file, and that did not work it just each column off.

    So I am not sure what I can do, any ideas would be appreciated.

    Thank you

     

     

     

  • December 30, 2019 at 2:25 pm

    #3711178

    Update,

    I thought that the fields were out of place but they are okay. One field now is having a problem, and I am looking at that now.

    But I would like to have your opinion if what I did was what I should do to get rid of the line feed.

    Thank you

  • December 30, 2019 at 6:05 pm

    #3711249

    My apologies.  You're correct.  You didn't mention Excel anywhere.  My old sometimes caffeine deprived eyes misread it that way.

    Going back to the beginning, you stated that the file had both CrLf at the end of the lines in the file and that you want to import the lines separately but still KEEP the Cr at the end of each line.  If I finally got what you requested down correctly, then you want to identify the row terminator as just Lf and the Cr should come in with the imported data.  You won't see it in a grid return.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 30, 2019 at 6:31 pm

    #3711253

    Yes, but more to the point I wanted to know if what I proposed would import the data without the line feed?

    Or how can I do that?

    Thank you

     

  • December 31, 2019 at 4:44 am

    #3711368

    itmasterw 60042 wrote:

    Yes, but more to the point I wanted to know if what I proposed would import the data without the line feed?

    Or how can I do that?

    Thank you

    Like I  said in my previous post, yes, but you got it backwards.  You want to use Lf as the row terminator during the import so the Cr's will come in with the data.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 31, 2019 at 11:58 am

    #3711401

    Okay thanks

    I appreciate your help

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply