Import Tab Delimited Text File Into Database

What db are you using?

If MS SQL, then use DTS (Data Transformation Services) it is made to perform large imports and it easily imports an excel file with 8000 records into the db within a second.

Otherwise you could do something like

INSERT INTO yourTable
SELECT    
FROM         OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\temp\taco2.xls";Extended properties=Excel 5.0')...Sheet1$

Using microsoft access file: .mdb

i want to have an upload function on the webpage, where i could select "Browse For File", selecting file, hit upload, and then the processing happens.

Hi,

yes DTS can be used for it as well - thats teh best way to go ! but for some reason if u cant use it - then u'll need to create a DSN for ur excel file on ur server

Use ur normal upload utility ... allow the user to upload the xls file.

save it on ur server [create a ODBC DSN to this excel file on the server]

<CFQUERY NAME="Qry_Excel" DATASOURCE="Excel_DSN" DBTYPE="ODBC">
      select * from "sheet1$"            
</CFQUERY>

<cfloop QUERY="Qry_Excel">
      <CFQUERY DATASOURCE="DB_Dsn">
        Insert into (field1, field2)
            values (
            <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#ExcelData1#">,
            <CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" VALUE="#Exceldata2#">
            )
    </CFQUERY>      
</CFLOOP>

If the fieldnames are same just use import function as given above by Tacobell
Insert into DBTable (Select * from "Sheet1$")

How might my form look like which i used to upload the document? I need to append the data, and the .txt file will need to overwrite the .txt file that is stored on the server.

ex. i upload tabDelim.txt today, on tomorrow, i upload again, the new file needs to overwrite tabDelim.txt (hence only having one file on the server), and appending the data to the .mdb file, as opposed to overwriting the data on the .mdb file.

the form could be as simple as this

<form name="frmData" action="doStuff.cfm" method="post" enctype="multipart/form-data">
     <input type="file" name="filename"><br />
     <input type="submit">
</form>

then on the action page

<cffile action="upload" destination="#expandPath('tabDelim.txt')#" filefield="filename" nameconflict="overwrite">

just use the code I posted earlier for the insert into the database.

oops, this should be the action page

<cffile action="upload" destination="#expandPath('.')#" filefield="filename" nameconflict="overwrite">
<cffile action="rename" destination="#expandPath('tabDelim.txt')#" source="#expandPath(cffile.serverfile)#">

jyokum:

So far i tried what you posted and it works, well sorta. it is puttin the information into the datafile, however, i am running into an error. Any  inside? My 12th column is a lengthy column, in which I specified it to be a MEMO field to accept the data.

Error Executing Database Query.  
[MERANT][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft Access Driver]Invalid precision value  
 
The Error Occurred in C:\CFusionMX\wwwroot\Testing\uploadAction.cfm: line 33
 
31 :                <cfqueryparam value="#listgetat(record,12,chr(9))#" cfsqltype="cf_sql_varchar">
32 :           )
33 :           </cfquery>
34 :      </cfloop>
35 :      Data inserted into the database!!

 

--------------------------------------------------------------------------------
 
SQL    INSERT INTO Table (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12) VALUES ( (param 1) , (param 2) , (param 3) , (param 4) , (param 5) , (param 6) , (param 7) , (param 8) , (param 9) , (param 10) , (param 11) , (param 12) )  
DATASOURCE   testing
VENDORERRORCODE   98
SQLSTATE   HY104

If I understand you correctly you are now doing a SELECT on the ACCESS DB and then import each row while looping over it in CF, correct?

Why don't you use

INSERT INTO yourTable
SELECT    
FROM         OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\temp\taco2.xls";Extended properties=Excel 5.0')...Sheet1$

You can even make the FROM a Query of a Query

INSERT INTO yourTable
SELECT    
FROM         myQuery

There is no looping volved

If I understand you correctly you are now doing a SELECT on the ACCESS DB and then import each row while looping over it in CF, correct?

Why don't you use

INSERT INTO yourTable
SELECT    
FROM         OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\temp\taco2.xls";Extended properties=Excel 5.0')...Sheet1$

You can even make the FROM a Query of a Query

INSERT INTO yourTable
SELECT    
FROM         myQuery

There is no looping involved

jyokum:

I got it to work. cf_sql_longvarchar is the type i need to use.

Here is another problem. Now when i export the TAB Delimited file, any row in access that contains a comma (in my long text field), Excel puts that column in quotes, which is going to be a problem.

How would i go about getting rid of the quotes while they are being INSERTED into the database?

Tacobell777:

Your tactic seems easy to implement, but what i would like to know is, the code you show above is going to be in my action page? Would i be keeping my upload page the same?

1st Page (Select File Page)
-------------------------------
<body>
<form name="frmData" action="uploadAction.cfm" method="post" enctype="multipart/form-data">
     <input type="file" name="filename"><br>
     <input type="submit">
</form>
</body>

2nd Page (Process The Upload)
------------------------------------
<cffile action="upload" destination="#expandPath('.')#" filefield="filename" nameconflict="overwrite">
<cffile action="rename" destination="#expandPath('tabDelim.txt')#" source="#expandPath(cffile.serverfile)#">

<cfif isDefined('form.filename')>
     <cffile action="upload" destination="#expandPath('.')#" filefield="filename" nameconflict="makeunique">
     <cffile action="read" file="#expandpath(cffile.ServerFile)#" variable="fileData">
     <cfloop index="record" list="#fileData#" delimiters="#chr(10)##chr(13)#">
          <cfquery name="qryInsert" datasource="testing">
          INSERT INTO Commercial (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>
     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>

<cfif isDefined('form.filename')>
     <cffile action="upload" destination="#expandPath('.')#" filefield="filename" nameconflict="makeunique">

          <cfquery name="qryInsert" datasource="testing">
          INSERT INTO Commercial (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12)
          SELECT    Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12
          FROM         OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\Temp; Extended Properties="Text; HDR=No; FMT=Delimited"')...YOURTEXTFILE#CSV
          </cfquery>
     </cfloop>

<cfelse>

I think this comes close to what you need.

In regards to the line:

FROM         OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\Temp; Extended Properties="Text; HDR=No; FMT=Delimited"')...YOURTEXTFILE#CSV

this is supposed to work on another computer that does not have the database file on their local computer. The database file is on my web server. This is all working on the internet. so  C:\TEMP; will not work. What would i put in the place?


Also I believe your earlier post in regards the EXCEL file works better as i do not have to do any exporting into CSV which is easier, so i will go with that option.

The file should be on the server, i.e. uploaded already..

the file is already uploaded. what i am confused by is why in my source i would write 'Data Source=c:\Temp;

as the datasource is not on my computer.

Change c:\temp to where ever the file is, maybe I should have made that clear form the start, change everything to suit your environment, the file name as well..

Tacobell777:

I feel like an idiot over here, but this is not working right. somethings wrong with the FROM clause.

<cfquery name="qryInsert" datasource="testing">
       INSERT INTO Commercial (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12)
       SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12
       FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="tabDelim.xls";Extended  properties=Excel 5.0')...COMM$
       </cfquery>

--------------rest of source

<body>
<cffile action="upload" destination="#expandPath('.')#" filefield="filename" nameconflict="overwrite">
<cffile action="rename" destination="#expandPath('tabDelim.xls')#" source="#expandPath(cffile.serverfile)#">

<cfif isDefined('form.filename')>
     <cffile action="upload" destination="#expandPath('.')#" filefield="filename" nameconflict="makeunique">

<cfquery name="qryInsert" datasource="testing">
       INSERT INTO Commercial (Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12)
       SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12
       FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="tabDelim.xls";Extended  properties=Excel 5.0')...COMM$
       </cfquery>

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>

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="tabDelim.xls";Extended  properties=Excel 5.0')...COMM$

In the data source you dont have the full path, i.e. C:\whatever\file.xls
And you know COMM should represent the Sheet name, right?

ok. i will put the full path, but my question is the end of the line

...COMM$

is this the correct syntax? Or are you assuming i know what to write at the end of the line:
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="tabDelim.xls";Extended  properties=Excel 5.0')...COMM$

and in regards to OpenDataSource, is this supposed to be the name of my db?

My SQL is not powerful at all and your post is kinda confusing.

Some helpful posts which i just could not get to work in my fashion

https://www.experts-exchange.com/questions/20244979/Uploading-excel-spreadsheet-into-database.html


http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrgrfexcelobjects.asp

For the life of my i cannot figure out why knowone has asked or posted the question in plain ENGLISH on the following:

How Do I IMPORT all the USED Columns and Rows from an EXCEL file into my .mdb without creating a DSN on the server.

Hypothetical situation: User have data in excel, would like to upload that data (APPENDING) into their current and existing .mdb file on the webserver, without exporting to CSV or TAB DELIMITED.

If you find a link to answer that exact question (with source code), please post it, especially if it is aimed a people with minimal experience with SQL, ExcelOBJECTS, COLDFUSION or DTS.