CF8 Importing CSV using listgetAt and blank columns

Hi all,

I'm currently trying to import a .CSV file to one of my tables and am having an issue when fields are blank.  I'm using listgetAt to specify the column to map to, but when there is a blank (fax number, for example...) it stops processing and throws an error such as there only being 8 columns instead of 10 - because there may have been 2 blank.

There are 10 columns in my .CSV file, and I'm using '|' as the delimeter.  I want to skip the blank fields but maintain the index #.....any suggestions?

I stripped out cfqueryparam for now - will put it back in once I get the import working properly.

Thanks,
Todd
<!---don't worry about column 9 - it's a SIC code and I use for a lookup only --->
<!--- my_longitude and my_latitude are found using a Yahoo API and the address in columns 2-5 --->
 
 
<cfquery name="importcsv" datasource="#DSN#">
 
 
         insert into customer (customer_name, customer_address1, customer_city, customer_state, customer_zip, customer_phone, customer_fax, customer_website, customer_main_service, customer_year_established, customer_longitude, customer_latitude, customer_member_level, customer_created_date, customer_created_by, customer_primary_language)
         
         VALUES (
         	
            '#listgetAt('#index#',1, '|')#', <!--- customer_name --->
            '#listgetAt('#index#',2, '|')#',	<!--- customer_address1 --->
            '#listgetAt('#index#',3, '|')#',	<!--- customer_city --->
            '#listgetAt('#index#',4, '|')#',	<!--- customer_state --->
            '#listgetAt('#index#',5, '|')#',	<!--- customer_zip --->
            '#listgetAt('#index#',6, '|')#',	<!--- customer_phone --->
            '#listgetAt('#index#',7, '|')#',	<!--- customer_fax --->
            '#listgetAt('#index#',8, '|')#',	<!--- customer_website --->
            '#my_service#',			<!--- customer_main_service --->
            '#listgetAt('#index#',10, '|')#',<!--- customer_year_established --->
            '#my_longitude#',			<!--- customer_longitude --->
            '#my_latitude#',			<!--- customer_latitude --->
            '1',					<!--- customer_member_level --->
            #DateFormat(now(),'mm/dd/yyyy')#,	<!--- customer_created_date --->
            '#session.auth.email#',	<!--- customer_created_by --->
            'English'				<!--- customer_primary_language --->
            
            
             )
 
     </cfquery>

Open in new window

LVL 2
NewcoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
Yes, most of the built in list functions ignore empty elements.  

If you are using CF 8, the simplest solution is to use an array instead.  The listToArray function has a new parameter that _will_ preserve empty elements:  listToArray(list, delim, includeEmptyFields).  Just convert the list to an array, then use array notation to access each of the elements, instead of list functions.

<cfset elements = listToArray(yourList, "|", true)>

...
<!--- use array notation to access each element --->
INSERT INTO TableName (...Columns.. )
VALUES
(
#elements[1]#,
#elements[2]#,
.....

)

If you are using MX7, try this CFC from Ben Nadel's blog:
http://www.bennadel.com/blog/501-Parsing-CSV-Values-In-To-A-ColdFusion-Query.htm

0
_agx_Commented:
> If you are using MX7, try this CFC from Ben Nadel's blog:

.... The less elegant solution for MX7 is to replace the empty elements with some other character (like a space).  You can find an example in the last post of this thread:
http://www.experts-exchange.com/Web_Development/Software/ColdFusion_Studio/Q_23987713.html
0
NewcoAuthor Commented:
I'm going to try listToArray here in a little bit...
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

galadoreCommented:
For prior versions of CF, you can use ListToArray, but you do need to replace empty fields first with spaces first by changing the delimiter from just a pipe to a pipe and a space.  I actually do it a couple times because I've had odd experiences sometimes with this kind of replace.  

<cfset myarray = listtoarray(replace(replace(MYLIST, "|","| ", "ALL"),"|","| ","ALL"),"|")>
0
_agx_Commented:
Yes, that is pretty much what I mentioned in thread above.
0
NewcoAuthor Commented:
Ok.  I'm missing something.  I'm reading the file, creating an array from my csv file, and looping it - but I'm getting an 'You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members' error.  I've posted what I have below:



