Wrox Programmer Forums
|
Classic ASP Professional For advanced coder questions in ASP 3. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Professional section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 18th, 2012, 12:08 AM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default make split function ignore commers within quotes

hi all

I am looking for a way to effectively make the spilt function ignore commers that are within quotes. EG the csv file may look like:

10,some string value,"some, other, string, value",Yes,P99999

when I use the FSO to read the file I am using the split function to get this information into an array, do some things, then insert into DB. but of course "some, other, string, value" as seen above gets split into individual cells when the CSV is opened in Excel. I need this to appear in one cell.

Please note - passing the CSV file directly to the DB is not an option. I am being feed the CSV file so its a major hassle to ask for it to be re-formatted, probably no an option.

TYIA
__________________
Wind is your friend
Matt
 
Old July 18th, 2012, 04:21 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Hi Matt,

Sounds like regular expressions is the way to go. This article describes a similar problem: http://stackoverflow.com/questions/3...outside-quotes

The code it presents is .NET but reg ex patterns are generally language independent so youy may be able to make this work with classic ASP as well.

Cheers,

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
 
Old July 18th, 2012, 04:54 PM
Friend of Wrox
 
Join Date: Jan 2004
Posts: 1,870
Thanks: 12
Thanked 20 Times in 20 Posts
Send a message via AIM to mat41
Default

HI Imar - Thank you for the link. I have asked the organization that produces the CSV file if they can use another character such as the pipe for this scenario.....hopefully they will entertain it although I am unsure.

Otherwise as far as you are aware is the only option to write a custom split function?

Thanking you....
__________________
Wind is your friend
Matt
 
Old July 19th, 2012, 02:50 AM
Imar's Avatar
Wrox Author
 
Join Date: Jun 2003
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

>> Otherwise as far as you are aware is the only option to write a custom split function?

Regular expressions are extremely powerful and would make this relatively issue (once you figured out the reg ex you need). If your data is very predictable you could get those commas out of the way first. For example, you could maybe split on quotes first and then remove / replace the comma from the quoted items. Messy, but it might work.

Another solution is to use quoted identifiers where all your values are always quoted.

I would go for a reg ex if you can't change the source file.

Cheers,

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!





Similar Threads
Thread Thread Starter Forum Replies Last Post
Is there any function in XSLT to handle double quotes LeoMathew XSLT 1 September 22nd, 2010 06:03 AM
using the split function jd_erd Access 2 May 7th, 2007 09:59 PM
split function MunishBhatia ASP.NET 2.0 Professional 4 May 1st, 2007 10:05 AM
using 'Split' function p_gauri7 Crystal Reports 0 March 11th, 2005 01:02 PM
Split function Beulah ASP.NET 1.0 and 1.1 Basics 1 September 12th, 2003 09:14 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.