How to delete 1st 2 rows in a csv/excel file?

Report · Jul 24, 2009

Hi,

Is there a way to read a csv file and delete the 1st 2 rows (or any no. of rows I would like to)?

I'm currently using Ben Nadel's GREAT piece of code (Parsing CSV Values into a Coldfusion query) found at http://www.bennadel.com/index.cfm?dax=blog:501.view as follows:

<cffile action="read" file="#variables.uploadedFile#" variable="csvResult">


<cfinvoke component="components.csvToQuery" method="CSVToQuery" returnvariable="qCsvData">
     <cfinvokeargument name="CSV" value="#trim(variables.csvResult)#">
     <cfinvokeargument name="FirstRowIsHeadings" value="true">
</cfinvoke>

I need to manipulate qCsvData to delete the 1st 2 rows.

Any ideas would be most welcome.

Thanks and regards,

Yogesh Mahadnac

Report · Jul 26, 2009

Any ideas?

Report · Jul 26, 2009

From your question it sounds to me like qCsvData is already a Query Object.

That being the case you could use this script to remove the lines you want to remove. I pulled this out of one of my CFC's. You likely would want to add it back into a custom udf or cfc of your own and pass in the query object you want it to run against and an array of row index's you want to have left out of the return query.

For now I just changed where the arguments.query would be with your specified qCsvData queary object so it would be easier for you to test and threw in some rowsToIgnore. Just change it to [1,2] if you only want those rows left out but note that you can ignore any row by index. You also do not need to know what the column names are for this to work.

<cfscript>
rowsToIgnore = [1,2,3,4,5,7,9];

    i=1;
    cl = [];
    cl = qCsvData.columnlist;
    newQuery = queryNew(cl);
    While(i <= qCsvData.recordcount){
        j=1;
        if(!ArrayFind(rowsToIgnore,i)){
            QueryAddRow(newQuery);
            While(j <= ListLen(cl)){
                cn = listGetAt(cl,j);
                QuerySetCell(newQuery,cn,qCsvData[cn]);
                j=j+1;
            }
        }
        i=i+1;
    }
</cfscript>

NOTE: There is very likely a much better way of doing this but this is what I had handy for you.

Hope you like it!

-Joe

Report · Jul 26, 2009

Here is the same written as a cfc. The Foo.Bar part is just to show that accidently adding something other then a number should not throw it off. However I did not test this against other data objects such as complex objects or variables. but those should cause their own errors which should not be hard to track down. One would hope.

You would use it as so.

<cfquery name="myQuery" datasource="myDataSource" >
SELECT *
FROM myTable
</cfquery>

<cfscript>
    RecordsToRemove = [1,2,3,"foo.bar",4,5,7,9,22];
    obj = CreateObject("component", "QueryUtils");
    newQuery = obj.removeRecordsByIndex(myQuery,RecordsToRemove);
</cfscript>

Report · Jul 26, 2009

Sorry It would not let me attach the cfc in the last post for some reason.

Here it is.

    <cffunction name="removeRecordsByIndex" returntype="Query" output="false" description="I remove records from a query based on index numbers supplied.">
        <cfargument name="queryObject" type="Query" required="true" hint="Query to remove records from" />
        <cfargument name="indexArray" type="Array" required="true" hint="An array of index numbers of the records you want removed." />

        <cfscript>
            for(i=1; i < ArrayLen(ARGUMENTS.indexArray); i++){
                if(!IsNumeric(ARGUMENTS.indexArray)){
                    ArrayDeleteAt(ARGUMENTS.indexArray, i);
                    //I should throw an error here but I am being lazy
                }
            }

            i=1;
            cl = [];
            cl = ARGUMENTS.queryObject.columnlist;
            newQuery = queryNew(cl);

            While(i <= ARGUMENTS.queryObject.recordcount){
                j=1;
                if(!ArrayFind(ARGUMENTS.indexArray,i)){
                    QueryAddRow(newQuery);
                    While(j <= ListLen(cl)){
                        cn = listGetAt(cl,j);
                        QuerySetCell(newQuery,cn,ARGUMENTS.queryObject[cn]);
                        j=j+1;
                    }
                }
                i=i+1;
            }

            return newQuery;
        </cfscript>

    </cffunction>

</cfcomponent>

Report · Jul 26, 2009

Hi Joseph,

Many thanks for your very useful reply!

Do you happen to have the method ArrayFind as well please?

Thanks in advance and best regards,

Yogesh.

Report · Jul 26, 2009

Hi Joseph,

Thanks again for your brilliant answer!

I still need you help by the way, if you don't mind, of course!

I've based myself on your logic and tried to implement it differently.

(apologies if it's a bit complicated. it's almost 1 o'clock in the morning here, and sleep is getting over...)

Here is what I've done:

Now, I know that I need to ignore the 1st 2 rows, which means, my headers in fact start on the 3rd row.

I've created my column names dynamically and then added rows to my query object.

<cfset var myQry = QueryNew("")>
     <cfset var i = 0>
     <cfset var j = 0>
     <cfset var columnName = "">
     <cfset var columnList = "">
     <cfset var tempList = arguments.queryObject.columnList>

<cfloop index="i" from="1" to="#arguments.queryObject.recordcount#">

        
        <cfif i gt 2>

<cfset columnName = replaceNoCase(trim(arguments.queryObject[item])," ","_","All")>

<cfset columnName = replaceNoCase(trim(columnName),"/","_","All")>

<cfset QueryAddColumn(myQry,columnName,"cf_sql_varchar",ArrayNew(1))>

                    
                    <cfset columnList = columnList & columnName & ",">
                </cfloop>

                
                <cfset columnList = left(columnList,len(columnList)-1)>

            <cfelse>

                
                <cfset QueryAddRow(myQry)>

                <cfloop index="j" from="1" to="#listLen(tempList)#">
                    <cfset item = ListGetAt(tempList,j)>
                    <cfset fieldValue = trim(arguments.queryObject[item])>
                    <cfset col = listGetAt(columnList,j)>
                    <cfset QuerySetCell(myQry,col,fieldValue)>
                </cfloop>

            </cfif>

        </cfif>

    </cfloop>

</cffunction>

Question:

Is there a simpler way to do the following?

I'm trying to replace all blank spaces and any "/" between words with an "_".

I know we can use Regular Expressions to do it much simpler, but I'm not very familiar with the syntax.

I'd be grateful if you could please provide me with the correct syntax.

Thanks in advance.

Best regards,

Yogesh

Report · Jul 26, 2009

You are very welcome for the above and I like your implementation. However as for RegEx I am not very good at it myself and everytime I need to hand code with it I find myself reaching for a book to remember how.

That might be a question you would want to repost into a new thread.

Have a great day!

-Joe

Report · Jul 26, 2009

Hi Joe,

Many thanks for your reply.

Will definitely post it in a new thread.

Keep up the very good job!

Best regards,

Yogesh.

Adobe Community

How to delete 1st 2 rows in a csv/excel file?