<cffile action="read" file="#my_file#" variable="csvfile">
 
 
<cfset elements = listToArray(#csvfile#,'|',true)>
 
 
<cfloop index="x" array="#elements#">
 
 
<cfquery name="importcsv" datasource="scooble">
 
insert into customer_import_test (
		customer_name, 
        customer_address1, 
        customer_city, 
        customer_state, 
        customer_zip, 
        customer_phone, 
        customer_fax, 
        customer_website, 
        customer_main_service, 
        customer_year_established, 
        customer_longitude, 
        customer_latitude, 
        customer_member_level, 
        customer_created_date, 
        customer_created_by, 
        customer_primary_language)
         
         VALUES (
         	
            <cfqueryparam value="#x[1]#" cfsqltype="cf_sql_varchar">, 						<!--- customer_name --->
            <cfqueryparam value="#x[2]#" cfsqltype="cf_sql_varchar">,						<!--- customer_address1 --->
            <cfqueryparam value="#x[3]#" cfsqltype="cf_sql_varchar">,						<!--- customer_city --->
            <cfqueryparam value="#x[4]#" cfsqltype="cf_sql_varchar">,						<!--- customer_state --->
            <cfqueryparam value="#x[5]#" cfsqltype="cf_sql_varchar">,						<!--- customer_zip --->
            <cfqueryparam value="#x[6]#" cfsqltype="cf_sql_varchar">,						<!--- customer_phone --->
            <cfqueryparam value="#x[7]#" cfsqltype="cf_sql_varchar">,						<!--- customer_fax --->
            <cfqueryparam value="#x[8]#" cfsqltype="cf_sql_varchar">,						<!--- customer_website --->
            <cfqueryparam value="#my_service#" cfsqltype="cf_sql_integer">,					<!--- customer_main_service --->
            <cfqueryparam value="#x[10]#" cfsqltype="cf_sql_varchar">,						<!--- customer_year_established --->
            <cfqueryparam value="#my_longitude#" cfsqltype="cf_sql_float">,					<!--- customer_longitude --->
            <cfqueryparam value="#my_latitude#" cfsqltype="cf_sql_float">,					<!--- customer_latitude --->
            <cfqueryparam value="1" cfsqltype="cf_sql_integer">,							<!--- customer_member_level --->
            <cfqueryparam value="DateFormat(now(),'mm/dd/yyyy')#" cfsqltype="cf_sql_date">,	<!--- customer_created_date --->
            <cfqueryparam value="#session.auth.email#" cfsqltype="cf_sql_varchar">,			<!--- customer_created_by --->
            <cfqueryparam value="English" cfsqltype="cf_sql_varchar">						<!--- customer_primary_language --->
            
            
             )
 
</cfquery>
 
 
</cflooop>

Open in new window

0
_agx_Commented:
You want to loop the lines in the file, not the columns.  Just use <cfloop file="..."> to iterate through each line.  Then split each line into a #columns# array. Then you can reference each column using an index.

Not tested, but this should work:
<!--- loop through each line in the file --->
<cfloop file="c:\someFile.txt" index="line">
     <!--- split the line into column values --->
     <cfset columns = listToArray(line, "|", true)>
 
     <cfquery name="importcsv" datasource="scooble">
		insert into customer_import_test (
                customer_name, 
	        customer_address1, 
        	customer_city, 
	        customer_state, 
        	customer_zip, 
        	customer_phone, 
        	customer_fax, 
        	customer_website, 
        	customer_main_service, 
        	customer_year_established, 
        	customer_longitude, 
        	customer_latitude, 
        	customer_member_level, 
        	customer_created_date, 
        	customer_created_by, 
        	customer_primary_language)
         
         VALUES (
           <!--- use array notation to grab each column value --->
                
            <cfqueryparam value="#columns[1]#" cfsqltype="cf_sql_varchar">,                                           <!--- customer_name --->
            <cfqueryparam value="#columns[2]#" cfsqltype="cf_sql_varchar">,                                           <!--- customer_address1 --->
            <cfqueryparam value="#columns[3]#" cfsqltype="cf_sql_varchar">,                                           <!--- customer_city --->
            <cfqueryparam value="#columns[4]#" cfsqltype="cf_sql_varchar">,                                           <!--- customer_state --->
            <cfqueryparam value="#columns[5]#" cfsqltype="cf_sql_varchar">,                                           <!--- customer_zip --->
            <cfqueryparam value="#columns[6]#" cfsqltype="cf_sql_varchar">,                                           <!--- customer_phone --->
            <cfqueryparam value="#columns[7]#" cfsqltype="cf_sql_varchar">,                                           <!--- customer_fax --->
            <cfqueryparam value="#columns[8]#" cfsqltype="cf_sql_varchar">,                                           <!--- customer_website --->
            <cfqueryparam value="#my_service#" cfsqltype="cf_sql_integer">,                                     <!--- customer_main_service --->
            <cfqueryparam value="#columns[10]#" cfsqltype="cf_sql_varchar">,                                          <!--- customer_year_established --->
            <cfqueryparam value="#my_longitude#" cfsqltype="cf_sql_float">,                                     <!--- customer_longitude --->
            <cfqueryparam value="#my_latitude#" cfsqltype="cf_sql_float">,                                      <!--- customer_latitude --->
            <cfqueryparam value="1" cfsqltype="cf_sql_integer">,                                                        <!--- customer_member_level --->
            <cfqueryparam value="DateFormat(now(),'mm/dd/yyyy')#" cfsqltype="cf_sql_date">,     <!--- customer_created_date --->
            <cfqueryparam value="#session.auth.email#" cfsqltype="cf_sql_varchar">,                     <!--- customer_created_by --->
            <cfqueryparam value="English" cfsqltype="cf_sql_varchar">                                           <!--- customer_primary_language --->
            
            
             )
    </cfquery>
 
</cflooop>

Open in new window

0
_agx_Commented:
Correction.  Fix closing </cfloop> tag.
<!--- loop through each line in the file --->
<cfloop file="#my_file#" index="line">
     <!--- split the line into column values --->
     <cfset columns = listToArray(line, "|", true)>
 
     <cfquery name="importcsv" datasource="scooble">
                insert into customer_import_test (
                customer_name, 
                customer_address1, 
                customer_city, 
                customer_state, 
                customer_zip, 
                customer_phone, 
                customer_fax, 
                customer_website, 
                customer_main_service, 
                customer_year_established, 
                customer_longitude, 
                customer_latitude, 
                customer_member_level, 
                customer_created_date, 
                customer_created_by, 
                customer_primary_language)
         
         VALUES (
           <!--- use array notation to grab each column value --->
                
            <cfqueryparam value="#columns[1]#" cfsqltype="cf_sql_varchar">,                                           <!--- customer_name --->
            <cfqueryparam value="#columns[2]#" cfsqltype="cf_sql_varchar">,                                           <!--- customer_address1 --->
            <cfqueryparam value="#columns[3]#" cfsqltype="cf_sql_varchar">,                                           <!--- customer_city --->
            <cfqueryparam value="#columns[4]#" cfsqltype="cf_sql_varchar">,                                           <!--- customer_state --->
            <cfqueryparam value="#columns[5]#" cfsqltype="cf_sql_varchar">,                                           <!--- customer_zip --->
            <cfqueryparam value="#columns[6]#" cfsqltype="cf_sql_varchar">,                                           <!--- customer_phone --->
            <cfqueryparam value="#columns[7]#" cfsqltype="cf_sql_varchar">,                                           <!--- customer_fax --->
            <cfqueryparam value="#columns[8]#" cfsqltype="cf_sql_varchar">,                                           <!--- customer_website --->
            <cfqueryparam value="#my_service#" cfsqltype="cf_sql_integer">,                                     <!--- customer_main_service --->
            <cfqueryparam value="#columns[10]#" cfsqltype="cf_sql_varchar">,                                          <!--- customer_year_established --->
            <cfqueryparam value="#my_longitude#" cfsqltype="cf_sql_float">,                                     <!--- customer_longitude --->
            <cfqueryparam value="#my_latitude#" cfsqltype="cf_sql_float">,                                      <!--- customer_latitude --->
            <cfqueryparam value="1" cfsqltype="cf_sql_integer">,                                                        <!--- customer_member_level --->
            <cfqueryparam value="DateFormat(now(),'mm/dd/yyyy')#" cfsqltype="cf_sql_date">,     <!--- customer_created_date --->
            <cfqueryparam value="#session.auth.email#" cfsqltype="cf_sql_varchar">,                     <!--- customer_created_by --->
            <cfqueryparam value="English" cfsqltype="cf_sql_varchar">                                           <!--- customer_primary_language --->
            
            
             )
    </cfquery>
 
</cfloop>

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NewcoAuthor Commented:
It doesn't look like it's respecting each new line...I'm getting an empty string error.  Any ideas?
0
NewcoAuthor Commented:
Nevermind...found my mistake. Your code worked perfectly - thank you!

0
NewcoAuthor Commented:
Outstanding as usual.  Thank you for your help!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.