8 Replies Latest reply on Jul 26, 2009 11:20 PM by YogeshM

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

    YogeshM Level 1

      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:

       

      <!--- Read file --->
      <cffile action="read" file="#variables.uploadedFile#" variable="csvResult">
                 
      <!--- Invoke method to convert csv data to query --->
      <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

        • 2. Re: How to delete 1st 2 rows in a csv/excel file?
          Ratsnackbar Level 2

          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.

           

          <cfdump var="#qCsvData#" />

           

          <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][i]);
                          j=j+1;
                      }
                  }
                  i=i+1;   
              }
          </cfscript>

           

          <cfdump var="#newQuery#" />

           

          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

          • 3. Re: How to delete 1st 2 rows in a csv/excel file?
            Ratsnackbar Level 2

            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>

             

            <cfdump var="#myQuery#" />

             

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

             

            <cfdump var="#newQuery#" />

            • 4. Re: How to delete 1st 2 rows in a csv/excel file?
              Ratsnackbar Level 2

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

               

              Here it is.

               

              <!---
              DateCreated: 07/19/2009
              Author:         Joseph Snyder
              Name:         QueryUtils.cfc
              Purpose:     General purpose utilities for manipulating Queries. Currently there is only one method.
              --->

               

              <cfcomponent hint="Misc Utilities for manipulating queries, current there is only one function." output="false">

               

                  <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[i])){
                                  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][i]);
                                      j=j+1;
                                  }
                              }
                              i=i+1;   
                          }
                         
                          return newQuery;
                      </cfscript>
                 
                  </cffunction>

               

              </cfcomponent>

              1 person found this helpful
              • 5. Re: How to delete 1st 2 rows in a csv/excel file?
                YogeshM Level 1

                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.

                • 6. Re: How to delete 1st 2 rows in a csv/excel file?
                  YogeshM Level 1

                  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.

                   

                   

                  <cffunction name="removeRecords" returntype="Query" output="false" description="I remove records from a query.">

                       <cfargument name="queryObject" type="Query" required="true" hint="Query to remove records from" />

                   

                       <!--- Initialise function local variables --->
                       <cfset var myQry = QueryNew("")>
                       <cfset var i = 0>
                       <cfset var j = 0>
                       <cfset var columnName = "">
                       <cfset var columnList = "">
                       <cfset var tempList = arguments.queryObject.columnList>

                   

                      <!--- Loop query --->
                      <cfloop index="i" from="1" to="#arguments.queryObject.recordcount#">
                     
                          <!--- Ignore 1st 2 rows, 3rd row is header --->
                          <cfif i gt 2>

                   

                              <cfif i eq 3><!--- Create headers --->
                                 
                                  <!--- Loop column list (column_1, column_10, ...) --->
                                  <cfloop index="j" from="1" to="#listLen(tempList)#">
                                      <cfset item = ListGetAt(tempList,j)>

                                      <!--- Remove blank spaces --->

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

                   

                                      <!--- Remove slash --->
                                      <cfset columnName = replaceNoCase(trim(columnName),"/","_","All")>

                   

                                      <!--- Create column name --->
                                      <cfset QueryAddColumn(myQry,columnName,"cf_sql_varchar",ArrayNew(1))>
                                     
                                      <!--- Create list of column names --->
                                      <cfset columnList = columnList & columnName & ",">
                                  </cfloop>
                                 
                                  <!--- Remove trailing comma --->
                                  <cfset columnList = left(columnList,len(columnList)-1)>
                             
                              <cfelse><!--- As from 4th row, we add 1 new row to myQry for each iteration --->
                             
                                  <!--- Add rows --->
                                  <cfset QueryAddRow(myQry)>
                                 
                                  <cfloop index="j" from="1" to="#listLen(tempList)#">
                                      <cfset item = ListGetAt(tempList,j)>
                                      <cfset fieldValue = trim(arguments.queryObject[item][i])>
                                      <cfset col = listGetAt(columnList,j)>
                                      <cfset QuerySetCell(myQry,col,fieldValue)>
                                  </cfloop>
                             
                              </cfif>
                             
                          </cfif>
                         
                      </cfloop>

                   

                   

                       <cfreturn myQry>

                  </cffunction>

                   

                   

                  Question:

                   

                  Is there a simpler way to do the following?

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

                   

                  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

                  • 7. Re: How to delete 1st 2 rows in a csv/excel file?
                    Ratsnackbar Level 2

                    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

                    1 person found this helpful
                    • 8. Re: How to delete 1st 2 rows in a csv/excel file?
                      YogeshM Level 1

                      Hi Joe,

                       

                      Many thanks for your reply.

                       

                      Will definitely post it in a new thread.

                       

                      Keep up the very good job!

                       

                      Best regards,

                       

                      Yogesh.