Import Excel File into SQL Server 1

[Kleptican]

I've written a small script to upload an excel file to a folder on the server. What I'd like to do is take that excel file and import it into a table that I made. Basically automate as much of it as possible. What is the best way of going about this?

I've come across websites that say you have to separate each column into a .csv file, or something along those lines, in order to upload the data into the table. If converting the excel file to a .csv file is the only way to do it, can someone direct me to a website that would explain it? Thanks a lot.

 

[GUJUm0deL]

Are you using SQL Server?? If so, then the easiest way would be to do a DTS package and then run the package in your code.

<cfquery name="somename" datasource="#default_ds#" >
  exec master..xp_cmdshell 'dtsrun /S [i]ServerNameGoesHere[/i] /E /N [i]DTSPackageNameGoesHere[/i]'
</cfquery>

If you don't know how to create a DTS package let us know and we can give you instructions.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[GUJUm0deL]

Let me clarify, in case there's any confusion, take your .xls file and create a DTS package that will use that .xls file and turn it into a table. If you are constatntly updating the .xls file then run a DTS package via code that will do this for you. ONly thing is the .xls has to be the same name in order for the DTS package to work. The code I pasted above will execute the DTS package.

Let us know if you need help creating a DTS package.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[gadjodilo77]

Hi,

First make two tabels, In the example I made:




csv_naam (headers csv file)
zdev_naam (headers/fields table)
offset
readvar

Put the right data in this table like for instance:

header csv header table offset rv
SystemInformation_SerialNumber z_serienr 2 1
type z_type 1 1
MAC_Address z_mac 3 1
BIOS_InstallDate z_bios 6 1
Processor_CurrentClockSpeed(MHz) z_proc 7 1
Memory_TotalMemory(MB) z_mem 8 1
IP_Address z_ip 9 1
SystemInformation_Manufacturer z_fab 4 1
SystemInformation_Model z_model 5 1
InventoryInformation_LastScanDate z_scandatum 10 1

Then make another table with the following fields:

z_serienr
z_type
z_mac
z_bios
z_proc
z_mem
z_ip
z_fab
z_model
z_scandatum


See the code below I used:


<cfparam name="attributes.recordscheiding" default="#chr(10)##chr(13)#">
<CFPARAM name="attributes.fileupload_dirroot" default="C:\xampp\htdocs\bar\csv\files\">

<CFSETTING requesttimeout="600">
<CFSET toegevoegd=0>
<CFSET diflist="">
<CFSET Filename=' '>
<CFIF Isdefined("Form.Uploadfile") EQ "YES">
<CFIF Len(Trim(Form.UploadFile)) GT 0>
<CFFILE
ACTION= "UPLOAD"
FILEFIELD = "Form.UploadFile"
DESTINATION = "#attributes.fileupload_dirroot#"
NAMECONFLICT = "MakeUnique">
<CFSET Filename=file.Serverfile>
<CFELSE>
<!--- <CFSET Foutis="Geen file ingevoerd."> --->
<!--- error handeling --->
<cferror type = "REQUEST"
template ="../../error/request_err2.cfm">
<!--- <CFINCLUDE TEMPLATE="/systeem/foutmelding.cfm"> --->
</CFIF>
</CFIF>
<CFQUERY NAME="" DATASOURCE="">
DELETE from zen_dev
</CFQUERY>

<CFFILE ACTION="READ"
VARIABLE="Content"
FILE="#attributes.fileupload_dirroot##Filename#">
<CFSET Nerrors=0>

<CFSET content=replace(content,";;;;;;",";x;x;x;x;x;","ALL") >
<CFSET content=replace(content,";;;;;",";x;x;x;x;","ALL") >
<CFSET content=replace(content,";;;;",";x;x;x;","ALL") >
<CFSET content=replace(content,";;;",";x;x;","ALL") >
<CFSET content=replace(content,";;",";x;","ALL") >

<CFSET Totaan=ListLen(Content, "#attributes.recordscheiding#")>

<CFLOOP INDEX="Teller" FROM="1" TO="1">
<CFSET Element=ListGetAt(Content, Teller,"#attributes.recordscheiding#")>
<CFSET Expectedlen=listlen(element,";")>
<CFLOOP index="offset" from="1" TO=#listlen(element,";")#>
<CFSET Input=ListGetAt(Element, offset, ";")>
<CFQUERY NAME="getconversie" DATASOURCE="">
Select * FROM zen_1
Where offset=#offset#
</CFQUERY>
<CFIF getconversie.recordcount>
<CFIF len(trim(getconversie.csv_naam)) NEQ len(trim(input))>

</CFIF>
</cfif>
</cfloop>
</CFLOOP>
<CFQUERY NAME="conversie" DATASOURCE="">
Select * FROM zen_1
where readvar=1
order by offset
</CFQUERY>
<!--- Data inlezen --->
<CFLOOP INDEX="Teller" FROM=2 TO=#Totaan#>
<CFSET Element=ListGetAt(Content, Teller,"#attributes.recordscheiding#")>
<CFSET CurLen = ListLen(Element, ";")>
<CFIF curlen EQ Expectedlen-1>
<CFSET element="#element#;x">
</CFIF>
<CFIF curlen EQ Expectedlen-2>
<CFSET element="#element#;x;x">
</CFIF>
<CFSET CurLen = ListLen(Element, ";")>
<CFIF curlen EQ Expectedlen>
<!--- <CFOUTPUT>#content#<br></CFOUTPUT> --->
<CFOUTPUT>#curlen#<br></CFOUTPUT>
<CFLOOP query="conversie">
<CFSET temp=trim(zdev_naam)>
<CFSET "#temp#"=ListGetAt(Element, offset, ";")>
</cfloop>
<CFQUERY NAME="update" DATASOURCE="">
INSERT INTO zen_dev
(
<CFLOOP query="conversie">

#zdev_naam# <CFIF #currentrow# NEQ #conversie.recordcount#>,</cfif>

</cfloop>
)
VALUES
(

<CFLOOP query="conversie">
'#evaluate(zdev_naam)#' <CFIF #currentrow# NEQ #conversie.recordcount#>,</cfif>
</cfloop>

)
</CFQUERY>
<CFELSE>

<CFOUTPUT>len=#curlen#-#element#<br></CFOUTPUT>
</CFIF>
</CFLOOP>
<CFFILE ACTION="Delete"
FILE="#attributes.fileupload_dirroot##Filename#">


Hope this will work for you!

Grt, Kabbi

 

[Kleptican]

Thanks for your replies.
GUJUm0deL - Yes, could you please describe how to make a dts package? The scenario that I have is that every month SQL Server will receive an excel file (which will change names. The name will be based on the month/day/year it is made.). So, I'm not updating an .xls, but instead, continually receiving a new one. Is it still possible to make a dts package that would handle this?

gadjodilo77 - I will try your code for the mean time.

Thanks, both of you.

 

[GUJUm0deL]

Kleptican, Open SQL Server:

[ol]
1.  Click the database you want to use, right-click on TABLE and choose 'All Tasks' -> 'Import Data'
2.  Click NEXT on the import/export wizard popup
3.  On the next screen, from DATA SOURCE choose Microsoft Excel 97-2000 and browse for the file
4.  Click NEXT
5.  On the next screen, (this part is where the data from the .xls will be saved to) choose the database you want the table to be created (by default it will be the database you chose in step 1)
6.  Click NEXT
7.  Leave the preselected radiobutton and click NEXT
8.  On the next screen, you'll see 'Sheet1$', 'Sheet2$' and 'Sheet3$', click the radiobutton for the sheet that has the info (these are sheets from .XLS file.  Ususually its sheet1$)
9.  Clcik the 'TRANSFORM' button, and select your criteria
10.  Click OK
11.  Click NEXT and then save the DTS package and you're done.
[/ol]

How are you uploading the file now? Are you uploading the file via FTP to your server folder or is the file coming off your local PC? If the former, then create this DTS off the server and map the file destination to the folder where the file is uploaded via FTP. If the latter, then create it from your local PC and map the file to the folder where yoiu save the .xls file.

The file MUST be in the folder you specified in the DTS or else the DTS will faile. Also rename the file to the filename that you mapped to from step3.

Play around with this, it may look like this is a lot of steps, but this will save you tons of time since now this is automated. You can either run the DTS via a schedule or off your code.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[Kleptican]

The file is being uploaded from a local pc.
Thanks a bunch for your help gujumodel, much appreciated!

 

[GUJUm0deL]

Anytime. And, thanks for the star,

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[asounds]

I just created a page that lets me upload an excel file, then I can query the excel file adn insert into a table using coldfusion. Once I figured it out, it was faily simple.

Here is a doc on creating the Datasource.

I had to create a dynamic data source in Coldfusion
(

http://www.macromedia.com/go/tn_18656)

I could send you my cf page if you want.

 

[Kleptican]

I appreciate the offer asounds, but thanks to everyone's help it's working now. and thx for the link because i can see that i have my page set up exactly as the example given in that link.