RE: CSV HELL

[Matthew Walker]

This function will parse a single line of Excel CSV if that's any use to
you.

<cffunction name="parseCsvLine">

            <cfargument name="line" type="string" required="yes">

            

            <cfset var pos = 1>

            <cfset var arrLine = arrayNew(1)>

            <cfset var firstChar = "">

            <cfset var foundCell = "">

            <cfset var notDone = len(line)>

            <cfloop condition="#notDone#">

                        <cfset firstChar = mid(line, pos, 1)>

                        <cfswitch _expression_="#firstChar#">

                        

                                    <cfcase value="," delimiters="|">

                                                <cfset arrayAppend(arrLine,
"")>

                                                <cfset pos = pos + 1>

                                    </cfcase>

                                    

                                    <cfcase value="""">

                                                <cfset foundCell =
reFind("""(([^""]|"""")*)""($|,)", line, pos, true)>

                                                <cfif foundCell.pos[1]>

                                                            <cfset
arrayAppend(arrLine, replace(mid(line, foundCell.pos[2], foundCell.len[2]),
"""""", """", "all"))>

                                                            <cfset pos = pos
+ foundCell.len[1]>

                                                <cfelse>

                                                            <cfset notDone =
false>            

                                                </cfif>

                                    </cfcase>

                                    

                                    <cfdefaultcase>

                                                <cfset foundCell =
reFind("[^,]+", line, pos, true)>

                                                <cfif foundCell.pos[1]>

                                                            <cfset
arrayAppend(arrLine, mid(line, foundCell.pos[1], foundCell.len[1]))>

                                                            <cfset pos = pos
+ foundCell.len[1] + 1>

                                                <cfelse>

                                                            <cfset notDone =
false>            

                                                </cfif>

                                    </cfdefaultcase>

                        

                        </cfswitch>

                        

                        <cfif pos gt len(line)>

                                    <cfset notDone = false>

                        </cfif>

                        

            </cfloop>

            

            <cfreturn arrLine>

            

</cffunction>  

  _____  

From: Rich Ziade [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 23 June 2004 12:15 a.m.
To: CF-Talk
Subject: CSV HELL

Hi all:

I'm writing a little function that parses a CSV file into a query (I know
this has been done before, but I need it to specifically handle Excel csv
exports). Here's the snag:

Whenever Excel sees a comma or a quote in one of the cells, it wraps it in
quotes. If it doesn't find either, it doesn't. So for example, here's a row:

This is red, "This is sorta ""red""", "This is blue, really"

Now, the above is 3 columns in Excel. The 2nd column has quotes around 'red'
so Excel wraps the whole thing in quotes and escapes the quotes around the
word red by doubling them. This is relatively easy to handle.

The doosy is the third column. Excel found a comma so it wrapped it in
quotes (which is fine). The problem is, listgetat and other list functions
are pretty useless to me because it thinks a fourth column exists
(containing: really").

I'm guessing I can probably go down some sort of regular _expression_ hell to
do this, but is there an easier way?

Thanks,
Rich

  _____

---

[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]

---