April 16, 2012 at 5:01 am
Please help me out with something.
I have a query which selects several columns from a table and exports to a csv file ready to be loaded into a different system.
Any of the character columns could contain a comma and therefore could affect the way the csv is imported into the next system. I need to correct this. My first thought was to wrap a replace around each character column to prevent against any commas slipping through the net.
After making the change and comparing back to the original I get differences as the REPLACE key word appears to also trim the field.
To demonstrate this check out this query below:
declare @table table
(col1 char(5))
insert into @table
select ''
select col1, replace(col1, ',', '') from @table
Copy and paste the results set into excel and run a =LEN() function the each cell you have pasted. I get 5 and 1 respectively.
Using the LEN keyword in SQL Server returns zero so why does Excel read this differently? It is imperative there are no differences in the new file or these will be treated as changed records.
Thanks in advance!
April 16, 2012 at 5:06 am
aaa-322853 (4/16/2012)
Please help me out with something.I have a query which selects several columns from a table and exports to a csv file ready to be loaded into a different system.
Any of the character columns could contain a comma and therefore could affect the way the csv is imported into the next system. I need to correct this. My first thought was to wrap a replace around each character column to prevent against any commas slipping through the net.
After making the change and comparing back to the original I get differences as the REPLACE key word appears to also trim the field.
To demonstrate this check out this query below:
declare @table table(col1 char(5))
insert into @table
select ''
select col1, replace(col1, ',', '') from @table
Copy and paste the results set into excel and run a =LEN() function the each cell you have pasted. I get 5 and 1 respectively.
Using the LEN keyword in SQL Server returns zero so why does Excel read this differently? It is imperative there are no differences in the new file or these will be treated as changed records.
Thanks in advance!
Replace is returning a VARCHAR not a CHAR. Try this: -
DECLARE @table TABLE (col1 CHAR(5));
INSERT INTO @table
SELECT '';
SELECT col1, CAST(REPLACE(col1, ',', '') AS CHAR(5)) AS modifiedCol1
FROM @table;
April 16, 2012 at 5:11 am
Great, thanks for your reply. That makes a lot of sense.
Problems is I have quite a few columns I need to make this change on! :crazy:
Going to take a little longer than I anticipated!
April 16, 2012 at 5:16 am
Can you come at this from a different angle and instead change your delimiter? You'll probably have less trouble if you use a pipe (|), for example.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
April 16, 2012 at 6:15 am
This is an existing process. The way the files are produced is changing to use bcp for speed.
The original process added double quotes around each field but bcp doesn't easily allow for this functionality unless you start playing with format files.
As I am now planning on omitting the double quotes I need to ensure no stray commas messes anything up!
April 16, 2012 at 6:26 am
As I am now planning on omitting the double quotes I need to ensure no stray commas messes anything up!
If you used pipes, they would not. But I take your point about it being an existing process.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply