We help IT Professionals succeed at work.

Remove Qoutes From Tab Delimited Txt File, Before or After Uploading Into The Database

zakirdavis
zakirdavis asked
on
523 Views
Last Modified: 2013-12-24
I have sucessfully been able to import a TAB DELIMITED text file (exported from excel) into my database, by using the accepted answer from this link:
https://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_20877591.html 

What i've noticed is if there is a COMMA within any of the excel fields, EXCEL puts that chunk of text in QUOTES.

My problem is after importing into my database the text file (TAB DELIMITED), i have all of these quotes in my text, which i do not want.

How could i removed the quotes in the most efficient way:
A. Either during the import or
B. After the import

And by the way, my if function does not work as intended (which seems logically coded), if the user clicks the submit button without selecting a file.

Source:
---------

<body>
<cfif isDefined('form.filename')>
     <cffile action="upload" destination="#expandPath('.')#" filefield="filename" nameconflict="OVERWRITE">
       <cffile action="rename" destination="#expandPath('tabDelim.txt')#" source="#expandPath(cffile.serverfile)#">
     <cffile action="read" file="#expandpath('tabdelim.txt')#" variable="fileData">
     <cfloop index="record" list="#fileData#" delimiters="#chr(10)##chr(13)#">
          <cfquery name="qryInsert" datasource="testing">
          INSERT INTO THENAMEOFMYTABLE (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12)
          VALUES (
               <cfqueryparam value="#listgetat(record,1,chr(9))#" cfsqltype="cf_sql_varchar">,
               <cfqueryparam value="#listgetat(record,2,chr(9))#" cfsqltype="cf_sql_varchar">,
               <cfqueryparam value="#listgetat(record,3,chr(9))#" cfsqltype="cf_sql_varchar">,
               <cfqueryparam value="#listgetat(record,4,chr(9))#" cfsqltype="cf_sql_numeric">,
               <cfqueryparam value="#listgetat(record,5,chr(9))#" cfsqltype="cf_sql_numeric">,
               <cfqueryparam value="#listgetat(record,6,chr(9))#" cfsqltype="cf_sql_numeric">,
               <cfqueryparam value="#listgetat(record,7,chr(9))#" cfsqltype="cf_sql_numeric">,
               <cfqueryparam value="#listgetat(record,8,chr(9))#" cfsqltype="cf_sql_numeric">,
               <cfqueryparam value="#listgetat(record,9,chr(9))#" cfsqltype="cf_sql_numeric">,
               <cfqueryparam value="#listgetat(record,10,chr(9))#" cfsqltype="cf_sql_numeric">,
               <cfqueryparam value="#listgetat(record,11,chr(9))#" cfsqltype="cf_sql_numeric">,
               <cfqueryparam value="#listgetat(record,12,chr(9))#" cfsqltype="cf_sql_longvarchar">
          )
          </cfquery>
     </cfloop>
       <CFFILE ACTION="DELETE" FILE="#expandpath('tabdelim.txt')#">
     Data inserted into the database!!
      
<cfelse>
     <form name="frmData" method="post" enctype="multipart/form-data" action="uploadAction.cfm">
          <input type="file" name="filename"><br>
          <input type="submit">
     </form>
</cfif>
 
</body>
Comment
Watch Question

Commented:
why don't u remove it while inserting into database

first of all ur if condition.... change it to

