| Off Topic A place for members to tell a joke, share a funny story and generally goof off with each other. |
 |
April 10, 2009, 02:50 PM
|
#1 (permalink) |
|
Character Info
Debuff 80 Orc Warlock Sargeras US PvP Guild: Fenrir Profile: Blizzard Armory Talent Spec: 0/13/58 |
Any VBscript whizzs?
I imagine this is going to be a pretty long shot but I know we have some coders here. So I'm writing a script that will basically dump the contents of a SQL DB table into a comma-delimited .csv file. The code works just fine but one of the columns has an address in it. The value in that column has a carriage return and line feed character which is screwing up my .csv file when I convert the DB rows to text. What I need to do is either remove all the CRLF characters or replace them with something else before converting them to a string. The problem is that I'm not super familiar with either vbscript or the object that contains the table (a RecordSet object) so I'm not sure how to replace the text in the recordset object. Here is the code that creates the recordset object and does the conversion to text. The silly part about % signs was some friggin ghetto work-around to cells that had commas in them which were giving me similar problems. The code can be seen here with the important parts highlighted: VisualBasic pastebin - collaborative debugging tool Obviously I know that at the most basic, I can find some way to iterate through each row of the recordset, iterate through each column of the row, pull the text out, check for the bad characters, delete them, and put the text back it. However it seems like there would be an easier was to do this, I just don't know it. Edit: Oh, and I know the syntax is ghetto as hell but I'm really new to vbscript so there is a lot of copy/pasting that I did. Edit2: if you don't know the recordset object specifically, here is an api: GetString Method (ADO) |
|
|
| Sponsored Links |
|
|
|
April 10, 2009, 03:08 PM
|
#2 (permalink) |
|
|
Re: Any VBscript whizzs?
One way would be to replace vbCRLF with a different character: strReturnedData = replace(strReturnedData, vbcrlf, "") You'll want to loop over the recordset: do until objRS.eof vbCrLf actually consists of two characters, chr(10) and chr(13) So, if you're still getting carriage returns in your output file, you'll want to replace chr(10) and chr(13) individually.. Last edited by Disumo; April 10, 2009 at 03:17 PM.. |
|
|
|
|
April 10, 2009, 03:09 PM
|
#3 (permalink) |
Character Info
Lavath 80 Blood Elf Warlock Shadow Council US RP Profile: Blizzard Armory Talent Spec: 3/13/55 |
Re: Any VBscript whizzs?
You could just get rid of it while you are getting the strFileData near line 13. I am assuming the vbCrLf is at the end of this field. |
|
|
|
|
April 10, 2009, 03:14 PM
|
#4 (permalink) | ||
|
Character Info
Debuff 80 Orc Warlock Sargeras US PvP Guild: Fenrir Profile: Blizzard Armory Talent Spec: 0/13/58 |
Re: Any VBscript whizzs?
Quote:
Quote:
Thanks for the replies though :D |
||
|
|
|
|
April 10, 2009, 03:21 PM
|
#5 (permalink) |
|
|
Re: Any VBscript whizzs?
I realized after that post that the replace() would affect the entire file. You may have to iterate over the ResultSet, get each row, and do the replace there Last edited by Disumo; April 10, 2009 at 03:32 PM.. |
|
|
|
|
April 10, 2009, 03:24 PM
|
#6 (permalink) |
|
Character Info
Debuff 80 Orc Warlock Sargeras US PvP Guild: Fenrir Profile: Blizzard Armory Talent Spec: 0/13/58 |
Re: Any VBscript whizzs?
Yeah, that's what I'm afraid of. i'm thinking I might be able to specify some bogus character as a row delimiter in the GetString method so I can do a simple Replace() on the whole text without worrying about screwing up the end of row delimeter. I'll update with how it goes... |
|
|
|
|
April 10, 2009, 03:41 PM
|
#7 (permalink) |
|
Character Info
Mindbomb 80 Human Warlock Kul Turis US PvE Guild: Dawn of Reckoning Profile: Blizzard Armory Talent Spec: 0/52/19 |
Re: Any VBscript whizzs?
Been a looooong while since I have worked with VBscript with the exception of some windows logon scripts. Just a thought, have you tried the recordset's SaveToFile method? (possibly even SavetoStream) Not sure if it would save as comma delimited by default or not but turning into a stream may have more options or the ultimate surprise handle the nasty CF LF within the field data for you. (wouldn't that be nice  ) But in a time crunch I just would iterate and be done with it.Prolly gonna have to iterate and replace. Should be overloaded Replace with a start and stop(length maybe) so to not remove the "end" CR LF.
__________________
Congratulations! You did well in a game. Poke your chest out and let me declare you champion of the world. |
|
|
|
|
April 10, 2009, 03:43 PM
|
#8 (permalink) | |
|
Character Info
Lavath 80 Blood Elf Warlock Shadow Council US RP Profile: Blizzard Armory Talent Spec: 3/13/55 |
Re: Any VBscript whizzs?
Quote:
|
|
|
|
|
|
April 10, 2009, 03:46 PM
|
#9 (permalink) | |
|
Character Info
Debuff 80 Orc Warlock Sargeras US PvP Guild: Fenrir Profile: Blizzard Armory Talent Spec: 0/13/58 |
Re: Any VBscript whizzs?
Quote:
Edit: Blarg, it looks like that only supports saving to a few formats. Here are the formats it supports adPersistADTG 0 Saves in a Microsoft Advanced Data TableGram (ADTG) format. adPersistXML 1 Saves in Extensible Markup Language (XML) format adPersistProviderSpecific 2 The provider will persist the Recordset using its own format Last edited by Debuff; April 10, 2009 at 03:54 PM.. |
|
|
|
|
|
| Bookmarks |
| Tags |
| vbscript, whizzs |
| Thread Tools | |
|
|
