November 6, 2018 at 11:57 am
Is there a way to change a comma delimited file to pipe delimited using a script?
Thanks.
November 6, 2018 at 12:29 pm
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
November 6, 2018 at 12:54 pm
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 ...
November 6, 2018 at 1:24 pm
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.
November 6, 2018 at 1:31 pm
Thank you very much; looking into it now. I will update you...
November 8, 2018 at 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?
November 8, 2018 at 12:23 pm
EMtwo - Thursday, November 8, 2018 11:28 AMDo 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.
November 8, 2018 at 1:27 pm
Read this article: http://www.sqlservercentral.com/articles/SQL+Server+2012/106903/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply