| Author |
Topic |
|
racinghart
Starting Member
49 Posts |
 Posted - 2007-02-05 : 19:10:02
|
| so i wrote this script and its pretty sweet (tara helped me before on this one) but now, for some STRANGE reason and its been reported by users the following:the script for example dumps "first name, last name"when we save the results as a CSV it looks good in notepad..."first name, last name"but we kept getting some kind of stange issues when we tried to dump that CSV data into another program to use... so i opened the CSV file in command prompt to see what it really looks like and it is actually "f i r s t n a m e , l a s t n a m e" with all these unnecessary spaces! why is it saving the CSV with spaces in it when my script is nothing of the sort? it was working fine last week, no changes were made to the script but now the CSV's are rendering those weird spaces. |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-02-05 : 19:20:46
|
| Is the datatype of your source column CHAR, VARCHAR, NCHAR, or NVARCHAR?CODO ERGO SUM |
 |
|
|
racinghart
Starting Member
49 Posts |
Posted - 2007-02-05 : 19:28:25
|
| here is the actual script as you may see i did not specify datatype for all the data column fields... should i?select '"'+Replace(name,'~','","')+'"' as name, '"'+Convert(char(10), strtdate, 101)+'"' as hiredate, '"'+Left(SSN,3)+'-'+Substring(SSN,4,2)+'-'+Substring(SSN,6,4)+'"' as ssn, '"'+Left(empid,6)+'"' as employeeid, '"'+Convert(char(10), birthdate, 101)+'"' as birthday, '"'+Left(phone,3)+'-'+Substring(phone,4,3)+'-'+Substring(phone,7,4)+'"' as phone, '"'+Rtrim(addr1)+' '+Rtrim(addr2)+'"' as address, '"'+Rtrim(city)+'"' as city, '"'+Rtrim(state)+'"' as state, '"'+Left(zip,5)+'"' as zip, '"'+Rtrim(department)+'"' as department, '"'+ CASE User5 when 'M' then '0' when 'F' then '1' when 'NULL' then ' ' end +'"'from employeewhere status = 'A' and DATEDIFF("dd",lastpaiddate,GETDATE()) <= '14'order by name |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-02-05 : 19:33:52
|
| Is the datatype of your source column CHAR, VARCHAR, NCHAR, or NVARCHAR?CODO ERGO SUM |
|
|
|
racinghart
Starting Member
49 Posts |
Posted - 2007-02-05 : 19:41:53
|
| CHAR. I just checked it |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-02-05 : 19:43:24
|
looks like the file is created as a unicode file, and your app doesn't understand that it's unicode.
www.elsasoft.org |
|
|
|
racinghart
Starting Member
49 Posts |
Posted - 2007-02-05 : 19:56:42
|
| is there any way i can get it to not save the file as a "unicode" any line of code to make sure its clean without spaces |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-02-05 : 22:43:47
|
I have no idea what you are using to create the file. I assume it's bcp. if so, you can use the -c flag to specify and ansi file.
www.elsasoft.org |
|
|
|
racinghart
Starting Member
49 Posts |
Posted - 2007-02-06 : 13:43:14
|
| i run the script in query analyzer... then at the bottom where the results are, i select all and choose the Save File as... and export it as a file_name.csv file... then when i open the csv file in command prompt using "edit" mode just to look at what is inside... the results have spaces between each letter... but then when i open the same file in notepad in windows, it looks perfectly fine. not sure if that makes sense |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-02-06 : 13:57:42
|
then "edit" mode in the cmd prompt does not understand the file is unicode.does it really matter though? most modern apps understand unicode. is something in your process really broken because of this?if you care, you'll have to change your script to convert everything to char/varchar/text from nchar/nvarchar/ntext. again, I haven't seen your script so I can't help you with that. also, I doubt that it's really necessary to do this.
www.elsasoft.org |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-06 : 14:01:24
|
| I'd just like to go on record that I helped with a small portion of the script. I would not have recommended exporting the data in this manner. Personally, I'd use bcp with the queryout option.Tara Kizer |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
racinghart
Starting Member
49 Posts |
Posted - 2007-02-06 : 14:37:11
|
quote:
Originally posted by jezemine
then "edit" mode in the cmd prompt does not understand the file is unicode.does it really matter though? most modern apps understand unicode. is something in your process really broken because of this?if you care, you'll have to change your script to convert everything to char/varchar/text from nchar/nvarchar/ntext. again, I haven't seen your script so I can't help you with that. also, I doubt that it's really necessary to do this.
www.elsasoft.org
select '"'+Replace(name,'~','","')+'"' as name,'"'+Convert(char(10), strtdate, 101)+'"' as hiredate,'"'+Left(SSN,3)+'-'+Substring(SSN,4,2)+'-'+Substring(SSN,6,4)+'"' as ssn,'"'+Left(empid,6)+'"' as employeeid,'"'+Convert(char(10), birthdate, 101)+'"' as birthday,'"'+Left(phone,3)+'-'+Substring(phone,4,3)+'-'+Substring(phone,7,4)+'"' as phone,'"'+Rtrim(addr1)+' '+Rtrim(addr2)+'"' as address,'"'+Rtrim(city)+'"' as city,'"'+Rtrim(state)+'"' as state,'"'+Left(zip,5)+'"' as zip,'"'+Rtrim(department)+'"' as department,'"'+ CASE User5 when 'M' then '0'when 'F' then '1'when 'NULL' then ' 'end +'"'from employeewhere status = 'A' and DATEDIFF("dd",lastpaiddate,GETDATE()) <= '14'order by name |
|
|
|
racinghart
Starting Member
49 Posts |
Posted - 2007-02-06 : 14:38:36
|
quote:
Originally posted by tkizer
I'd just like to go on record that I helped with a small portion of the script. I would not have recommended exporting the data in this manner. Personally, I'd use bcp with the queryout option.Tara Kizer
tara... can you provide a but more information on how i can import the data correctly as a CSV as opposed to selecting the results and clicking Save File As CSV |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-06 : 14:42:57
|
| First, you'll need to fix your query to just what you need. You certainly don't need all of those RTRIMs and quotes. Then you can do this:bcp.exe "SELECT Column1, Column2, Column3 FROM DatabaseName.dbo.YourTable" queryout E:\SomeFile.csv -c -SyourServer -T -t, -r\r\nbcp is a command line utility, so you run it from a cmd window. I prefer to fully qualify my objects in the query rather than using more switches in the bcp command.Tara Kizer |
|
|
|
racinghart
Starting Member
49 Posts |
|
|
racinghart
Starting Member
49 Posts |
Posted - 2007-02-06 : 16:31:11
|
quote:
Originally posted by jezemine
looks like the file is created as a unicode file, and your app doesn't understand that it's unicode.
www.elsasoft.org
taking what you said to consideration... i dumped the csv data and opened it with NOTEPAD and from that I chose SAVE FILE AS... and saved it over but made sure Encoding selection was on ANSI. I noticed it was being saved as UNICODE and that fixed it. The problem is, now we have to train the clerk an additional step of saving it as ANSI encoding after dumping the data to notepad. = / wonder if there is a sql code to just save the file as ANSI during the CSV file save. Thanks to everyone who helped, ive learned a great deal as usual but will put this concern aside for now. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-06 : 16:32:29
|
| Just use bcp as mentioned in my previous post and you won't need to do this saving stuff. Plus you can automate bcp unlike the method you've chosen!Tara Kizer |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-02-06 : 17:13:46
|
bcp? what's that? 
www.elsasoft.org |
|
|
|
Next Page
|
script results saving CSV file but has spaces!