Converting delimiter

  • Is there a way to change a comma delimited file to pipe delimited using a script?

    Thanks.

  • yes assuming that the file is a correctly formed delimited file

    read file using old delimiter - split columns - write to new file using new delimiter.

    dead easy to do in Powershell - a bit more work in T-SQL

    but I think more details of what you are trying to do are required

  • I have been having trouble loading my comma delimited .csv file using Bulk Insert -  commas in the data are being read as an end of a field and it's affecting the quality of the data - I have input of some fields that contain commas but not the end of the field. As a result, I have to manually remove all of the commas or change the delimiter to a pipe...  what I want to do is automate this process and so I'm looking for a script that can convert the commas to pipe ...

  • Was correct in assuming that what you asking for was not really the real requirement.

    Changing the delimiter alone is not the solution if you have to parse the file before using bulk insert - better one of the following solutions

    Assuming that the file is properly formatted as a CSV you can try the scriipt on this link http://bradsruminations.blogspot.com/2011/01/so-you-want-to-read-csv-files-huh.html

    another options, and possibly better is to use powershell to read the file and load directly to the database - not always feasible but more functionality.
    there are a few around - you can use Chrissy LeMaire one https://blog.netnerds.net/2015/09/import-csvtosql-super-fast-csv-to-sql-server-import-powershell-module/ or adapt it but the bulk of how to is there.

  • Thank you very much; looking into it now. I will update you...

  • Do you know if this issue persists across all SQL server versions or has it been fixed in any of the newer versions?

  • EMtwo - Thursday, November 8, 2018 11:28 AM

    Do you know if this issue persists across all SQL server versions or has it been fixed in any of the newer versions?

    It's not an issue with SQL Server.  It's an issue with a malformed .csv file.  If the field contents where enclosed in quotes, such as "some,value","another,value", then there are not problems with commas in the middle of a field.  Any time a file is read by any system your file would be a problem.

Viewing 8 posts - 1 through 7 (of 7 total)

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