<cfif isDefined('form.filename') And Len(Trim(form.filename))>      
      <cffile action="upload" destination="#expandPath('.')#" filefield="filename" nameconflict="OVERWRITE">
      <cffile action="rename" destination="#expandPath('tabDelim.txt')#" source="#expandPath(cffile.serverfile)#">
      <cffile action="read" file="#expandpath('tabdelim.txt')#" variable="fileData">
      <cfloop index="record" list="#fileData#" delimiters="#chr(10)##chr(13)#">
         <cfset Col1 = Replace(listgetat(record,1,chr(9)),"'","","All")><!--- and for removing ' --->
         <cfset Col2 = Replace(listgetat(record,2,chr(9)),"'","","All")><!--- and for removing ' --->
         <cfset Col12 = Replace(listgetat(record,12,chr(9)),"'","","All")><!--- and for removing ' --->

         <cfquery name="qryInsert" datasource="testing">
         INSERT INTO THENAMEOFMYTABLE (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12)
         VALUES (
              <cfqueryparam value="#Col1#" cfsqltype="cf_sql_varchar">,
              <cfqueryparam value="#Col2#" cfsqltype="cf_sql_varchar">,
              <cfqueryparam value="#listgetat(record,3,chr(9))#" cfsqltype="cf_sql_varchar">,
              <cfqueryparam value="#listgetat(record,4,chr(9))#" cfsqltype="cf_sql_numeric">,
              <cfqueryparam value="#listgetat(record,5,chr(9))#" cfsqltype="cf_sql_numeric">,
              <cfqueryparam value="#listgetat(record,6,chr(9))#" cfsqltype="cf_sql_numeric">,
              <cfqueryparam value="#listgetat(record,7,chr(9))#" cfsqltype="cf_sql_numeric">,
              <cfqueryparam value="#listgetat(record,8,chr(9))#" cfsqltype="cf_sql_numeric">,
              <cfqueryparam value="#listgetat(record,9,chr(9))#" cfsqltype="cf_sql_numeric">,
              <cfqueryparam value="#listgetat(record,10,chr(9))#" cfsqltype="cf_sql_numeric">,
              <cfqueryparam value="#listgetat(record,11,chr(9))#" cfsqltype="cf_sql_numeric">,
              <cfqueryparam value="#Col12#" cfsqltype="cf_sql_longvarchar">
         )
         </cfquery>
    </cfloop>
    <CFFILE ACTION="DELETE" FILE="#expandpath('tabdelim.txt')#">
    Data inserted into the database!!

<cfelse>
<!--- form --->
</cfif>

let me know

Regards
Hart

Author

Commented:
HART:

This does not work. I want to remove the following items

 , (Commas)

 ' (Single Quotes)

 " (Double Quotes)

What is ALL, as noted in your code:
<cfset Col1 = Replace(listgetat(record,1,chr(9)),"'","","All")>

Author

Commented:
When i use the following code:

<cfset Type = Replace(listgetat(record,1,chr(9)),"#chr(34)#","'","ALL")>

OR

<cfset Type = Replace(listgetat(record,1,chr(9)),"#chr(34)#","#chr(38)#","ALL")>

my database contains

  ' (Single Quotes) on place of the text that had double quotes around it.

Sample from TAB DELIMITED FILE
---------------------------------------

text      "text"      stats      .25      4      66      995      8025      0      0      55      "text"
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
sorry replacelist() won't work for u...
did u try out my earlier post ...

Regards
Hart

Commented:
zakirdavis, here's how I handled this exact problem.

I wrote a custom DLL that would read the used range of an Excel spreadsheet. You pass in an alternate delimiter (eg ~) and it will generate a new file with the text uniformly delimited so there are no quotes to screw you up.

code would be similar to

<cfscript>
obj=CreateObject('com','xlEngine.converter');
ret = obj.createDelimited('expandPath(commaDelimitedFile.txt)','expandPath(newFile.txt)','|');
obj='';
</cfscript>
<cffile action="read" file="expandPath(newFile.txt)" variable="fileData">

then just use fileData to do your database updates

let me know if your interested. I'm willing to share.

Author

Commented:
Yup, I tried your second post. I greatly appreciate it. I got a question, what books on CF do you have? What resources do you use to answer these questions? You surely can't have it all in your head (no disrespect intended). I like your attitude and knowledge and am seeking your status.

Author

Commented:
jyokum:

I am very interested.

zakir@zdaviswd.com

Commented:
thanks for the points, may be jyokums solution would be much better than mine...

but i gave u a solution w.r.t cfm..

And about the knowledge that i have, its bcos i have been working on cf for nearly 4 yrs and i am sure u would understand that this much time is more than enough for a person to understand the language very well..

now i never used any book, i just used the help documents given in cf...
and also am lucky that i am working with one of the best cf developers, so that is my knowledge background..

And also answering on EE helps me in many wayz, i get to learn as well as help others :-)

Regards
Hart

Commented:
now regarding the post...

now i used to have a same requirement..

the client used to upload an xls to the server and i had to insert the new data into my database...

what i had done was create an dsn on the xls file through cfadmin
[ps: don't forget to uncheck maintain connection check box in the cfadmin]

now with maintain connection off, i was able to overwrite the xls file everytime the client uploaded it.

and using the dsn, i could hit a query on the sheet and insert it into the db.

Only thing that was required was that the fieldnames should match with the db names [i mean the datatype etc..]

ps: In the link of Yog's answer, the solution is a good one, but i will have to try it out to let u know how he avoided the range

Regards
Hart

Commented:
http://www.alwyntech.com/blog/index.cfm?mode=entry&entry=C7A5E341-F0B1-4A81-9FD70764126A577E

I'll write up a better article when I get a chance but this should do for now.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.